[MySQL] Programmers SQL 고득점 Kit 풀기 - GROUP BY(1)

 

이전 글

https://fladi.tistory.com/450

 

 

서론

GROUP BY가 어렵다고 해서 SELECT, STRING, JOIN을 모두 풀고 도전하려고 했다. 하지만 문제들을 풀다보니 GROUP BY 연관 문제가 계속 나와서 미리 예습을 하고 온 느낌이다. 이제 GROUP BY만 끝내면 SQL kit 완주다! 조금 만 더 힘내야지

문제풀이 사이트: https://school.programmers.co.kr/learn/courses/30/parts/17044

 

+) 다시 되뇌고 시작하겠다! SQL 실행 순서는 FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

 

 

1. 식품분류별 가장 비싼 식품의 정보 조회하기 - 레벨4

SELECT A.CATEGORY, A.PRICE, A.PRODUCT_NAME
FROM FOOD_PRODUCT A
JOIN
(
    SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    GROUP BY CATEGORY
) B
ON A.CATEGORY = B.CATEGORY 
    AND A.PRICE = B.MAX_PRICE    
ORDER BY PRICE DESC;

GROUP BY가 익숙하지 않다는게 느껴진다. 차근차근 어떻게든 풀어내긴 했다.

 

1. CATEGORY를 기준으로 GROUP BY를 수행하여 카테고리별 최대 가격을 구한다
   1. ('과자', '국', '김치', '식용유')에 포함되는 카테고리만 포함해야한다
2. 카테고리의 최대 가격에 해당하는 상품의 이름을 조회하기 위해 JOIN을 다시 수행해준다

 

한 카테고리에 최대 가격을 가지는 상품이 여러 개인 경우를 고려해야할 것 같았지만, 그냥 돌렸더니 정답처리가 되었다. 테케가 그렇게 구성되어 있어서 그런듯하다. 내 풀이는 최대 가격을 가지는 상품이 여러 개이면 전부 출력하도록 작성되었다. 

 

<RANK 풀이>

SELECT CATEGORY, PRICE, PRODUCT_NAME
FROM (
    SELECT 
        CATEGORY, 
        PRICE, 
        PRODUCT_NAME,
        RANK() OVER (PARTITION BY CATEGORY ORDER BY PRICE DESC) as PRICE_RANK
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
) A
WHERE PRICE_RANK = 1
ORDER BY PRICE DESC;

조금 더 개선하면 RANK()를 사용할 수도 있다.

 

1. RANK()함수를 사용한다
   1. CATEGORY별로 파티션되고, 이는 PRICE 내림차순으로 정렬된다
   2. (이 때 ORDER BY는 각 파티션 내에서 한 번만 수행된다)
   3. 매겨진 순위를 PRICE_RANK로 저장한다
2. PRICE_RANK를 1로 지정하여 최대값(우선순위 1)을 가지는 상품을 찾아낸다

 

이 풀이도 마찬가지로 가장 높은 가격의 상품이 여러 개라면 여러 개가 출력된다. RANK()는 동일한 가격이라면 같은 RANK를 부여하기 때문이다. 딱 하나만 출력하고 싶다면 RANK()가 아닌 ROW_NUMBER()를 사용하면 같은 값이라도 다른 번호가 부여된다. 

 

ROW_NUMBER()를 사용할 때 같은 가격이라면 사전순으로 작은 이름을 출력하도록 하고싶다면, 해당 조건을 ORDER BY에 추가하면 된다.

 

 

2. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 - 레벨3

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE 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(CAR_ID) > 4
) 
AND START_DATE >= '2022-08-01' AND START_DATE< '2022-11-01'
GROUP BY MONTH, CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH, CAR_ID DESC

-- START_DATE가 2022.08 ~ 2022.10 인 count가 5회 이상인 자동차 ID 조회
-- 월, 자동차 별 대여횟수 COUNT

차근차근 풀어나가니 문제가 풀렸다.

 

1. START_DATE를 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차의 CAR_ID를 조회해야한다
   1. CAR_ID를 기준으로 GROUP BY를 수행하고,
   2. 해당 일자 내에 포함되는 레코드 개수가 4개를 초과(5개 이상)하는 것만 조회한다
2. 2022년 8월부터 2022년 10월까지 범위에 포함되면서, 1번에서 구한 CAR_ID에 해당하는 레코드를 조회한다
   1. MONTH와 CAR_ID를 기준으로 GROUP BY를 수행하고,
   2. 개수가 0개 이상인 것들만 출력한다

 

