CTE 완전 정복: 서브쿼리 지옥에서 벗어나는 법

WITH 절 하나로 복잡한 쿼리가 읽기 쉬워진다. 재귀 CTE로 계층 구조도 한 방에.

TL;DR

• CTE (Common Table Expression): 쿼리 내 임시 테이블, WITH 절로 정의
• 가독성: 복잡한 서브쿼리를 이름 붙여 분리
• 재사용: 같은 CTE를 여러 번 참조 가능
• 재귀 CTE: 계층 구조, 연속 날짜 생성 등에 활용

1. CTE가 뭔가요?

서브쿼리의 문제

-- 읽기 어려운 중첩 서브쿼리
SELECT
    user_id,
    total_amount,
    user_avg,
    total_amount - user_avg as diff
FROM (
    SELECT
        user_id,
        SUM(amount) as total_amount,
        AVG(SUM(amount)) OVER () as user_avg
    FROM (
        SELECT
            user_id,
            amount
        FROM orders
        WHERE status = 'completed'
          AND created_at >= '2024-01-01'
    ) filtered_orders
    GROUP BY user_id
) user_totals
WHERE total_amount > user_avg;

뭐가 뭔지 모르겠죠?

CTE로 정리하면

WITH filtered_orders AS (
    SELECT user_id, amount
    FROM orders
    WHERE status = 'completed'
      AND created_at >= '2024-01-01'
),
user_totals AS (
    SELECT
        user_id,
        SUM(amount) as total_amount,
        AVG(SUM(amount)) OVER () as user_avg
    FROM filtered_orders
    GROUP BY user_id
)
SELECT
    user_id,
    total_amount,
    user_avg,
    total_amount - user_avg as diff
FROM user_totals
WHERE total_amount > user_avg;

각 단계가 명확하게 보입니다!

2. CTE 기본 문법

단일 CTE

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

다중 CTE

WITH
first_cte AS (
    SELECT ...
),
second_cte AS (
    SELECT * FROM first_cte  -- 이전 CTE 참조 가능
    WHERE ...
),
third_cte AS (
    SELECT * FROM second_cte
    JOIN first_cte ON ...    -- 여러 CTE 조인도 가능
)
SELECT * FROM third_cte;

3. 실전 예제: 매출 분석

문제: 월별 매출과 전월 대비 성장률

WITH monthly_sales AS (
    -- 1단계: 월별 매출 집계
    SELECT
        DATE_TRUNC('month', order_date) as month,
        SUM(amount) as revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', order_date)
),
sales_with_growth AS (
    -- 2단계: 전월 매출 가져오기
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) as prev_revenue
    FROM monthly_sales
)
-- 3단계: 성장률 계산
SELECT
    month,
    revenue,
    prev_revenue,
    ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) as growth_rate
FROM sales_with_growth
WHERE prev_revenue IS NOT NULL
ORDER BY month;

결과:

4. CTE vs 서브쿼리 vs 임시 테이블

언제 뭘 쓸까?

• 서브쿼리: 단순한 필터링, EXISTS 체크
• CTE: 복잡한 다단계 처리, 가독성 중요할 때
• 임시 테이블: 대용량 데이터, 인덱스 필요, 여러 쿼리에서 재사용

5. 재귀 CTE: 계층 구조 처리

조직도 예제

-- 직원 테이블
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT  -- 상사의 id (NULL이면 최고 경영자)
);

-- 샘플 데이터
INSERT INTO employees VALUES
(1, '김대표', NULL),
(2, '이부장', 1),
(3, '박과장', 2),
(4, '최대리', 3),
(5, '정사원', 4),
(6, '한부장', 1),
(7, '오과장', 6);

재귀 CTE로 조직도 출력

