[코딩테스트] 프로그래머스 SQL GROUP BY 1 ~ 10

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit(GROUP BY)입니다.

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

SELECT 
    B.CATEGORY,
    SUM(S.SALES)
FROM BOOK B JOIN (
    SELECT *
    FROM BOOK_SALES
    WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-01'
) S
ON B.BOOK_ID = S.BOOK_ID
GROUP BY B.CATEGORY
ORDER BY B.CATEGORY

• 2022년 1월에 판매된 레코드를 조회합니다. (테이블 S)

(
    SELECT *
    FROM BOOK_SALES
    WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-01'
) S

• BOOK 테이블과 BOOK_ID를 기준으로 JOIN합니다.
  (FROM BOOK B JOIN (...) S ON B.BOOK_ID = S.BOOK_ID)
• 카테고리를 기준으로 GROUP BY 해줍니다.
  (GROUP BY B.CATEGORY)
• 판매량의 합계를 위해 SUM 함수를 사용하고 CATEGORY를 오름차순으로 정렬합니다.

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

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

• 2022년 1월에 판매된 책의 BOOK_ID와 판매량의 합계를 조회합니다.(테이블 BS)

(
    SELECT BOOK_ID, SUM(SALES) AS SALES
    FROM BOOK_SALES
    WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-01'
    GROUP BY BOOK_ID
)BS

• BS 테이블과 BOOK 테이블과 BOOK_ID를 기준으로 조인합니다.
  (FROM BOOK B JOIN (...) BS ON B.BOOK_ID = BS.BOOK_ID)
• 저자별, 카테고리별 그룹화를 해줍니다.
  (GROUP BY AUTHOR_ID, B.CATEGORY)
• 판매량과 가격을 기준으로 TOTAL_SLAES를 구합니다.
  (SUM(B.PRICE * BS.SALES) AS TOTAL_SALES)
• 해당 결과를 구한 테이블 O와 AUTHOR 테이블을 AUTHOR_ID를 기준으로 조인합니다.
  (FROM AUTHOR A JOIN (...) O ON A.AUTHOR_ID = O.AUTHOR_ID)
• AUTHOR_ID를 오름차순으로, 카테고리를 내림차순으로 정렬합니다.

식품분류별 가장 비싼 식품의 정보 조회하기

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

• 식품 분류를 통해 그룹화를 합니다.
  (GROUP BY CATEGORY)
• 식품 분류에는 '과자', '국', '김치', '식용유' 만 가능하기에 그룹화에서 HAVING절을 이용합니다.
  (HAVING CATEGORY IN ('과자', '국', '김치', '식용유'))
• 그룹화한 테이블에서 각 식품 분류 별 가장 가격이 높은 필드를 선택하고 F2라는 테이블의 별칭을 설정합니다.

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

• FOOD_PRODUCT와 F2 테이블에서 가장 비싼 가격과 일치하고 식품 분류를 기준으로 JOIN합니다.
  (FROM FOOD_PRODUCT F1 JOIN (...) F2 ON F1.PRICE = F2.MAX_PRICE AND
  F1.CATEGORY IN ('과자', '국', '김치', '식용유'))
• 식품 가격을 기준으로 내림차순 정렬합니다.

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

SELECT
    B.FOOD_TYPE,
    A.REST_ID,
    A.REST_NAME,
    B.FAVORITES
FROM REST_INFO A JOIN (
    SELECT
        FOOD_TYPE,
        MAX(FAVORITES) AS FAVORITES
    FROM REST_INFO
    GROUP BY FOOD_TYPE
)B ON A.FAVORITES = B.FAVORITES AND A.FOOD_TYPE = B.FOOD_TYPE
ORDER BY FOOD_TYPE DESC

• 음식 종류를 기준으로 그룹화를 진행한 후 즐겨찾기가 가장 많은 음식 종류를 구한 테이블을 B라고 지정합니다.

(
    SELECT
        FOOD_TYPE,
        MAX(FAVORITES) AS FAVORITES
    FROM REST_INFO
    GROUP BY FOOD_TYPE
)B

• REST_INFO 테이블과 B 테이블의 즐겨찾기 수와 음식 종류를 기준으로 JOIN합니다.
  (FROM REST_INFO A JOIN (...)B ON A.FAVORITES = B.FAVORITES AND A.FOOD_TYPE = B.FOOD_TYPE)
• 음식 종류를 기준으로 내림차순 정렬합니다.

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

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

• CAR_ID를 기준으로 그룹화합니다.
  (GROUP BY CAR_ID)
• 2022년 10월 16일을 기준으로 대여중인 차는 대여중, 대여중이지 않은 차는 대여 가능으로 표시하기 위해 CASE 절을 사용합니다.
  시작일자가 대여 가능보다 대여중이 우선적으로 선택하기 위해 MAX 함수를 사용합니다.
  (MAX(CASE WHEN '2022-10-16' BETWEEN TO_CHAR(START_DATE,'YYYY-MM-DD') AND TO_CHAR(END_DATE,'YYYY-MM-DD') THEN '대여중' ELSE '대여 가능' END) AS AVAILABILITY)