<좀 더 개선한 코드>

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE CAR_ID IN 
(
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY CAR_ID
    HAVING COUNT(CAR_ID) > 4
) 
AND START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY MONTH, CAR_ID
# HAVING RECORDS > 0 레코드가 없으면 애초에 조회되지 않음
ORDER BY MONTH, CAR_ID DESC

 

BETWEEN A AND B 문법으로 대체하였고,

GROUP BY의 경우 레코드가 없으면 애초에 조회되지 않기 때문에 0이 나올 수 없다. 그래서 HAVING은 딱히 필요없다

 

 

3. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 - 레벨3

SELECT 
    CAR_ID,
    MAX(
        CASE 
        WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN '대여중'
        ELSE '대여 가능'
        END
    ) AS "AVAILABILITY"
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

푸는걸 실패하고 다른 풀이를 참고하였다. 상당히 참신한 풀이라서 마음에 들었다.

 

1. 일단 CAR_ID로 GROUP BY를 수행한다
2. 만약 대여중인 상태라면 AVAILABILITY컬럼에 '대여중'을 넣고, 아니면 '대여 가능'을 넣도록 한다
   1. "대여중"이 "대여 가능" 문자보다 더 사전순으로 뒤에있기 때문에 MAX함수를 사용한다
3. 출력한다.

 

알고리즘을 풀 때는 몇 번 사용해본 적 있는 전략인데, SQL에서도 필요할 때 사용해야겠다.

 

<다른 풀이>

WITH RENTAL_STATUS AS (
    SELECT DISTINCT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE
)

SELECT 
    h.CAR_ID,
    CASE 
        WHEN r.CAR_ID IS NOT NULL THEN '대여중'
        ELSE '대여 가능'
    END AS "AVAILABILITY"
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
LEFT JOIN RENTAL_STATUS r ON h.CAR_ID = r.CAR_ID
GROUP BY h.CAR_ID
ORDER BY h.CAR_ID DESC

위 같은 참신한 풀이를 생각해낼 수 없다면 CTE를 만드는 방법도 있다. 이게 가장 쉽게 떠올릴 수 있는 방법인 것 같다

 

• 대여중인 모든 CAR_ID를 중복없이 조회하는 CTE를 만든다
• CAR_RENTAL_COMPANY_RENTAL_HISTORY와 LEFT JOIN을 수행하여 대여중이지 않은 CAR_ID의 경우 r.CAR_ID가 NULL이 나오도록 한다
• CAR_ID를 기준으로 GROUP BY를 수행한다
• r.CAR_ID가 NULL인 경우 '대여 가능'으로 표시한다

 

 

4. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기 - 레벨2

SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR 
WHERE OPTIONS like '%통풍시트%' OR OPTIONS like '%열선시트%' OR OPTIONS like '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC;

like를 여러 개 사용할 수 있는 방법이 있을 것 같은데, 모르겠어서 다 이어붙였다.

이렇게 여러 개가 포함될 수 있는 경우에는 REGEXP를 사용하면 더 간결해질 수 있다.

 

SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR 
WHERE OPTIONS REGEXP '통풍시트|열선시트|가죽시트'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE;

정규표현식에 익숙하다면 이 방법이 더 편할 것 같다. 

 

 

5. 성분으로 구분한 아이스크림 총 주문량

SELECT I.INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF F
JOIN ICECREAM_INFO I
ON F.FLAVOR = I.FLAVOR
GROUP BY I.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER ASC

GROUP BY만 안다면 어렵지 않게 풀린다

 

 

6. 저자 별 카테고리 별 매출액 집계하기 - 레벨4

SELECT A.AUTHOR_ID AS AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY,
    SUM(BS.SALES * B.PRICE) AS TOTAL_SALES
FROM BOOK_SALES BS
JOIN BOOK B
JOIN AUTHOR A
ON BS.BOOK_ID = B.BOOK_ID   
    AND B.AUTHOR_ID = A.AUTHOR_ID
WHERE BS.SALES_DATE like "2022-01%"
GROUP BY A.AUTHOR_ID, B.CATEGORY
ORDER BY AUTHOR_ID ASC, CATEGORY DESC

하나하나 JOIN을 해주고, GROUP BY로만 잘 묶으면 어렵지 않은 문제다.

 

 

7. 카테고리 별 도서 판매량 집계하기 - 레벨3

