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

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

📌문제

• ​테이블 : CAR_RENTAL_COMPANY_RENTAL_HISTORY
• 조건
  • start_date 기준: 2022년 8월 1일 ~ 2022년 10월 31일
  • 해당 기간 동안 대여 횟수가 5회 이상인 자동차(CAR_ID)만
  • 이 조건을 만족하는 차들에 대해 월별(Car ID별) 대여 회수 출력
  • 컬럼명 : RECORDS
• 정렬
  • 월 기준 오름차순, CAR_ID 기준 내림차순
• 주의
  • 특정 월에 대여가 0회이면 결과에서 제외

💡 정답 쿼리 1

CTE 쿼리

WITH ValidCars AS (
    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(*) >= 5
)
SELECT MONTH(start_date) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM car_rental_company_rental_history
WHERE start_date BETWEEN '2022-08-01' AND '2022-10-31' -- 다시 조건 걸기
    AND car_id IN (SELECT car_id FROM ValidCars)
GROUP BY MONTH, CAR_ID
ORDER BY MONTH, CAR_ID DESC;

서브쿼리로 해야 하는 이유 having절 바깥에 쓰면 안되는 이유

여기서 어려웠던 점은 "총 대여 횟수 5회 이상" 조건을 어디에서 걸 것인가? 이다.
질문은 이거다. "왜 인라인 서브쿼리를 써서 CAR_ID를 필터링해야 하는가? 그냥 바깥 HAVING에서 COUNT를 걸면 안 되는가?

바깥 GROUP BY는 "월별 + CAR_ID별"이다.

GROUP BY MONTH, CAR_ID

즉, 이 시점에서는 "월별로 CAR_ID별 대여 횟수"를 구하고 있는 것이다.
이제 여기서 HAVING COUNT(*) >= 5라고 하면 무슨 의미일까? 이건 "각 월마다 해당 CAR_ID가 5회 이상 대여되었는가"를 의미하게 된다.
→ 우리가 원하는 조건(전체 기간 기준 5회 이상)과는 다르다.

예를 들면:

원본 데이터 일부:

→ CAR_ID 1은 전체 6회 대여했지만,

• 8월: 2회
• 9월: 1회
• 10월: 3회

이제 아래처럼 하면?

GROUP BY MONTH(start_date), CAR_ID
HAVING COUNT(*) >= 5

→ 8월도, 9월도, 10월도 각각 5회 미만이라 결과에 아예 안 나옴
→ 즉, 우리가 원하는 "총 5회 이상인 차를 월별로 보여줘"가 아님

그래서 인라인 서브쿼리로 먼저 CAR_ID 선별하는 것이다.

SELECT CAR_ID
FROM ...
GROUP BY CAR_ID
HAVING COUNT(*) >= 5

이렇게 하면 전체 기간 기준으로 5회 이상 대여된 차들만 뽑을 수 있다. 그 다음에야 바깥에서 CAR_ID IN (...)으로 선별된 차들만 가지고 월별 대여 횟수 세는 게 가능하다.

왜 having count(*) >= 5면 각 월마다 5회 이상인가

그럼 여기서 "왜 HAVING COUNT(*) >= 5를 쓰면 각 월마다 5회 이상인지 되는 거냐"는 생각이 든다. 그 이유는 바로 GROUP BY가 어떻게 되어 있느냐에 달려 있다. 정확히 말하면, GROUP BY MONTH(start_date), CAR_ID로 묶었기 때문에 집계 함수(COUNT)는 그 그룹에만 작동한다.

📌 핵심 개념: GROUP BY가 뭘 기준으로 묶느냐에 따라 HAVING 조건이 달라진다.

SELECT MONTH(start_date), 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(start_date), CAR_ID
HAVING COUNT(*) >= 5;

이 쿼리에서 일어나는 일을 살펴 보자

1. 먼저, start_date 기준으로 MONTH(start_date)로 월별로 나눈다.
2. 그 안에서 CAR_ID별로 다시 그룹화한다.
3. 즉, 그룹은 이런 형태를 띄게 된다.
   • (2022-08, CAR_ID 1)
   • (2022-08, CAR_ID 2)
   • (2022-09, CAR_ID 1)
   • ...
