[7주차_SQL] SQL 풀이 모음

KodaHye commented 1 month ago

SQL 유형: GROUP BY OVER()
코멘트 템플릿 하단 텍스트 복사해서 사용 해 주세요💌

## 문제: [문제명](링크)

### 설명

### 코드

### 아이디어

icegosimperson commented 1 month ago

문제: 카테고리 별 도서 판매량 집계하기

설명

2022년 1월의 카테고리 별 도서 판매량을 합산하고, 카테고리(CATEGORY), 총 판매량(TOTAL_SALES) 리스트를 출력 정렬 :카테고리명을 기준으로 오름차순

코드

SELECT a.CATEGORY, SUM(b.SALES) AS TOTAL_SALES
FROM BOOK a
JOIN BOOK_SALES b ON a.BOOK_ID=b.BOOK_ID
WHERE b.SALES_DATE LIKE '2022-01%'
GROUP BY a.CATEGORY
ORDER BY a.CATEGORY;

아이디어

2022년 1월에 판매된 카테고리 출력 -> 집계 함수 적용 전 필터링 필요 -> WHERE
WHERE b.SALES_DATE LIKE '2022-01%' 필터링 후 카테고리, 총 판매량 리스트 출력

KodaHye commented 1 month ago

문제: 저자 별 카테고리 별 매출액 집계하기

설명

도서 정보, 저자 정보, 판매량 정보가 있는 테이블이 있을 때, 2022년 1월 도서 별 총 매출액 구하기

코드

SELECT A.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORY, SUM(C.SALES * A.PRICE) `TOTAL_SALES`
FROM BOOK A
JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
JOIN BOOK_SALES C ON A.BOOK_ID = C.BOOK_ID
WHERE YEAR(C.SALES_DATE) = 2022 AND MONTH(C.SALES_DATE) = 1
GROUP BY A.AUTHOR_ID, A.CATEGORY
ORDER BY A.AUTHOR_ID ASC, A.CATEGORY DESC

아이디어

BOOK_ID와 AUTHOR_ID를 기준으로 테이블 조인하기
DATETIME 함수를 통해 2022년 1월 데이터만 가지고 오기
GROUP BY절에서 원하는 조건대로 묶기
SELECT 구문에 SUM(C.SALES * A.PRICE) 출력하기

SUM(C.SALES * A.PRICE)와 SUM(C.SALES) * A.PRICE의 값이 다를 수 있다는거 주의하기!

baexxbin commented 1 month ago

문제: 1070. Product Sales Analysis III

설명

판매된 모든 제품의 첫해에 대한 제품 ID, 연도, 수량 및 가격 출력

코드

SELECT product_id, year AS first_year, quantity, price
FROM Sales
WHERE (product_id, year) IN (
    SELECT product_id, MIN(year) AS year
    FROM Sales
    GROUP BY product_id
)

아이디어

팔린 상품의 첫해 정보를 가져와야함.
우선 팔린 상품의 product_id로 그룹바이
- 이때, SELECT절의 모든 열은 GROUP BY절에 명시적으로 표시되어야 함
- 따라서 GROUP BY가 필요한 product_id와 year를 WHERE절의 서브쿼리로 먼저 필터링

yeahdy commented 1 month ago

문제: 입양 시각 구하기(2)

설명

0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회

코드

WITH RECURSIVE time_h AS (
  SELECT 0 AS HOUR_DATA
  UNION ALL
  SELECT HOUR_DATA+1 
    FROM time_h
   WHERE HOUR_DATA < 23
)
SELECT
    th.HOUR_DATA AS HOUR
    ,COUNT(DISTINCT ao.ANIMAL_ID) AS COUNT
FROM time_h th
LEFT JOIN ANIMAL_OUTS ao
ON th.HOUR_DATA = HOUR(ao.DATETIME)
GROUP BY th.HOUR_DATA
ORDER BY th.HOUR_DATA;

아이디어

테이블에 존재하지 않는 시간을 구하기 위해 WITH RECURSIVE 재귀 쿼리문을 통해 0~23까지 데이터 추출
COUNT(*) 와 COUNT(칼럼명) 차이
- COUNT(*) NULL 값을 포함하기 때문에 NULL 인 데이터를 1로 표시함
- COUNT(칼럼명) NULL 값을 포함하지 않기 때문에 NULL 인 데이터를 제외하고 카운팅 (= 문제에서 NULL 을 제외하고 카운팅하기 때문에 0으로 표시됨)

ROW_NUMBER() OVER() 를 활용한 코드

각 행에 대해 고유한 순번을 반환하고, 기본값은 1부터 시작

SELECT
    h.HOUR
    ,COUNT(DISTINCT ao.ANIMAL_ID) AS COUNT
