CTE (Common Table Expression) | KAITRUST AI 백과사전

📖 상세 설명

CTE (Common Table Expression)는 SQL의 WITH 절을 사용하여 쿼리 내에서 임시로 이름이 있는 결과 집합을 정의하는 기능입니다. 서브쿼리를 쿼리 본문 앞에 정의해 재사용하고, 복잡한 쿼리를 논리적인 단위로 분리하여 가독성을 높입니다.

비재귀 CTE (Non-Recursive CTE)

일반적인 서브쿼리를 WITH 절로 분리하여 이름을 부여합니다. 동일한 서브쿼리를 여러 번 참조할 때 유용합니다.

재귀 CTE (Recursive CTE)

자기 자신을 참조하는 CTE로, 계층적 데이터(조직도, 폴더 구조, 카테고리 트리)를 처리할 때 필수적입니다. 기준 멤버(Base Case)와 재귀 멤버(Recursive Member)로 구성됩니다.

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

특성	CTE	서브쿼리	임시 테이블
정의 위치	쿼리 앞 (WITH)	쿼리 안	별도 CREATE
재사용	쿼리 내 여러 번	한 번만	세션 동안
재귀 지원	O	X	X (직접 구현)
인덱스	X	X	O
최적화	옵티마이저 결정	인라인	구체화됨

💻 코드 예제

기본 CTE - 여러 번 참조

-- 월별 매출 집계를 CTE로 정의
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS total_sales,
        COUNT(*) AS order_count
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY DATE_TRUNC('month', order_date)
)
-- CTE를 여러 번 참조하여 분석
SELECT
    month,
    total_sales,
    order_count,
    AVG(total_sales) OVER () AS avg_monthly_sales,
    total_sales - LAG(total_sales) OVER (ORDER BY month) AS growth
FROM monthly_sales
ORDER BY month;

다중 CTE - 복잡한 분석