4. 그리고 COUNT(*)는 그 그룹 안의 행 개수만 센다!

그래서 왜 월별 5회 조건이 되는가?

• HAVING COUNT(*) >= 5는 현재 쿼리 그룹(= 월 + CAR_ID)에 대해서만 작동함
• 즉, "이 월, 이 차(CAR_ID)에서 대여가 5번 이상인가?"를 묻는 거야
• 우리가 원하는 "해당 차가 전체 기간(8~10월) 동안 총 5회 이상 대여되었는가?"와는 다른 기준이다.

쉽게 비유하면...

전체 학생들 중에서 총 시험 5번 이상 본 애들을 보고 싶은데 GROUP BY 학년, 학생 해놓고 "시험 5번 이상?" 이라고 물어보면,
이 결과는 "1학년 때 시험 5번 이상?" "2학년 때는?" 같은 식이 되는 것이다. 그래서 해결책은 "총 대여 5회 이상인 차"를 먼저 필터링해야 한다. → 이건 월 단위가 아니라 CAR_ID 단위로만 GROUP BY 해야 한다. 그래서 아래와 같은 서브쿼리나 CTE가 필요하다.

-- 해당 기간 동안 총 5회 이상 대여된 차만 뽑기
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(*) >= 5

이렇게 뽑은 CAR_ID만 가지고 다시 월별 그룹핑을 해야 정확한 결과가 나오는 것이다.

바깥 쿼리에도 start_date 날짜 조건 써야 하는 이유

WITH절에서 이미 날짜 조건을 줬는데, 왜 바깥 WHERE절에서 start_date BETWEEN ...을 또 써야 하는가에 대한 결론이다.

WITH절과 바깥 SELECT는 서로 별개의 쿼리이다.
WITH절은 단지 서브쿼리 결과를 저장한 것뿐이기 때문에,
바깥쪽 본문에서 다시 날짜 조건을 걸지 않으면 범위가 넓어진다.
즉, 전체 car_id를 대상으로 한다. 따라서 반드시 바깥 쿼리에도 start_date를 지정해 주어야 한다.

💡 정답 쿼리 2

WHERE절 서브쿼리

SELECT
    DATE_FORMAT(start_date, '%Y-%m') AS MONTH,
    CAR_ID,
    COUNT(*) AS RECORDS
FROM
    CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE
    start_date BETWEEN '2022-08-01' AND '2022-10-31'
    AND 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(*) >= 5
    )
GROUP BY
    MONTH,
    CAR_ID
ORDER BY
    MONTH ASC,
    CAR_ID DESC;

1. DATE_FORMAT(start_date, '%Y-%m') AS MONTH
   → 월 단위로 보기 쉽게 포맷 변환 (예: 2022-08)
2. CAR_ID IN (SELECT...)
   → 먼저 해당 기간 동안 5회 이상 대여된 CAR_ID만 필터링하는 서브쿼리
3. GROUP BY MONTH, CAR_ID
   → 월별 + 자동차별로 그룹화해서 대여 횟수 계산
4. ORDER BY MONTH ASC, CAR_ID DESC

start_date 조건은 이미 위에서 걸었는데 왜 WHERE 절에 CAR_ID IN (서브쿼리)를 또 써야 할까?

1. 서브쿼리

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(*) >= 5

• 특정 조건을 만족하는 CAR_ID만 추리는 역할
• 이 부분은 8~10월 사이에 대여 기록이 5건 이상인 차량(CAR_ID)만 추려내는 쿼리이다.

2. 메인 쿼리

• 위에서 추려진 차량들을 대상으로 다시 월별로 건수를 세는 것이다.
• 8월~10월 사이에 5번 이상 대여된 차량만 월별로 몇 건 대여됐는지 세는 것

서브쿼리 없이도 GROUP BY HAVING 쓰면 안돼?

못 쓴다. 왜냐면 HAVING COUNT(*) >= 5는 CAR_ID 전체 기준이기 때문에, 월별로 나눈 GROUP BY MONTH, CAR_ID와는 기준이 달라진다. 만약 HAVING을 메인 쿼리에서 쓰면, 그건 월별 대여 횟수가 5건 이상인 경우를 의미하게 된다. 전혀 다른 의미의 쿼리가 된다.