[프로그래머스] SQL 고득점 Kit 문제풀이 - GROUP BY

프로그래머스 스쿨 - 온라인 IT 특화 교육 전문 플랫폼

카테고리 별 도서 판매량 집계하기(Lv. 3)

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

• 카테고리 별 → GROUP BY
• 총 판매량 → SUM()

식품분류별 가장 비싼 식품의 정보 조회하기(Lv. 4)

3 방법 모두 기본 아이디어는 동일하고 “식품분류별로 가격이 제일 비싼 식품의 분류, 가격”을 조회하는데 서브쿼리를 쓰는지, WITH절을 쓰는지 차이만 있다.

1. 서브쿼리 + JOIN

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

1. WITH 절 + ,

WITH T1 AS(
    SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    GROUP BY CATEGORY
)

SELECT A.CATEGORY, A.PRICE AS MAX_PRICE, A.PRODUCT_NAME
FROM FOOD_PRODUCT AS A, T1 AS B
WHERE A.CATEGORY = B.CATEGORY
AND A.PRICE = B.MAX_PRICE
ORDER BY MAX_PRICE DESC;

1. WITH 절 + JOIN

WITH TMP AS (
    SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    GROUP BY CATEGORY
)

SELECT B.CATEGORY, B.MAX_PRICE, A.PRODUCT_NAME
FROM FOOD_PRODUCT AS A
JOIN TMP AS B
ON A.CATEGORY = B.CATEGORY
AND A.PRICE = B.MAX_PRICE
ORDER BY MAX_PRICE DESC;

진료과별 총 예약 횟수 출력하기(Lv. 2)

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

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기(Lv. 3)

1. 서브쿼리

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(HISTORY_ID) AS RECORDS 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
AND CAR_ID IN (
    -- 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차 ID
    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(HISTORY_ID) >= 5
)
GROUP BY MONTH, CAR_ID
ORDER BY MONTH ASC, CAR_ID DESC;

• 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들 → WHERE , GROUP BY , COUNT()
• 해당 기간 동안의 → WHERE , BETWEEN A AND B
• 월별 자동차 ID 별 총 대여 횟수 → GROUP BY , COUNT()

1. WITH 절, JOIN

WITH TMP AS (
    -- 1. 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차 ID
    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(HISTORY_ID) >= 5
)

-- 2. 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수 리스트
SELECT MONTH(A.START_DATE) AS MONTH, A.CAR_ID, COUNT(A.HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS A, TMP AS B
WHERE A.CAR_ID = B.CAR_ID
AND START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY MONTH, CAR_ID
ORDER BY MONTH, CAR_ID DESC;

성분으로 구분한 아이스크림 총 주문량(Lv. 2)

SELECT B.INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF AS A
JOIN ICECREAM_INFO AS B
ON A.FLAVOR = B.FLAVOR
GROUP BY INGREDIENT_TYPE
ORDER BY TOTAL_ORDER;

자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기**(Lv. 2)**

1. LIKE, OR

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

• '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차 → WHERE, OR, LIKE
• 자동차 종류 별로 몇 대인지 → GROUP BY , COUNT()
• 자동차 종류를 기준으로 오름차순 정렬 → ORDER BY

1. REGEXP

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

고양이와 개는 몇마리 있을까(Lv. 2)

SELECT ANIMAL_TYPE, COUNT(*) AS COUNT
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

동명 동물 수 찾기(Lv. 2)

SELECT NAME, COUNT(NAME) AS COUNT
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT >= 2
ORDER BY NAME;

• 이름이 없는 동물은 집계에서 제외 → COUNT(NAME) (COUNT(*) 은 NULL 을 포함하기 때문에 오답)

SELECT NAME, COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT >= 2
ORDER BY NAME;

가격대 별 상품 개수 구하기(Lv. 2)

SELECT FLOOR(PRICE/10000) * 10000 AS PRICE_GROUP, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

년, 월, 성별 별 상품 구매 회원 수 구하기(Lv. 4)

• 오답
  • 같은 YEAR, MONTH 에 한 회원이 상품을 여러번 구매할 수 있다는 점을 고려하지 않았음
  • 즉, 2022-1 에 1번 회원이 2번 이상 구매한 이력이 있을 경우 중복으로 카운팅되는 문제가 발생한다.
  • COUNT(*) → COUNT(DISTINCT OS.USER_ID) 로 변경
  • [MYSQL] 두 가지 버전 WHERE OR HAVING 활용 (USER_ID 중복제거 필수)
• SELECT YEAR(OS.SALES_DATE) AS YEAR, MONTH(OS.SALES_DATE) AS MONTH, UI.GENDER AS GENDER, COUNT(*) AS USERS FROM ONLINE_SALE AS OS JOIN USER_INFO AS UI ON OS.USER_ID = UI.USER_ID WHERE GENDER IS NOT NULL GROUP BY YEAR, MONTH, GENDER ORDER BY YEAR, MONTH, GENDER;

SELECT YEAR(B.SALES_DATE) AS YEAR, MONTH(B.SALES_DATE) AS MONTH, GENDER,
COUNT(DISTINCT B.USER_ID) USERS
FROM USER_INFO AS A
JOIN ONLINE_SALE AS B
ON A.USER_ID = B.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER;

입양 시각 구하기(2)(Lv. 4)⭐️

• 참고자료
• count(*)을 하면 1이 나오는이유?

-- 0 ~ 23 값을 갖는 임시 테이블
WITH RECURSIVE TMP AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 FROM TMP WHERE HOUR < 23
)