WITH
-- 1단계: 고객별 주문 집계
customer_orders AS (
    SELECT
        customer_id,
        COUNT(*) AS total_orders,
        SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
-- 2단계: 고객 세그먼트 분류
customer_segments AS (
    SELECT
        customer_id,
        total_orders,
        total_spent,
        CASE
            WHEN total_spent >= 100000 THEN 'VIP'
            WHEN total_spent >= 50000 THEN 'Regular'
            ELSE 'Basic'
        END AS segment
    FROM customer_orders
),
-- 3단계: 세그먼트별 통계
segment_stats AS (
    SELECT
        segment,
        COUNT(*) AS customer_count,
        AVG(total_spent) AS avg_spent
    FROM customer_segments
    GROUP BY segment
)
-- 최종 결과: 세그먼트별 고객 목록과 통계
SELECT
    c.customer_id,
    c.segment,
    c.total_spent,
    s.customer_count,
    s.avg_spent,
    c.total_spent - s.avg_spent AS diff_from_avg
FROM customer_segments c
JOIN segment_stats s ON c.segment = s.segment
ORDER BY c.segment, c.total_spent DESC;

재귀 CTE - 조직도 (직원 계층 구조)

-- 직원 테이블 구조: id, name, manager_id
WITH RECURSIVE org_tree AS (
    -- 기준 멤버 (Base Case): 최상위 관리자
    SELECT
        id,
        name,
        manager_id,
        1 AS level,
        ARRAY[name] AS path,
        name AS full_path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 재귀 멤버 (Recursive Member): 하위 직원
    SELECT
        e.id,
        e.name,
        e.manager_id,
        o.level + 1,
        o.path || e.name,
        o.full_path || ' > ' || e.name
    FROM employees e
    JOIN org_tree o ON e.manager_id = o.id
)
SELECT
    REPEAT('  ', level - 1) || name AS hierarchy,
    level,
    full_path
FROM org_tree
ORDER BY path;

재귀 CTE - 카테고리 트리

-- 카테고리 무한 뎁스 조회
WITH RECURSIVE category_tree AS (
    -- 루트 카테고리
    SELECT
        id,
        name,
        parent_id,
        0 AS depth,
        CAST(id AS VARCHAR(1000)) AS sort_path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 하위 카테고리
    SELECT
        c.id,
        c.name,
        c.parent_id,
        ct.depth + 1,
        ct.sort_path || '/' || CAST(c.id AS VARCHAR(1000))
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
    WHERE ct.depth < 10  -- 무한 루프 방지
)
SELECT
    REPEAT('├─ ', depth) || name AS tree_view,
    depth,
    id
FROM category_tree
ORDER BY sort_path;

재귀 CTE - 날짜 시리즈 생성

-- 연속된 날짜 시리즈 생성 (30일)
WITH RECURSIVE date_series AS (
    SELECT CURRENT_DATE - INTERVAL '29 days' AS date

    UNION ALL

    SELECT date + INTERVAL '1 day'
    FROM date_series
    WHERE date < CURRENT_DATE
)
-- 날짜별 주문 집계 (주문 없는 날도 포함)
SELECT
    d.date,
    COALESCE(COUNT(o.id), 0) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_amount
FROM date_series d
LEFT JOIN orders o ON DATE(o.order_date) = d.date
GROUP BY d.date
ORDER BY d.date;

재귀 CTE - BOM (Bill of Materials) 전개

-- 제품 BOM 전개 (부품 구성표)
WITH RECURSIVE bom_explosion AS (
    -- 최상위 제품
    SELECT
        product_id,
        component_id,
        quantity,
        1 AS level,
        CAST(component_id AS VARCHAR(1000)) AS path
    FROM bill_of_materials
    WHERE product_id = 'PROD-001'

    UNION ALL

    -- 하위 부품
    SELECT
        b.product_id,
        b.component_id,
        b.quantity * bom.quantity AS total_quantity,
        bom.level + 1,
        bom.path || ' > ' || b.component_id
    FROM bill_of_materials b
    JOIN bom_explosion bom ON b.product_id = bom.component_id
    WHERE bom.level < 10
)
SELECT
    REPEAT('  ', level - 1) || component_id AS component,
    quantity AS total_needed,
    path
FROM bom_explosion
ORDER BY path;

💬 현업 대화 예시

신입 개발자

"이 쿼리에서 같은 서브쿼리가 3번이나 반복되는데, 어떻게 정리할 수 있을까요?"

시니어 개발자

"CTE로 빼. WITH customer_stats AS (...) 이렇게 정의하고 여러 번 참조하면 가독성도 좋아지고 옵티마이저가 알아서 최적화해줘."

백엔드 개발자

"댓글 대댓글 구조를 무한 뎁스로 조회해야 하는데, 반복문으로 DB 여러 번 호출하니까 느려요."

DBA

"재귀 CTE 써봐. WITH RECURSIVE로 한 번에 전체 트리 가져올 수 있어. 단, 무한루프 방지용으로 depth 제한 꼭 걸고."

데이터 분석가

"이 분석 쿼리가 200줄인데, 로직 파악이 어려워요. 테스트도 힘들고요."

시니어 분석가

"단계별로 CTE로 쪼개. 필터링 → 집계 → 변환 → 조인 이런 식으로 분리하면 각 단계 결과를 SELECT * FROM cte_name으로 확인할 수 있어."

⚠️ 주의사항

⚠️ 재귀 CTE 무한 루프

재귀 CTE에서 종료 조건이 없으면 무한 루프 발생! 반드시 WHERE 절로 종료 조건을 명시하고, depth나 level 컬럼으로 최대 깊이 제한을 걸어야 합니다. PostgreSQL은 기본 100회, SQL Server는 100회가 기본 제한입니다.

⚠️ 성능 고려사항

CTE는 데이터베이스마다 동작이 다릅니다. PostgreSQL 12 이전에는 항상 구체화(Materialized)되어 대용량 데이터에서 비효율적일 수 있습니다. MySQL 8.0 이상, SQL Server, Oracle은 옵티마이저가 인라인 여부를 결정합니다.

⚠️ CTE 구체화 제어 (PostgreSQL 12+)

WITH cte AS MATERIALIZED (...) 또는 AS NOT MATERIALIZED (...)로 구체화 여부를 명시적으로 제어할 수 있습니다. 대용량 데이터에서는 EXPLAIN으로 실행계획을 반드시 확인하세요.

CTE