WITH 절

WITH 절

menu_book WITH 절이란?

• (영문)

The WITH clause lets you define a temporary result set (also called a CTE: Common Table Expression) that you can refer to within your main query.

• (한글)

WITH 절은 **임시 테이블(=서브쿼리 결과)**을 먼저 정의하고, 그걸 메인 쿼리에서 재사용할 수 있게 해주는 문법이에요.또한, CTE 라고 불려요

⸻

2. 왜 사용할까? • 복잡한 서브쿼리를 미리 이름 붙여서 정리할 수 있음 • 가독성 좋아짐 • 쿼리 재사용 가능 (같은 서브쿼리를 여러 번 쓰지 않아도 됨) • 중첩된 서브쿼리를 피할 수 있음

⸻

3. 기본 문법

WITH 임시이름 AS (
    서브쿼리
)
SELECT ...
FROM 임시이름;

⸻

4. 예제

예제 테이블: sales

id	product	amount	year
1	Laptop	2000	2023
2	Phone	1500	2023
3	Laptop	2200	2024
4	Phone	1800	2024
5	Laptop	2500	2024

⸻

예제 1: 2024년 판매 데이터만 따로 떼어서 분석

WITH sales_2024 AS (
    SELECT *
    FROM sales
    WHERE year = 2024
)
SELECT product, SUM(amount) AS total
FROM sales_2024
GROUP BY product;

결과

product total Laptop 4700 Phone 1800

설명: WITH sales_2024라는 임시 테이블을 만들어 2024년 데이터만 분리하고, 그걸 기준으로 제품별 총 판매액을 계산했어요.

⸻

예제 2: WITH 절을 두 개 이상 사용하는 경우

WITH 
sales_2023 AS (
    SELECT * FROM sales WHERE year = 2023
),
sales_2024 AS (
    SELECT * FROM sales WHERE year = 2024
)
SELECT '2023' AS year, product, SUM(amount) AS total
FROM sales_2023
GROUP BY product
UNION ALL
SELECT '2024' AS year, product, SUM(amount) AS total
FROM sales_2024
GROUP BY product;

결과

year product total 2023 Laptop 2000 2023 Phone 1500 2024 Laptop 4700 2024 Phone 1800

설명: 연도별로 따로 나눈 데이터를 UNION ALL로 합쳐서 보여줍니다.

⸻

5. WITH 절 vs 서브쿼리

항목 WITH 절 서브쿼리 가독성 매우 높음 복잡하면 읽기 어려움 재사용 가능 (한 번 정의해서 여러 번 사용) 동일 쿼리를 반복 작성해야 함 성능 Oracle이 내부적으로 최적화 가능 중복 계산 발생 시 성능 저하 가능

⸻

6. 주의사항 • Oracle 11g 이상부터 지원되며, Oracle 19c에서도 완벽히 사용 가능 • WITH 절은 SELECT, INSERT, UPDATE, DELETE에도 사용할 수 있음 • WITH 절 내 쿼리에는 ORDER BY를 직접 넣을 수 없음 (단, ROW_NUMBER()와 함께 정렬 가능)

⸻

요약 정리

개념 설명 WITH 절 서브쿼리를 임시 테이블처럼 이름 붙여 사용 장점 쿼리 재사용, 가독성 향상, 유지보수 용이 사용 예 복잡한 쿼리 정리, 여러 조건 조합 분석 등

좋습니다! 이번엔 Oracle 19c에서 사용할 수 있는 **고급 WITH 절 기능인 재귀 CTE (WITH RECURSIVE)**와 실전 활용 예제를 소개해드릴게요. Oracle에서는 RECURSIVE 키워드는 사용하지 않지만, 재귀적 WITH 사용은 가능합니다.

⸻

1. WITH 재귀 쿼리란? • **재귀 CTE(Common Table Expression)**는 WITH 절 안에서 자기 자신을 호출해서 계층적으로 데이터를 조회할 수 있는 기능입니다. • 주로 트리 구조, 부서-직원, 폴더-파일, 부모-자식 관계 등 계층형 데이터를 처리할 때 사용합니다.

⸻

2. 기본 구조

WITH cte_name (컬럼들) AS (
    -- anchor (기준이 되는 첫 번째 쿼리)
    SELECT ...
    FROM ...
    WHERE ...

    UNION ALL

    -- recursive (재귀적으로 자기 자신 호출)
    SELECT ...
    FROM 원본테이블 t
    JOIN cte_name c ON t.parent_id = c.id
)
SELECT * FROM cte_name;

⸻

3. 실전 예제: 조직도 계층 구조 조회

예제 테이블: employees

emp_id name manager_id 1 CEO NULL 2 Alice 1 3 Bob 1 4 Carol 2 5 David 2 6 Eve 3

⸻

목표

CEO → 부하 직원 → 그 밑 직원 순으로 전체 조직 계층도를 출력하고 싶어요.

⸻

쿼리