WITH RECURSIVE org_tree AS (
    -- Base case: 최상위 (대표)
    SELECT
        id,
        name,
        manager_id,
        1 as level,
        name as path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: 부하 직원들
    SELECT
        e.id,
        e.name,
        e.manager_id,
        ot.level + 1,
        ot.path || ' → ' || e.name
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT
    REPEAT('  ', level - 1) || name as org_chart,
    level,
    path
FROM org_tree
ORDER BY path;

결과:

6. 재귀 CTE: 날짜 시리즈 생성

문제: 데이터가 없는 날짜도 0으로 표시하고 싶다

-- 주문이 없는 날은 아예 안 나옴
SELECT DATE(order_date) as date, COUNT(*) as orders
FROM orders
GROUP BY DATE(order_date);

해결: 날짜 시리즈 생성 후 LEFT JOIN

WITH RECURSIVE date_series AS (
    -- 시작일
    SELECT DATE '2024-01-01' as date

    UNION ALL

    -- 하루씩 증가
    SELECT date + INTERVAL '1 day'
    FROM date_series
    WHERE date < '2024-01-31'
),
daily_orders AS (
    SELECT DATE(order_date) as date, COUNT(*) as orders
    FROM orders
    WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01'
    GROUP BY DATE(order_date)
)
SELECT
    ds.date,
    COALESCE(do.orders, 0) as orders
FROM date_series ds
LEFT JOIN daily_orders do ON ds.date = do.date
ORDER BY ds.date;

이제 주문이 0인 날도 표시됩니다!

7. 재귀 CTE: 누적 계산

일별 누적 매출

WITH RECURSIVE daily_sales AS (
    SELECT
        DATE(order_date) as date,
        SUM(amount) as daily_amount
    FROM orders
    GROUP BY DATE(order_date)
),
cumulative AS (
    -- 첫 날
    SELECT
        date,
        daily_amount,
        daily_amount as cumulative_amount,
        1 as day_num
    FROM daily_sales
    WHERE date = (SELECT MIN(date) FROM daily_sales)

    UNION ALL

    -- 다음 날들
    SELECT
        ds.date,
        ds.daily_amount,
        c.cumulative_amount + ds.daily_amount,
        c.day_num + 1
    FROM daily_sales ds
    JOIN cumulative c ON ds.date = c.date + INTERVAL '1 day'
)
SELECT * FROM cumulative ORDER BY date;

💡 실무에서는 `SUM() OVER (ORDER BY date)` 윈도우 함수가 더 효율적이지만, 재귀 CTE의 원리를 이해하는 데 좋은 예제입니다.

8. 카테고리 계층 구조

전체 카테고리 경로 구하기

-- 카테고리 테이블 (자기 참조)
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT
);

-- 샘플 데이터
INSERT INTO categories VALUES
(1, '전자제품', NULL),
(2, '컴퓨터', 1),
(3, '노트북', 2),
(4, '게이밍 노트북', 3),
(5, '의류', NULL),
(6, '남성의류', 5);

-- 전체 경로 구하기
WITH RECURSIVE category_path AS (
    SELECT
        id,
        name,
        parent_id,
        name as full_path,
        1 as depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT
        c.id,
        c.name,
        c.parent_id,
        cp.full_path || ' > ' || c.name,
        cp.depth + 1
    FROM categories c
    JOIN category_path cp ON c.parent_id = cp.id
)
SELECT id, name, full_path, depth
FROM category_path
ORDER BY full_path;

결과:

9. 무한 루프 방지

재귀 CTE는 잘못 작성하면 무한 루프에 빠질 수 있습니다.

안전 장치 1: LIMIT

WITH RECURSIVE bad_cte AS (
    SELECT 1 as n
    UNION ALL
    SELECT n + 1 FROM bad_cte  -- 종료 조건 없음!
)
SELECT * FROM bad_cte
LIMIT 100;  -- 안전 장치

안전 장치 2: 깊이 제한

WITH RECURSIVE safe_tree AS (
    SELECT id, name, parent_id, 1 as depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, st.depth + 1
    FROM categories c
    JOIN safe_tree st ON c.parent_id = st.id
    WHERE st.depth < 10  -- 최대 10단계까지만
)
SELECT * FROM safe_tree;

안전 장치 3: 방문 체크 (순환 참조 방지)

WITH RECURSIVE safe_tree AS (
    SELECT
        id,
        name,
        parent_id,
        ARRAY[id] as visited  -- 방문한 id 배열
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT
        c.id,
        c.name,
        c.parent_id,
        st.visited || c.id
    FROM categories c
    JOIN safe_tree st ON c.parent_id = st.id
    WHERE NOT c.id = ANY(st.visited)  -- 이미 방문했으면 스킵
)
SELECT * FROM safe_tree;

10. 실전 팁

1. CTE 이름은 명확하게

-- Bad
WITH a AS (...), b AS (...), c AS (...)

-- Good
WITH daily_orders AS (...),
     user_totals AS (...),
     top_customers AS (...)

2. 디버깅할 때 단계별 확인

WITH step1 AS (...),
     step2 AS (...),
     step3 AS (...)

-- 디버깅: 중간 단계 확인
SELECT * FROM step2;  -- step3 대신 step2 확인

3. 성능 고려

-- PostgreSQL: MATERIALIZED 힌트로 CTE 결과 저장
WITH MATERIALIZED expensive_calc AS (
    -- 복잡한 계산
)
SELECT * FROM expensive_calc a
JOIN expensive_calc b ON ...;  -- 두 번 참조해도 한 번만 계산

결론

CTE 핵심 포인트:

• WITH 절로 쿼리를 논리적 단계로 분리
• 이름만 잘 지어도 가독성 200% 향상
• 재귀 CTE로 계층 구조, 시퀀스 생성 가능
• 무한 루프 방지는 필수!

References

1. PostgreSQL Documentation - WITH Queries
2. MySQL 8.0 - Recursive CTE
3. SQL Server - Common Table Expressions