CTE(Common Table Expression) 완벽 이해 가이드

🎯 CTE란?

**CTE (Common Table Expression)**는 **"임시 테이블"**을 만드는 SQL 문법입니다.

쉬운 비유

• 서브쿼리의 업그레이드 버전
• 변수처럼 중간 결과를 저장하고 재사용
• 함수처럼 복잡한 로직을 단순화

---

📝 기본 문법

 

sql

WITH 임시테이블명 AS (
    SELECT 쿼리...
)
SELECT * FROM 임시테이블명;

실제 예시

 

sql

-- 기존 복잡한 쿼리
SELECT *
FROM (
    SELECT customer_id, SUM(amount) as total
    FROM orders 
    WHERE order_date >= '2023-01-01'
    GROUP BY customer_id
) sub
WHERE total > 1000;

-- CTE로 깔끔하게
WITH customer_totals AS (
    SELECT customer_id, SUM(amount) as total
    FROM orders 
    WHERE order_date >= '2023-01-01'
    GROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE total > 1000;

---

🔄 CTE vs 서브쿼리 비교

서브쿼리 방식 (복잡함)

 

sql

-- 서브쿼리: 읽기 어려움
SELECT c.name, o.total_amount
FROM customers c
INNER JOIN (
    SELECT customer_id, SUM(amount) as total_amount
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
) o ON c.id = o.customer_id
WHERE o.total_amount > 500;

CTE 방식 (깔끔함)

 

sql

-- CTE: 읽기 쉬움
WITH completed_orders AS (
    SELECT customer_id, SUM(amount) as total_amount
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT c.name, co.total_amount
FROM customers c
INNER JOIN completed_orders co ON c.id = co.customer_id
WHERE co.total_amount > 500;

---

🚀 CTE의 장점

1. 가독성 향상

 

sql

-- 단계별 명확한 로직
WITH step1 AS (데이터 추출),
     step2 AS (데이터 가공),
     step3 AS (최종 집계)
SELECT * FROM step3;

2. 재사용 가능

 

sql

WITH popular_products AS (
    SELECT product_id, COUNT(*) as order_count
    FROM orders
    GROUP BY product_id
    HAVING COUNT(*) > 100
)
-- 같은 CTE를 여러 번 사용 가능
SELECT p1.product_id, p1.order_count
FROM popular_products p1
UNION ALL
SELECT p2.product_id, p2.order_count  
FROM popular_products p2
WHERE p2.order_count > 200;

3. 복잡한 로직 단순화

 

sql

WITH 
-- 1단계: 월별 매출
monthly_sales AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') as month,
           SUM(amount) as monthly_total
    FROM orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
-- 2단계: 전월 대비 증감
sales_growth AS (
    SELECT month, monthly_total,
           LAG(monthly_total) OVER (ORDER BY month) as prev_month,
           monthly_total - LAG(monthly_total) OVER (ORDER BY month) as growth
    FROM monthly_sales
)
-- 3단계: 최종 결과
SELECT month, monthly_total, growth,
       ROUND(growth / prev_month * 100, 2) as growth_rate
FROM sales_growth
WHERE prev_month IS NOT NULL;

---

🔢 CTE 유형별 설명

1. 일반 CTE (Single CTE)

 

sql

WITH high_value_customers AS (
    SELECT customer_id, SUM(amount) as total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(amount) > 1000
)
SELECT c.name, hvc.total_spent
FROM customers c
INNER JOIN high_value_customers hvc ON c.id = hvc.customer_id;

2. 다중 CTE (Multiple CTEs)

 

sql

WITH 
sales_2023 AS (
    SELECT * FROM orders WHERE YEAR(order_date) = 2023
),
top_customers AS (
    SELECT customer_id, SUM(amount) as total
    FROM sales_2023
    GROUP BY customer_id
    ORDER BY total DESC
    LIMIT 10
)
SELECT * FROM top_customers;

3. 재귀 CTE (Recursive CTE)

 

sql

WITH RECURSIVE countdown AS (
    -- 시작값
    SELECT 10 as num
    
    UNION ALL
    
    -- 재귀 부분  
    SELECT num - 1
    FROM countdown
    WHERE num > 1
)
SELECT * FROM countdown;
-- 결과: 10, 9, 8, 7, 6, 5, 4, 3, 2, 1

---

💼 실무 활용 사례

1. 고객 세그멘테이션

 

sql

WITH customer_metrics AS (
    SELECT 
        customer_id,
        COUNT(*) as order_count,
        SUM(amount) as total_spent,
        AVG(amount) as avg_order_value,
        MAX(order_date) as last_order_date
    FROM orders
    GROUP BY customer_id
),
customer_segments AS (
    SELECT *,
        CASE 
            WHEN total_spent > 5000 AND order_count > 10 THEN 'VIP'
            WHEN total_spent > 1000 THEN 'Premium'
            WHEN order_count > 5 THEN 'Regular'
            ELSE 'New'
        END as segment
    FROM customer_metrics
)
SELECT segment, COUNT(*) as customer_count,
       AVG(total_spent) as avg_spent
FROM customer_segments
GROUP BY segment;

2. 매출 분석 대시보드

 

sql

WITH 
daily_sales AS (
    SELECT DATE(order_date) as sale_date,
           SUM(amount) as daily_total
    FROM orders
    WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
    GROUP BY DATE(order_date)
),
moving_average AS (
    SELECT sale_date, daily_total,
           AVG(daily_total) OVER (
               ORDER BY sale_date 
               ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
           ) as ma_7day
    FROM daily_sales
)
SELECT sale_date, daily_total, ma_7day,
       daily_total - ma_7day as variance
FROM moving_average
ORDER BY sale_date DESC;

---

🎯 코딩테스트에서의 CTE

자주 출제되는 패턴

1. 계층 구조 분석

 

sql

-- 카테고리별 상품 분석
WITH category_hierarchy AS (
    -- Recursive CTE로 카테고리 트리 생성
),
sales_by_category AS (
    -- 카테고리별 매출 집계
)
SELECT * FROM sales_by_category;

2. 시계열 데이터 분석

 

sql

-- 월별 성장률 분석
WITH monthly_metrics AS (
    -- 월별 지표 계산
),
growth_analysis AS (
    -- 전월 대비 증감률 계산
)
SELECT * FROM growth_analysis;

3. 복잡한 필터링

 

sql

-- 조건부 고객 분석
WITH qualified_customers AS (
    -- 1차 필터링
),
final_analysis AS (
    -- 2차 가공
)
SELECT * FROM final_analysis;

---

⚡ CTE 성능 팁

✅ 좋은 사용

 

sql

-- 인덱스 활용 가능한 조건
WITH filtered_data AS (
    SELECT * FROM large_table
    WHERE indexed_column = 'value'  -- 인덱스 활용
    AND date_column >= '2023-01-01'
)

❌ 피해야 할 사용

 

sql

-- 전체 테이블 스캔 유발
WITH all_data AS (
    SELECT * FROM large_table  -- 너무 큰 중간 결과셋
)

🔧 최적화 방법

 

sql

-- 1. 필요한 컬럼만 선택
WITH optimized AS (
    SELECT id, amount, date_column  -- 필요한 컬럼만
    FROM orders
    WHERE date_column >= '2023-01-01'  -- 조기 필터링
)

-- 2. 단계별 필터링
WITH step1 AS (
    SELECT * FROM table1 WHERE condition1  -- 1차 필터
),
step2 AS (
    SELECT * FROM step1 WHERE condition2   -- 2차 필터
)

---

🏆 CTE 마스터 체크리스트

초급 → 중급

• ✅ 기본 CTE 문법 이해
• ✅ 서브쿼리를 CTE로 변환 가능
• ✅ 다중 CTE 사용 가능

중급 → 고급

• ✅ Recursive CTE 활용
• ✅ 복잡한 비즈니스 로직을 CTE로 구조화
• ✅ 성능을 고려한 CTE 설계

쿠팡 BA 레벨

• ✅ 실무 상황을 CTE로 모델링
• ✅ 가독성과 성능의 균형점 찾기
• ✅ 팀원들이 이해하기 쉬운 코드 작성

핵심 요약: CTE는 복잡한 SQL을 단계별로 나눠서 읽기 쉽게 만드는 도구입니다. 복잡한 데이터 분석에서는 필수 스킬이에요! 🚀