SELECT B.CATEGORY, SUM(BS.SALES) AS TOTAL_SALES
FROM BOOK_SALES BS
JOIN BOOK B
ON BS.BOOK_ID = B.BOOK_ID
WHERE BS.SALES_DATE like '2022-01%'
GROUP BY B.CATEGORY
ORDER BY CATEGORY ASC

JOIN이랑 GRUOP BY의 개념만 알면 1도 어렵지않다

 

 

8. 즐겨찾기가 가장 많은 식당 정보 출력하기

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM (
    SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES, 
        RANK() OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS FRANK
    FROM REST_INFO R
) A
WHERE A.FRANK = 1
ORDER BY FOOD_TYPE DESC

이전에 풀었던 문제에서 배운 RANK()를 사용했다. ROW_NUMBER가 아닌 RANK를 사용했기 때문에 즐겨찾기가 가장 많은 식당은 2개 이상 나올 수 있다.

아직 문법이 익숙하지 않지만 여러 번 사용하다보면 외워질 듯 하다.

 

<다른 풀이>

WITH TOP_RESTAURANTS AS (
    SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES,
        FIRST_VALUE(REST_ID) OVER (
            PARTITION BY FOOD_TYPE 
            ORDER BY FAVORITES DESC
        ) AS TOP_REST_ID
    FROM REST_INFO
)

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM TOP_RESTAURANTS
WHERE REST_ID = TOP_REST_ID
ORDER BY FOOD_TYPE DESC;

바뀐건 CTE로 뺀 것과 FIRST_VALUE를 사용한 것밖에 없다. FIRST_VALUE는 파티션 내에서 정렬된 첫 번째 값을 가져오는 함수이다. 파티션별로 가장 인기 있는(TOP_REST_ID가 1인) 값을 가져올 수 있다. 그래서 WHERE절에 REST_ID = TOP_REST_ID로 가져오면 정답이다.

 

 

9. 조건에 맞는 사용자와 총 거래금액 조회하기

SELECT U.USER_ID, U.NICKNAME, SUM(B.PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD B
JOIN USED_GOODS_USER U
ON B.WRITER_ID = U.USER_ID
WHERE B.STATUS = 'DONE'
GROUP BY U.USER_ID 
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES ASC

HAVING만 알면 어렵지 않다. 

 

HAVING에는 TOTAL_SALES라는 Alias를 사용이 가능하다. 쿼리 실행 순서는 JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT(Alias 생성) -> ORDER BY 순으로 실행되는데, 왜 사용이 가능할까?

SQL 파서가 쿼리를 분석할 때 SELECT 절을 먼저 읽어서 alias를 인식한다. 그래서 실제 실행은 위 순서대로 진행되지만, GROUP BY와 HAVING 에서는 SELECT 에서 정의된 alias를 참조가능하도록 특별히 허용한다고 한다. GROUP BY와 HAVING이 집계 결과를 다루는 특수한 목적을 가진 절이기 때문이다. 

 

위 풀이가 통과되는 DBMS인 MySQL은 GROUP BY와 HAVING에 SELECT에서 정의한 ALIAS를 사용할 수 있도록 특별히 허용해준다는 걸 알 수 있다. 하지만 표준에서는 사용할 수 없다는 사실을 기억하자!

 

 

10. 진료과별 총 예약 횟수 출력하기

SELECT MCDP_CD AS `진료과코드`, COUNT(*) AS `5월예약건수`
FROM APPOINTMENT
WHERE APNT_YMD like '2022-05%'
GROUP BY MCDP_CD
ORDER BY `5월예약건수`, `진료과코드`

위에 문제를 다 풀고오면 너무 쉽게 느껴질 듯하다.

 

 

11. 고양이와 개는 몇 마리 있을까

SELECT ANIMAL_TYPE, COUNT(*) AS count
FROM ANIMAL_INS 
WHERE ANIMAL_TYPE IN ('Cat', 'Dog')
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

너무 쉬워서 재미없다. 

ANIMAL_TYPE을 선별하는 과정은 HAVING절 보다 WHERE절에 하는게 성능이 더 좋다. 그룹화 전에 필터링을 수행하기 때문에 적은 데이터만 필터링하기 때문이다.

 

 

12. 동명 동물 수 찾기

SELECT NAME, COUNT(*) AS `COUNT`
FROM ANIMAL_INS 
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(*) > 1
ORDER BY NAME

이름이 NULL인 경우도 COUNT에 집계된다. NULL인 동물은 카운트하지 않는다고 했으니 IS NOT NULL을 꼭 붙여줘야한다.