[programmers] SQL Lv. 3 - 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/151139

프로그래머스

문제 설명
다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블입니다. CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID, CAR_ID, START_DATE, END_DATE 는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.

 

문제
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.

 

예시
예를 들어 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블이 다음과 같다면

대여 시작일을 기준으로 총 대여 횟수가 5회 이상인 자동차는 자동차 ID가 1, 2인 자동차입니다. 월 별 자동차 ID별 총 대여 횟수를 구하고 월 오름차순, 자동차 ID 내림차순으로 정렬하면 다음과 같이 나와야 합니다.

 

---

풀이

서브쿼리1: 월별 대여 기록

SELECT DATE_FORMAT(START_DATE, '%c') AS MONTH, 
    CAR_ID,
    COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY MONTH, CAR_ID

• 2022년 8월~10월 사이의 데이터를 조회
• `DATE_FORMAT(START_DATE, '%c') AS MONTH`로 대여 시작 월을 출력

✏️ DATE_FORMAT에서 월(Month) 표현
%M: Janeary, February, March, ..., December
%m: 01, 02, 03, ..., 12
%D: 1st, 2nd, 3rd, ..., 12th
%c: 1, 2, 3, ..., 12
%b: Jan, Feb, Mar, ..., Dec

SELECT DATE_FORMAT('2022-08-01', '%M') AS FullMonthName, -- August
       DATE_FORMAT('2022-08-01', '%m') AS TwoDigitMonth, -- 08
       DATE_FORMAT('2022-08-01', '%D') AS DayWithSuffix, -- 1st
       DATE_FORMAT('2022-08-01', '%c') AS NumericMonth,  -- 8
       DATE_FORMAT('2022-08-01', '%b') AS AbbrevMonth;   -- Aug

참고: https://velog.io/@donghoim/MySQL-DATETIME-%EC%9B%90%ED%95%98%EB%8A%94-%EC%9C%A0%ED%98%95%EC%9C%BC%EB%A1%9C-%EB%B3%80%EA%B2%BD-YYMMDD

 

서브쿼리2: 총 대여 횟수 계산

SELECT CAR_ID
FROM (
    SELECT DATE_FORMAT(START_DATE, '%c') AS MONTH,
        CAR_ID,
        COUNT(*) AS RECORDS
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY MONTH, CAR_ID
) TEMP
GROUP BY CAR_ID
HAVING SUM(RECORDS) >= 5

• 자동차 아이디(`CAR_ID`)별 전체 대여 횟수를 `SUM(RECORDS)`로 계산
• `HAVING`절에서 전체 대여 횟수가 5회 이상인 자동차만 필터링

 

최종 쿼리: 서브쿼리1 + 서브쿼리2

SELECT MONTH, CAR_ID, RECORDS
FROM (
    SELECT DATE_FORMAT(START_DATE, '%c') AS MONTH, 
        CAR_ID,
        COUNT(*) AS RECORDS
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY MONTH, CAR_ID
) SUB
WHERE CAR_ID IN (
    -- 생략(서브쿼리2의 결과)
)
ORDER BY CAST(MONTH AS UNSIGNED) ASC, CAR_ID DESC

• 서브쿼리1(`SUB`)에서 서브쿼리2의 결과(`CAR_ID IN ...`)에 만족하는 데이터만 출력
• `MONTH`에 오름차순, `CAR_ID`에 내림차순으로 정렬
  • `MONTH`는 문자형식(`VARCHAR`)이라 사전순 정렬이 된다. (1, 10, 11, 12, 2, 3, 4, ..., 9)
  • 문제의 예시에서는 숫자 정렬을 하고있으므로 `MONTH`를 숫자로 변환해서 정렬해야한다.
  • `CAST(MONTH AS UNSIGNED)`로 월 데이터를 숫자(`UNSIGNED`)로 변환한다.

참고 : https://hoehen-flug.tistory.com/27

 

---

제출

-- 코드를 입력하세요
SELECT MONTH, CAR_ID, RECORDS
FROM 
(
    SELECT DATE_FORMAT(START_DATE, '%c') AS MONTH, 
        CAR_ID,
        COUNT(*) AS RECORDS
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY MONTH, CAR_ID
) SUB
WHERE CAR_ID IN (
    SELECT CAR_ID
    FROM (
        SELECT DATE_FORMAT(START_DATE, '%c') AS MONTH,
            CAR_ID,
            COUNT(*) AS RECORDS
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
        GROUP BY MONTH, CAR_ID
    ) TEMP
    GROUP BY CAR_ID
    HAVING SUM(RECORDS) >= 5
)
ORDER BY CAST(MONTH AS UNSIGNED) ASC, CAR_ID DESC

 

---

다른 풀이 - 윈도우 함수

간결하게 푼 다른 풀이를 찾아보았다.

서브쿼리를 두 개 결합한 내 풀이보다 중복 코드가 없어 더 간결하고 가독성이 좋다.

SELECT 
    MONTH,                  
    CAR_ID,                 
    RECORDS                 
FROM (
    SELECT 
        DATE_FORMAT(START_DATE, '%c') AS MONTH,
        CAR_ID,
        COUNT(*) AS RECORDS,
        SUM(COUNT(*)) OVER (PARTITION BY CAR_ID) AS TOTAL_RECORDS	-- 자동차별 총 대여 횟수
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY MONTH, CAR_ID
) SUB
WHERE TOTAL_RECORDS >= 5	-- 총 대여 횟수가 5회 이상인 자동차 필터링
ORDER BY CAST(MONTH AS UNSIGNED) ASC, CAR_ID DESC;

• 윈도우 함수 `SUM(COUNT(*)) OVER (PARTITION BY CAR_ID) `
  • 각 `CAR_ID`별로 모든 월의 대여 횟수를 합산하여 총 대여 횟수를 계산 
  • `SUM`: 그룹 내에서 합계를 계산
  • `OVER`: 그룹을 정의하거나 순서를 지정하여 윈도우 함수를 적용
  • `PARTITION BY`: 데이터를 특정 기준으로 그룹화

2025.01.14 - [TIL (Today I Learned)] - [TIL] SQL 윈도우 함수 (Window Function)

[TIL] SQL 윈도우 함수 (Window Function)