FROM (
    SELECT ROW_NUMBER() OVER()-1 AS HOUR
    FROM ANIMAL_OUTS
    LIMIT 24
) h
LEFT JOIN ANIMAL_OUTS ao
ON h.HOUR = HOUR(ao.DATETIME)
GROUP BY h.HOUR
ORDER BY h.HOUR;

Jewan1120 commented 1 month ago

문제: 1070. Product Sales Analysis III

설명

판매된 모든 제품의 첫 해에 대한 제품 ID, 연도, 수량, 가격을 선택하는 쿼리 작성하세요 .
결과 표를 임의의 순서 로 반환

코드

SELECT
    product_id 
    , year `first_year`
    , quantity
    , price
FROM
    Sales
WHERE (product_id, year) in (
        SELECT
            product_id
            , MIN(year) min_Year
        FROM
            Sales
        GROUP BY
            product_id
    )

아이디어

년도 별로 집계가 되었기 때문에 서브 쿼리와 GROUP BY를 이용해 각 product_id와 MIN(year)를 구해주면 됨
서브 쿼리에 조회된 결과로 기존 테이블에서 다시 조회

yeongleej commented 1 month ago

문제: Customers Who Bought All Products

설명

customer: 고객들이 산 상품정보
product: 상품 리스트

=> 상품 리스트의 모든 상품을 구매한 고객들을 출력

코드

select customer_id
from customer
group by customer_id
having count(distinct product_key) = (select count(product_key) from product)

아이디어

한 고객당 산 총 상품의 개수와 상품리스트의 총 상품 개수가 같으면 모든 상품을 구매한 것
distinct로 중복 구매한 상품은 한번만 카운트해야 함

yeongleej commented 4 weeks ago

문제: Biggest Single Number

설명

myNumbers 테이블에서 1개의 숫자만 존재하는 숫자 중 가장 큰 값 출력

코드

select max(num) as num
from (
    select num
    from mynumbers
    group by num
    having count(num) = 1
) my

아이디어

처음에는 select max(num) from mynumbers group by num having count(num) = 1로 풀었는데 자꾸 max값이 여러개 나왔습니다.
- 위의 코드는 그룹별로 max값을 출력하는 코드이기 때문에 당연히 그룹별로 큰값인 자기자신이 출력되는 것입니다 ㅎㅎ
때문에 서브쿼리로 1개의 숫자를 가지고 있는 숫자들을 출력하고 그 중 가장 큰 값을 구했습니다.

저는 요즘 sql 풀 때 max() 활용하는게 어려운것 같아요,,,, max() 활용사례를 더 공부해야겠습니다~ 😢

baexxbin commented 4 weeks ago

문제: 178. Rank Scores

설명

점수의 순위 찾기
동점은 같은 순위, 이후 값은 연속된 수

코드 & 아이디어

RANK(), OVER 이용한 방법
```
SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS "RANK"
FROM Scores
```
- DENSE_RANK()로 동점자 처리를, OVER를 통해 점수 높은 순으로 등수 매김
- DENSE_RANK(): 동점자 같은 순위로 처리하고 이어서 다음 순위 부여
- RANK(): 동점자 같은 순위로 처리하고, 동점자 수 만큼 건너뛰어서 다음 순위 부여
- OVER(): 윈도우 함수로 어떤 기준으로 순위를 계산할지 정의
  
  윈도우 함수: 레코드 집합에서 특정 범위를 정의하고 그 범위 내에서 통계, 순위, 누적 합계 등 계산 수행. (원래의 행을 유지하며 추가로 계산된 값 반환)

서브테이블, GROUP BY 이용
```
SELECT S.score, COUNT(S2.score) AS "RANK"
FROM Scores S,
(SELECT DISTINCT score FROM Scores) S2
WHERE S.score <= S2.score
GROUP BY S.id
ORDER BY S.score DESC;
```
서브 테이블을 이용하는 방법도 있어서 가져와봤습니다!
- S2: 점수들만 뽑은 테이블
- 현재 점수랑 S2의 점수들과 비교하며 자신과 같거나 큰 점수 개수 셈(=등수)
- 각 개별 점수로 만들기 위해 S.id를 그룹화해서 COUNT를 적용할 수 있도록 함

yeahdy commented 4 weeks ago

문제: 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

설명

평가 점수별 등급과 등급에 따른 사번, 성명, 평가 등급, 성과금을 조회

성과금 정보

기준 점수	평가 등급	성과금(연봉 기준)
96 이상	S	20%
90 이상	A	15%
80 이상	B	10%
이외	C	0%

평가등급의 컬럼명은 GRADE로, 성과금의 컬럼명은 BONUS 사번 기준으로 오름차순 정렬

코드

