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;

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;

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

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;

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;

-- 직원 테이블
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);

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;

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

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;

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;

-- 카테고리 테이블 (자기 참조)
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;

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

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;

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;

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

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

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

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

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