SELECT A.HOUR, COUNT(B.ANIMAL_ID) AS COUNT
FROM TMP AS A
LEFT JOIN ANIMAL_OUTS AS B
ON A.HOUR = HOUR(B.DATETIME)
GROUP BY HOUR;

• 0시부터 23시까지 → WITH RECURSIVE 문
• 각 시간대별로 → GROUP BY
• 입양이 몇 건이나 → COUNT(A.ANIMAL_ID) (ANIMAL_OUTS 의 컬럼 중에서 NULL 이 가능한 NAME 컬럼을 제외하고 모두 가능함 = 꼭 ANIMAL_ID 일 필요는 없음)
• 
• COUNT(*) 은 A.ANIMAL_ID 가 NULL 인 행까지 포함하여 갯수를 세기때문에 사용X
• 

저자 별 카테고리 별 매출액 집계하기(Lv. 4)⭐️

1. 첫번째 풀이 - 서브쿼리(복잡한 방법)

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

1. 두번째 풀이 - JOIN 2번

• 저자, 카테고리 별이기 때문에 A.SALES * SUM(B.PRICE) 로 풀면 오답이다.

SELECT B.AUTHOR_ID, C.AUTHOR_NAME, B.CATEGORY, 
SUM(A.SALES * B.PRICE) TOTAL_SALES
FROM BOOK_SALES AS A
JOIN BOOK AS B
ON A.BOOK_ID = B.BOOK_ID
JOIN AUTHOR AS C
ON B.AUTHOR_ID = C.AUTHOR_ID
WHERE A.SALES_DATE LIKE '2022-01%'
GROUP BY B.AUTHOR_ID, B.CATEGORY
ORDER BY AUTHOR_ID, CATEGORY DESC;

• 추가설명

    SELECT B.BOOK_ID, B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, B.PRICE * BS.SALES
    FROM BOOK AS B
    JOIN BOOK_SALES AS BS
    ON B.BOOK_ID = BS.BOOK_ID
    JOIN AUTHOR AS A
    ON B.AUTHOR_ID = A.AUTHOR_ID
    WHERE SALES_DATE LIKE '2022-01%'
    ORDER BY B.AUTHOR_ID ASC, B.CATEGORY DESC, B.BOOK_ID ASC;

  
• 이해가 안된다면 아래 쿼리를 먼저 이해해보자. GROUP BY 를 하지 않은 상태에서 해당 책의 판매량 * 판매가 를 먼저 구한 결과다. 실제로 B.PRICE * BS.SALES 합을 구해보면 위의 TOTAL_SALES 와 일치한다.

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기(Lv. 3)

SELECT CAR_ID, 
CASE 
    WHEN CAR_ID IN (
        -- 1. 2022년 10월 16일에 대여 중인 자동차 ID
        SELECT DISTINCT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE '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;

• 2022년 10월 16일에 대여 중
  • WHERE START_DATE <= '2022-10-16' AND '2022-10-16' <= END_DATE
  • WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE

조건에 맞는 사용자와 총 거래금액 조회하기(Lv. 3)

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