SELECT
    hem.EMP_NO,
    hem.EMP_NAME,
    CASE
        WHEN sub.average >= 96 THEN 'S'
        WHEN sub.average >= 90 THEN 'A'
        WHEN sub.average >= 80 THEN 'B'
        ELSE 'C'
        END AS GRADE,
    CASE
        WHEN sub.average >= 96 THEN hem.SAL*0.2
        WHEN sub.average >= 90 THEN hem.SAL*0.15
        WHEN sub.average >= 80 THEN hem.SAL*0.1
        ELSE 0
        END AS BONUS
FROM HR_EMPLOYEES hem
INNER JOIN (
    SELECT
        hg.EMP_NO,
        SUM(hg.SCORE)/2 AS average
    FROM HR_GRADE hg
    INNER JOIN HR_EMPLOYEES he
    ON hg.EMP_NO = he.EMP_NO
    GROUP BY hg.EMP_NO
) sub
ON hem.EMP_NO = sub.EMP_NO

아이디어

INNER JOIN 서브쿼리 사원의 평가 정보 테이블에서 회원별로 평균값 계산
평균값을 통해 성과급 정보에 맞춰서 케이스별로 조회

icegosimperson commented 4 weeks ago

문제: 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

설명

대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수 (컬럼명: RECORDS) 리스트를 출력

코드

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE >= '2022-08-01' and START_DATE  < '2022-11-01' and 
    CAR_ID IN (SELECT CAR_ID
               FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
               WHERE START_DATE >= '2022-08-01' AND START_DATE <'2022-11-01'
               GROUP BY CAR_ID HAVING COUNT(HISTORY_ID) > 4)
GROUP BY MONTH, CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH ASC, CAR_ID DESC;

아이디어

시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들

GROUP BY : MONTH, CAR_ID
서브쿼리 : 총 대여 횟수가 5회 이상인 자동차ID를 필터링 후 메인쿼리에서 월별 대여 횟수 구함

KodaHye commented 4 weeks ago

문제: 즐겨찾기가 가장 많은 식당 정보 출력하기

설명

식당의 정보가 있는 REST_INFO 테이블을 통해 음식 종류별로 즐거찾기 수가 가장 많은 식당의 정보 출력하기

코드

오답코드 즐겨찾기의 값은 가장 큰 값이 나올 수 있으나, ID, 음식 이름이 해당 즐겨찾기에 대한 식당 정보가 아닐 수 있음

SELECT FOOD_TYPE, REST_ID, REST_NAME, MAX(FAVORITES) `FAVORITES`
FROM REST_INFO
GROUP BY FOOD_TYPE
ORDER BY FOOD_TYPE DESC

정답코드 (1) 서브쿼리 사용

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO A
WHERE (FOOD_TYPE, FAVORITES) IN (
SELECT FOOD_TYPE, MAX(FAVORITES)
FROM REST_INFO
GROUP BY FOOD_TYPE
)
ORDER BY FOOD_TYPE DESC

정답코드 (2) RANK 함수 사용


WITH TMP AS (
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES,
#   ROW_NUMBER(): 행 번호 표시
#   PARTITION BY FOOD_TYPE: FOOD_TYPE 별로 순위를 나눔
ROW_NUMBER() OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC, REST_ID) AS RANK_FAVOIRTES
FROM REST_INFO
)

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES FROM TMP WHERE RANK_FAVOIRTES = 1 ORDER BY FOOD_TYPE DESC


<BR>

> TMP 결과

![image](https://github.com/user-attachments/assets/bc7a66fe-3448-4ee3-8d53-31a61ca7c2d7)

### 아이디어
* 단순히 `GROUP BY`를 한 후, 내가 원하는 값만 `MAX`를 하면 안됨
* 서브쿼리나 `OVER` 함수 사용하기

Jewan1120 commented 4 weeks ago

문제: 619. Biggest Single Number

설명

+-------------+------+
| Column Name | Type |
+-------------+------+
| num         | int  |
+-------------+------+

주어진 테이블 내에서 유일하게 등장하는 숫자들 중 최댓값을 찾아서 출력

코드

서브 쿼리 사용

SELECT
  MAX(num) num
FROM
  (SELECT
      num
  FROM
      MyNumbers
  GROUP BY
      num
  HAVING
      COUNT(num) = 1) temp

랭크 사용

SELECT *
FROM (
  SELECT num, RANK() OVER (ORDER BY num DESC) AS ranking
  FROM MyNumbers
  GROUP BY num
  HAVING COUNT(num) = 1
) ranked_numbers
WHERE ranking = 1;

| num | ranking |
| --- | ------- |
| 6   | 1       |

왜 ranking 빼고 num만 조회하려면 null이 나올까요..

아이디어

미리 중복된 값을 가지는 데이터들을 제거한 서브 쿼리를 작성
작성된 서브 쿼리에서 최댓값을 출력

GreatAlgorithm-Study / AlgorithmStudy