• CAR_ID를 기준으로 내림차순 정렬합니다.

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

SELECT
    EXTRACT(MONTH FROM S.START_DATE) AS MONTH,  -- 월을 숫자로 바로 추출
    S.CAR_ID,
    COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY S
JOIN (
    SELECT 
        CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE TO_CHAR(START_DATE, 'YYYY-MM') BETWEEN '2022-08' AND '2022-10'
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5
) R ON S.CAR_ID = R.CAR_ID
WHERE TO_CHAR(S.START_DATE, 'YYYY-MM') BETWEEN '2022-08' AND '2022-10'
GROUP BY EXTRACT(MONTH FROM S.START_DATE), S.CAR_ID
ORDER BY EXTRACT(MONTH FROM S.START_DATE) ASC, S.CAR_ID DESC;

• 2022년 8월부터 10월까지 대여횟수가 5회 이상인 자동차를 구하기 위해 CAR_ID를 기준으로 그룹화 한 후 HAVING 절을 통해 조건을 걸어 서브 테이블 R을 생성합니다.

(
    SELECT 
        CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE TO_CHAR(START_DATE, 'YYYY-MM') BETWEEN '2022-08' AND '2022-10'
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5
) R

• CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 CAR_ID를 기준으로 R과 JOIN합니다.
  (FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY S JOIN (...) R ON S.CAR_ID = R.CAR_ID)
• JOIN 조건에는 맞지만 START_DATE의 조건을 위해 한번 더 걸어줍니다.
  (WHERE TO_CHAR(S.START_DATE, 'YYYY-MM') BETWEEN '2022-08' AND '2022-10')
• 대여 월과 CAR_ID를 기준으로 그룹화합니다.
  월을 구하는 방법에는 EXTRACT(MONTH FROM S.START_DATE)를 이용합니다. => 숫자만 추출하기 위함입니다.
  (GROUP BY EXTRACT(MONTH FROM S.START_DATE), S.CAR_ID)
• 월을 기준으로 오름차순 정렬하고 자동차 ID를 기준으로 내림차순 정렬합니다.

자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

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;

• OPTION이 '통풍시트', '열선시트', '가죽시트'를 구하기 위해 like절을 이용합니다. (OPTIONS가 VARCHAR로 이루어져있기 때문에 like 절을 통해 조회합니다)
  (WHERE OPTIONS LIKE '%통풍시트%' 
     OR OPTIONS LIKE '%열선시트%' 
     OR OPTIONS LIKE '%가죽시트%')
• 자동차 종류 별로 묶기 위해 그룹화합니다.
  (GROUP BY CAR_TYPE)
• 자동차 종류를 기준으로 오름차순 정렬합니다.

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

SELECT
    I.INGREDIENT_TYPE,
    SUM(F.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;

• 상반기 주문정보와 아이스크림 정보를 FLAVOR를 통해 JOIN합니다.
  (FROM FIRST_HALF F JOIN ICECREAM_INFO I ON F.FLAVOR = I.FLAVOR)
• 아이스크림 맛을 기준으로 그룹화합니다.
  (GROUP BY I.INGREDIENT_TYPE)
• 상반기 아이스크림 총 주문량의 합을 위해 SUM 함수를 사용하고 총 주문량를 기준으로 오름차순 정렬합니다.

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

SELECT
    MCDP_CD AS "진료과 코드", 
    COUNT(*) AS "5월예약건수"
FROM APPOINTMENT
WHERE TO_CHAR(APNT_YMD, 'YYYY-MM') = '2022-05'
GROUP BY MCDP_CD
ORDER BY "5월예약건수", "진료과 코드"

• 2022년 05월에 예약한 환자를 위해 테이블에 조건을 걸어줍니다.
  (WHERE TO_CHAR(APNT_YMD, 'YYYY-MM') = '2022-05')
• 진료과 별로 그룹화하기 위해 MCDP_CD를 기준으로 그룹화합니다.
  (GROUP BY MCDP_CD)
• 예약 수를 구하기 위해 COUNT 함수를 사용하고 예약 수와 진료과 코드를 기준으로 오름차순 정렬합니다.

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

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

• 완료된 중고거래에서 회원별 중고 물품을 판매한 총 금액이 70만원이 넘는 레코드를 조회하기 위해 서브 테이블 R을 생성합니다.

(
    SELECT
        WRITER_ID,
        SUM(PRICE) AS TOTAL_SALES
    FROM USED_GOODS_BOARD B
    WHERE B.STATUS = 'DONE'
    GROUP BY WRITER_ID
    HAVING SUM(PRICE) >= 700000
)R

• USED_GOODS_BOARD B와 R에 회원 아이디를 기준으로 JOIN합니다.
  (FROM USED_GOODS_USER U JOIN (...)R ON U.USER_ID = R.WRITER_ID)
• 총 판매액을 기준으로 오름차순 정렬합니다.