SQL Common Table ExpressionsSQL CTE

-- 서브쿼리 vs CTE 비교
-- 서브쿼리 (가독성 낮음)
SELECT * FROM (
  SELECT user_id, COUNT(*) as order_count
  FROM orders WHERE status = 'completed'
  GROUP BY user_id
) t WHERE order_count >= 5;

-- CTE (가독성 높음)
WITH completed_orders AS (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
)
SELECT u.name, co.order_count
FROM completed_orders co
JOIN users u ON u.id = co.user_id
WHERE co.order_count >= 5;

-- 다중 CTE (순차 참조 가능)
WITH
revenue AS (
  SELECT product_id, SUM(amount) AS total
  FROM sales GROUP BY product_id
),
ranked AS (
  SELECT *, RANK() OVER (ORDER BY total DESC) AS rnk
  FROM revenue
)
SELECT * FROM ranked WHERE rnk <= 10;

-- 조직도 계층 조회 (재귀)
WITH RECURSIVE org_tree AS (
  -- 앵커 멤버 (기저): CEO
  SELECT id, name, manager_id, 0 AS depth, name AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 재귀 멤버: 하위 직원
  SELECT e.id, e.name, e.manager_id,
         ot.depth + 1,
         ot.path || ' > ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 10  -- 무한루프 방지
)
SELECT depth, path, name FROM org_tree ORDER BY path;

-- 피보나치 수열 (재귀 CTE)
WITH RECURSIVE fib(n, a, b) AS (
  SELECT 0, 0, 1
  UNION ALL
  SELECT n+1, b, a+b FROM fib WHERE n < 10
)
SELECT n, a AS fibonacci FROM fib;
-- 0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55

-- MATERIALIZED: 항상 별도 실행 (캐시됨)
-- NOT MATERIALIZED: 인라인 최적화 허용 (기본값)
WITH expensive_calc AS MATERIALIZED (
  SELECT user_id, complex_function(data) AS result
  FROM large_table
)
SELECT * FROM expensive_calc WHERE result > 100
UNION ALL
SELECT * FROM expensive_calc WHERE result < 0;
-- expensive_calc는 한 번만 실행됨

-- INSERT/UPDATE/DELETE와 함께 (PostgreSQL)
WITH new_users AS (
  INSERT INTO users (email, name)
  SELECT email, name FROM staging_users
  RETURNING id, email
)
INSERT INTO audit_log (user_id, action)
SELECT id, 'created' FROM new_users;