[MySQL] Programmers SQL 고득점 Kit 풀기 - GROUP BY(1)
이전 글
서론
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가 익숙하지 않다는게 느껴진다. 차근차근 어떻게든 풀어내긴 했다.
- CATEGORY를 기준으로 GROUP BY를 수행하여 카테고리별 최대 가격을 구한다
- ('과자', '국', '김치', '식용유')에 포함되는 카테고리만 포함해야한다
- 카테고리의 최대 가격에 해당하는 상품의 이름을 조회하기 위해 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()를 사용할 수도 있다.
- RANK()함수를 사용한다
- CATEGORY별로 파티션되고, 이는 PRICE 내림차순으로 정렬된다
- (이 때 ORDER BY는 각 파티션 내에서 한 번만 수행된다)
- 매겨진 순위를 PRICE_RANK로 저장한다
- 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차근차근 풀어나가니 문제가 풀렸다.
- START_DATE를 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차의 CAR_ID를 조회해야한다
- CAR_ID를 기준으로 GROUP BY를 수행하고,
- 해당 일자 내에 포함되는 레코드 개수가 4개를 초과(5개 이상)하는 것만 조회한다
- 2022년 8월부터 2022년 10월까지 범위에 포함되면서, 1번에서 구한 CAR_ID에 해당하는 레코드를 조회한다
- MONTH와 CAR_ID를 기준으로 GROUP BY를 수행하고,
- 개수가 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푸는걸 실패하고 다른 풀이를 참고하였다. 상당히 참신한 풀이라서 마음에 들었다.
- 일단 CAR_ID로 GROUP BY를 수행한다
- 만약 대여중인 상태라면 AVAILABILITY컬럼에 '대여중'을 넣고, 아니면 '대여 가능'을 넣도록 한다
- "대여중"이 "대여 가능" 문자보다 더 사전순으로 뒤에있기 때문에 MAX함수를 사용한다
- 출력한다.
알고리즘을 풀 때는 몇 번 사용해본 적 있는 전략인데, 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 ASCGROUP 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 ASCJOIN이랑 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 ASCHAVING만 알면 어렵지 않다.
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을 꼭 붙여줘야한다.