WITH org_chart (emp_id, name, manager_id, level) AS (
    -- Anchor: 최상위 관리자 (CEO)
    SELECT emp_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: 상위 직원의 부하직원들
    SELECT e.emp_id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT LPAD(' ', (level - 1) * 4) || name AS indented_name, level
FROM org_chart
ORDER BY level, emp_id;

⸻

결과

indented_name level CEO 1 Alice 2 Bob 2 Carol 3 David 3 Eve 3

설명: LPAD를 이용해서 직원 이름 앞에 공백을 넣어 들여쓰기 효과를 주었고, level을 이용해 계층을 구분했어요.

⸻

4. 실전 활용: 메뉴 구조, 카테고리 분류, 댓글 트리 등 • 게시판 댓글 (대댓글) • 온라인 쇼핑몰 카테고리 (대분류 > 중분류 > 소분류) • 파일 시스템 (폴더 > 하위 폴더 > 파일) • 사내 조직도

⸻

5. 주의사항 (Oracle 19c 기준)

항목 설명 RECURSIVE 키워드 Oracle에서는 쓰지 않습니다. PostgreSQL 같은 DBMS에서만 필요합니다. UNION ALL 필수 재귀 CTE는 **반드시 UNION ALL**로 이어야 합니다. LEVEL, CONNECT BY 기존의 Oracle 계층 쿼리 방식 (CONNECT BY)와도 유사한 역할을 합니다.

⸻

보너스: CONNECT BY vs WITH 재귀

특징 CONNECT BY WITH 재귀 문법 Oracle 전용 문법 표준 SQL 방식 (Oracle에서도 지원됨) 읽기 쉬움 간단하지만 복잡해지면 어려움 가독성 높음, 유연함 순서 제어 ORDER SIBLINGS BY 필요 그냥 ORDER BY 사용 가능 컬럼 추가 유연성 낮음 WITH에 원하는 컬럼 자유롭게 추가 가능

⸻ 좋습니다! Oracle 19c에서 유용하게 활용할 수 있는 실전 시나리오별 WITH 절 (특히 재귀 CTE 포함)을 예제 중심으로 소개해드릴게요. 아래 시나리오는 실제 업무나 프로젝트에서도 많이 쓰이는 상황들입니다.

⸻

시나리오 1: 조직도 조회 (부서장 → 직원 트리 구조)

상황 • 인사팀에서 조직도를 트리 형태로 보고 싶어합니다. • employees 테이블에는 emp_id, name, manager_id가 있고, 누가 누구의 상사인지 정보가 들어 있습니다.

쿼리 예제

WITH org AS (
    SELECT emp_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.name, e.manager_id, o.level + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.emp_id
)
SELECT LPAD(' ', (level - 1) * 4) || name AS name, level
FROM org
ORDER BY level, emp_id;

활용 목적: 사내 인트라넷 조직도, 상하 관계 파악, 평가 구조 설계

⸻

시나리오 2: 게시판 댓글 트리 조회

상황 • 게시판에서 댓글-대댓글 구조를 조회해야 합니다. • comments 테이블: comment_id, parent_id, content

쿼리 예제

WITH reply_tree AS (
    SELECT comment_id, parent_id, content, 1 AS depth
    FROM comments
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.comment_id, c.parent_id, c.content, r.depth + 1
    FROM comments c
    JOIN reply_tree r ON c.parent_id = r.comment_id
)
SELECT LPAD(' ', (depth - 1) * 4) || content AS content_tree
FROM reply_tree;

활용 목적: 커뮤니티 사이트, 포럼, 블로그 댓글 관리

⸻

시나리오 3: 제품 카테고리 분류 트리

상황 • 온라인 쇼핑몰에서 상품이 속한 카테고리를 트리로 관리하고 싶어요. • categories 테이블: category_id, parent_id, name

쿼리 예제

WITH category_tree AS (
    SELECT category_id, parent_id, name, 1 AS level
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.category_id, c.parent_id, c.name, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT LPAD(' ', (level - 1) * 2) || name AS category_path
FROM category_tree
ORDER BY level, name;

활용 목적: 카테고리 트리 메뉴 생성, 상품 등록 시 분류 관리

⸻

시나리오 4: 연속 날짜 생성 (캘린더 만들기)

상황 • 날짜 기준 보고서 작성 시 날짜 테이블이 없으면 어려움. • WITH 절로 임시 날짜 테이블을 생성해서 활용 가능.

쿼리 예제: 2024년 1월 1일~10일까지 날짜 생성

WITH dates (dt) AS (
    SELECT TO_DATE('2024-01-01', 'YYYY-MM-DD') FROM dual
    UNION ALL
    SELECT dt + 1 FROM dates
    WHERE dt + 1 <= TO_DATE('2024-01-10', 'YYYY-MM-DD')
)
SELECT dt FROM dates;

활용 목적: 일별 매출 집계, 출근/결근 현황 분석, 캘린더 생성

⸻

시나리오 5: 누적 매출 구하기 (누적합)

상황 • 시간 순으로 누적 매출을 보여줘야 하는 보고서. • sales 테이블: sale_date, amount

WITH daily_sales AS (
    SELECT sale_date, amount
    FROM sales
    WHERE sale_date BETWEEN DATE '2024-01-01' AND DATE '2024-01-10'
),
running_total AS (
    SELECT ds1.sale_date, ds1.amount,
        (SELECT SUM(ds2.amount)
         FROM daily_sales ds2
         WHERE ds2.sale_date <= ds1.sale_date) AS cumulative_total
    FROM daily_sales ds1
)
SELECT * FROM running_total
ORDER BY sale_date;

활용 목적: 기간 누적 매출, 사용자 활동 누적, 진도율 계산 등

⸻

실전 활용 요약

시나리오 핵심 목적 활용 분야 조직도 계층 구조 조회 인사 시스템 댓글 댓글-대댓글 구조 트리화 커뮤니티, 블로그 카테고리 분류 대/중/소 분류 트리화 쇼핑몰, 콘텐츠 플랫폼 날짜 생성 연속 날짜 생성 보고서, 캘린더, 출석부 누적 매출 시간 순 누적 값 계산 통계 보고서, 재무 데이터

⸻

어떤 시나리오가 가장 관심 있으신가요? 혹은 현재 작업 중인 데이터 구조에 맞춰 구체적인 쿼리를 만들어드릴 수도 있어요.