프로그래머스 SQL 풀이 오답노트(12): GROUP BY, DATE, WITH

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

1. 문제가 된 쿼리

WITH ID_CTE AS (
    SELECT HISTORY_ID, CAR_ID, START_DATE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE >= DATE '2022-08-01'
    AND START_DATE < DATE '2022-11-01'
),
COUNT_CTE AS (
    SELECT DISTINCT CAR_ID
    FROM ID_CTE
    GROUP BY CAR_ID
    HAVING COUNT(HISTORY_ID) >= 5
),
DATE_CTE AS (
    SELECT HISTORY_ID,
           CAR_ID,
            CASE
            WHEN TO_DATE(START_DATE, 'YYYY-MM') = DATE '2022-08' THEN 8
            WHEN TO_DATE(START_DATE, 'YYYY-MM') = DATE '2022-09' THEN 9
            WHEN TO_DATE(START_DATE, 'YYYY-MM') = DATE '2022-10' THEN 10
            END AS MON
    FROM ID_CTE 
)
SELECT MON, CAR_ID, COUNT(HISTORY_ID) AS RECORDS
FROM DATE_CTE
WHERE CAR_ID IN (SELECT CAR_ID FROM COUNT_CTE)
GROUP BY MON, CAR_ID
ORDER BY MON ASC, CAR_ID DESC;

ORA-01861: literal does not match format string

ORA-01861 오류

여기서 ORA-01861 error의 경우 날짜 형식 변환과 관련된 문제이다.

• 문제점
  START_DATE가 이미 DATE 타입인데 TO_DATE 함수를 사용하고 있는 것이 문제이다.
  TO_DATE는 문자열을 날짜로 변환할 때 사용하는 함수이기 때문이다.

2. 해결 (EXTRACT로 월 추출)

WITH ID_CTE AS (
    SELECT HISTORY_ID, CAR_ID, START_DATE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE >= DATE '2022-08-01' 
    AND START_DATE < DATE '2022-11-01'
),
COUNT_CTE AS (
    SELECT DISTINCT CAR_ID 
    FROM ID_CTE
    GROUP BY CAR_ID
    HAVING COUNT(HISTORY_ID) >= 5
),
DATE_CTE AS (
    SELECT HISTORY_ID, 
           CAR_ID, 
           EXTRACT(MONTH FROM START_DATE) AS MON
    FROM ID_CTE
)
SELECT MON, 
       CAR_ID, 
       COUNT(HISTORY_ID) AS RECORDS
FROM DATE_CTE
WHERE CAR_ID IN (SELECT CAR_ID FROM COUNT_CTE)
GROUP BY MON, CAR_ID
ORDER BY MON ASC, CAR_ID DESC;

3. 더 최적화된 쿼리

• JOIN을 사용하여 인덱스를 활용할 수 있다.

WITH COUNT_CTE AS (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE >= DATE '2022-08-01' 
    AND START_DATE < DATE '2022-11-01'
    GROUP BY CAR_ID
    HAVING COUNT(HISTORY_ID) >= 5
)
SELECT EXTRACT(MONTH FROM START_DATE) AS MON, 
       R.CAR_ID, 
       COUNT(R.HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY R
INNER JOIN COUNT_CTE C ON R.CAR_ID = C.CAR_ID
WHERE R.START_DATE >= DATE '2022-08-01' 
AND R.START_DATE < DATE '2022-11-01'
GROUP BY EXTRACT(MONTH FROM R.START_DATE), R.CAR_ID
ORDER BY MON ASC, R.CAR_ID DESC;

4. 언제 CTE를 써야 할까?

사용 사례	CTE 사용	서브쿼리 사용
동일한 데이터를 여러 번 참조할 때	✅ 유리함	🚫 비효율적
단순한 필터링과 GROUP BY	🚫 불필요	✅ 서브쿼리가 빠름
복잡한 계층 구조 (재귀 CTE)	✅ 필요	🚫 서브쿼리 불가능
성능 최적화가 필요할 때	🚫 조심해야 함	✅ INLINE 최적화 유리

실제 실행 계획 비교 (EXPLAIN ANALYZE)

CTE를 사용할 때와 서브쿼리를 사용할 때, EXPLAIN ANALYZE로 실행 계획을 확인하면 성능 차이를 볼 수 있음.

EXPLAIN ANALYZE
WITH CTE_EXAMPLE AS (
    SELECT CAR_ID, COUNT(HISTORY_ID) AS RENT_COUNT
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE >= DATE '2022-08-01' 
    AND START_DATE < DATE '2022-11-01'
    GROUP BY CAR_ID
)
SELECT * FROM CTE_EXAMPLE WHERE RENT_COUNT >= 5;

실행 계획을 보면 CTE가 TEMP TABLE로 MATERIALIZED 되는지 확인 가능.
만약 TEMP TABLE이 생성된다면 불필요한 디스크 I/O가 발생하여 속도가 느려질 가능성이 높음.

CTE의 성능 문제: MATERIALIZED vs. INLINE

일반적으로 CTE는 두 가지 방식으로 동작할 수 있음:

1. MATERIALIZED (실제 임시 테이블로 저장됨)

   • 쿼리가 실행될 때 CTE 결과를 한 번 계산한 후 캐싱하여 재사용
   • 같은 CTE를 여러 번 참조할 경우 유리
   • 하지만 최적화되지 않으면 불필요한 저장과 읽기로 인해 성능이 저하될 수 있음

2. INLINE (일반 서브쿼리처럼 동작)

   • 데이터베이스가 CTE를 일반 서브쿼리처럼 풀어서 실행
   • 불필요한 임시 저장 없이 최적화 가능
   • 일부 DB에서는 자동으로 INLINE 처리하지 않음 → MATERIALIZED 강제 적용으로 성능 저하 가능성 있음

**Oracle에서는 기본적으로 CTE가 INLINE 처리되지 않고, MATERIALIZED 되는 경우가 많음**.

5. MS SQL SERVER

WITH ID_CTE AS (
    SELECT HISTORY_ID, CAR_ID, START_DATE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE >= '2022-08-01'
    AND START_DATE < '2022-11-01'
),
COUNT_CTE AS (
    SELECT DISTINCT CAR_ID
    FROM ID_CTE
    GROUP BY CAR_ID
    HAVING COUNT(HISTORY_ID) >= 5
),
DATE_CTE AS (
    SELECT HISTORY_ID,
           CAR_ID,
           MONTH(START_DATE) AS MON  -- EXTRACT(MONTH FROM START_DATE) 와 같은 문법은 ORACLE용임.
    FROM ID_CTE 
)
SELECT MON AS MONTH, CAR_ID, COUNT(HISTORY_ID) AS RECORDS
FROM DATE_CTE
WHERE CAR_ID IN (SELECT CAR_ID FROM COUNT_CTE)
GROUP BY MON, CAR_ID
ORDER BY MON ASC, CAR_ID DESC;