[SQL] CTE (Common Table Expression)

 

개념

개념

조회 결과에 임시 이름을 붙여, 같은 SQL문 안에서 테이블처럼 사용하는 기능

조회 결과 작성

→ 결과에 임시 이름 지정

→ 뒤의 SQL문에서 테이블처럼 사용

 

실제 테이블로 저장되는 것은 아니고, 작성한 SQL문이 실행되는 동안에만 사용할 수 있음.

 

사용 의의

복잡한 조회 과정을 의미 있는 단계로 나눌 수 있음

=> 복잡한 서브쿼리를 여러 단계로 중첩하는 대신, 각 조회 결과에 이름을 붙여 위에서부터 읽을 수 있다.

 

EX]

-- 원본 데이터 조회 → 필요한 데이터 필터링 → 집계 → 최종 결과 조회
WITH 필터링_결과 AS (
    SELECT
        그룹_컬럼,
        숫자_컬럼
    FROM 테이블명
    WHERE 조건
),
집계_결과 AS (
    SELECT
        그룹_컬럼,
        SUM(숫자_컬럼) AS 합계
    FROM 필터링_결과
    GROUP BY 그룹_컬럼
)
SELECT
    그룹_컬럼,
    합계
FROM 집계_결과
WHERE 합계 >= 기준값;

-- 처리 단계
-- 1. 필터링_결과_생성
-- 2. 필터링_결과를 이용해 집계_결과_생성
-- 3. 집계_결과에서 최종 데이터 조회

EX] 원본 데이터 조회 → 필요한 데이터 필터링 → 집계 → 최종 결과 조회

 

비교 : 서브쿼리

구분	CTE	서브쿼리
작성 위치	SQL문 앞의 WITH절	SQL문 내부
구조	처리 단계를 위에서부터 나눔	사용하는 위치에 직접 작성
반복 참조	같은 SQL문 안에서 이름으로 참조 가능	같은 내용을 반복 작성할 수 있음
적합한 경우	복잡한 조회를 단계별로 정리	단순한 중간 조회

 

 

성능

주로 쿼리를 읽고 관리하기 쉽게 만드는 기능임.

CTE를 사용한다고 해서 자동으로 성능 향상이 되는 것은 아니다.

---

 

문법

기본 문법

WITH CTE명 AS (
    SELECT
        컬럼1,
        컬럼2,
        ...
    FROM 테이블명
    [WHERE 조건]
)
SELECT
    컬럼1,
    컬럼2,
    ...
FROM CTE명;

-- EX]
-- 특정 조건의 데이터를 구한 뒤, 그 결과를 다시 조회
WITH 대상_데이터 AS (
	SELECT
    	컬럼1,
        컬럼2
    FROM 테이블명
    WHERE 조건1
)
SELECT 
	컬럼1,
    컬럼2
FROM 대상_데이터
WHERE 조건2

 

 

 

-- EX] 완료된 주문 중 결제 금액이 50,000원 이상인 주문 조회
/**
orders]
| id | status    | total_price |
| -: | --------- | ----------: |
|  1 | completed |       30000 |
|  2 | completed |       70000 |
|  3 | cancelled |       90000 |
|  4 | completed |      120000 |
*/

WITH completed_orders AS (
	SELECT id, status, total_price
    FROM orders
    WHERE stats = 'completed'
)
SELECT id, status, total_price
FROM completed_orders
WHERE total_price >= 50000;

-- 실행과정 -- 
/**
1. CTE 내부의 SELECT 실행

결과)
| id | status    | total_price |
| -: | --------- | ----------: |
|  1 | completed |       30000 |
|  2 | completed |       70000 |
|  4 | completed |      120000 |

2. CTE결과에서 결제 금액이 50,000원 이상인 행만 조회

결과)
| id | status    | total_price |
| -: | --------- | ----------: |
|  2 | completed |       70000 |
|  4 | completed |      120000 |

*/

여러 CTE 정의

하나의 WITH절에서 여러 CTE정의 가능

각 CTE는 쉼표로 구분한다

WITH 첫_번째_CTE AS (
    SELECT ...
),
두_번째_CTE AS (
    SELECT ...
    FROM 첫_번째_CTE
)
SELECT ...
FROM 두_번째_CTE;

 

일반적으로 뒤에서 정의한 CTE는 앞에서 정의한 CTE를 참조 가능

 

EX]

-- EX] 완료된 주문을 회원별로 합산한 뒤, 총 주문금액이 100,000원 이상인 회원 조회

/**
orders)
| id | user_id | status    | total_price |
| -: | ------: | --------- | ----------: |
|  1 |       1 | completed |       30000 |
|  2 |       1 | completed |       80000 |
|  3 |       2 | cancelled |      120000 |
|  4 |       2 | completed |       70000 |
|  5 |       3 | completed |      150000 |
*/

WITH completed_orders AS ( -- 첫 번째 CTE : 완료된 주문만 조회
    SELECT
        user_id,
        total_price
    FROM orders
    WHERE status = 'completed'
),
/** 결과
| user_id | total_price |
| ------: | ----------: |
|       1 |       30000 |
|       1 |       80000 |
|       2 |       70000 |
|       3 |      150000 |
*/
user_order_totals AS ( -- 두번째 CTE : 회원별 완료 주문금액 합산 
    SELECT
        user_id,
        SUM(total_price) AS total_order_price
    FROM completed_orders
    GROUP BY user_id
)
/** 결과
| user_id | total_order_price |
| ------: | ----------------: |
|       1 |            110000 |
|       2 |             70000 |
|       3 |            150000 |
*/
SELECT
    user_id,
    total_order_price
FROM user_order_totals
WHERE total_order_price >= 100000;
/** 최종결과
| user_id | total_order_price |
| ------: | ----------------: |
|       1 |            110000 |
|       3 |            150000 |
*/

 

컬럼명 지정

-- CTE 내부에서 컬럼 별칭 지정 
WITH CTE명 AS (
    SELECT
        컬럼1 AS 결과_컬럼1,
        컬럼2 AS 결과_컬럼2
    FROM 테이블명
)
SELECT
    결과_컬럼1,
    결과_컬럼2
FROM CTE명;

-- CTE 이름 뒤에 결과 컬럼명 지정 가능 
-- 지정한 컬럼 개수는 CTE 내부 SELECT가 반환하는 컬럼 개수와 같아야 함
WITH CTE명 (
    결과_컬럼1,
    결과_컬럼2
) AS (
    SELECT
        컬럼1,
        컬럼2
    FROM 테이블명
)
SELECT
    결과_컬럼1,
    결과_컬럼2
FROM CTE명;

 

EX]

-- EX] 완료된 주문의 컬럼명을 변경하여 조회
/** 
orders)
| id | user_id | status    | total_price |
| -: | ------: | --------- | ----------: |
|  1 |      10 | completed |       30000 |
|  2 |      20 | cancelled |       50000 |
|  3 |      10 | completed |       70000 |
*/

WITH completed_orders AS (
    SELECT
        user_id AS customer_id,
        total_price AS order_amount
    FROM orders
    WHERE status = 'completed'
)
SELECT
    customer_id,
    order_amount
FROM completed_orders;

/** 결과
| customer_id | order_amount |
| ----------: | -----------: |
|          10 |        30000 |
|          10 |        70000 |
*/

// 아래와 같이 표현해도 됨
WITH completed_orders (
    customer_id,
    order_amount
) AS (
    SELECT
        user_id,
        total_price
    FROM orders
    WHERE status = 'completed'
)
SELECT
    customer_id,
    order_amount
FROM completed_orders;

재귀 CTE

개념

처음 데이터를 하나 만든 뒤, 방금 찾은 결과를 이용해 다음 데이터를 계속 찾는 방법

시작 데이터 만들기
        ↓
시작 데이터를 이용해 다음 데이터 찾기
        ↓
새로 찾은 데이터를 이용해 그다음 데이터 찾기
        ↓
더 이상 찾을 데이터가 없으면 종료

 

의의

일반적인 SELECT문은 한 번 실행하여 결과를 조회하는데, 

몇 번 반복해야 끝날지 미리 알기 어려운 데이터도 있음

 EX)

카테고리
└─ 하위 카테고리
   └─ 더 하위 카테고리
      └─ 더 더 하위 카테고리

 

단위가 몇개인지 모른다면 미리 JOIN 개수를 정하기가 어렵다. 

재귀 CTE는 같은 조회 규칙을 반복하여 끝 단계까지 자동으로 내려갈 수 있게 함. 

 

문법

WITH RECURSIVE CTE명 AS (
	-- 1. 시작 데이터
    초기_조회

    UNION ALL

	-- 2. 다음 데이터
    재귀_조회
)
SELECT ...
FROM CTE명;

 

구성	역할
초기 조회	반복을 시작할 첫 번째 데이터 생성
재귀 조회	이전 단계에서 찾은 결과를 이용해 다음 데이터 생성
UNION ALL	초기 결과와 반복해서 찾은 결과를 하나로 합침
종료 조건	더 이상 반복하지 않을 기준
최종 SELECT	재귀 CTE가 만든 전체 결과 조회

초기 조회
→ 시작 데이터 생성
        ↓
재귀 조회
→ 시작 데이터를 이용해 다음 데이터 생성
        ↓
재귀 조회 반복
→ 새로 생성된 데이터를 이용해 다음 데이터 생성
        ↓
종료 조건을 만족하면 중단
        ↓
지금까지 만든 결과를 모두 반환

 

활용

연속된 값 생성

WITH RECURSIVE 연속_값 AS (
    SELECT 시작값 AS 값

    UNION ALL

    SELECT 값 + 증가값
    FROM 연속_값
    WHERE 값 < 종료값
)
SELECT 값
FROM 연속_값;

-- 처리 과정
-- 1. 초기 조회에서 시작값 생성
-- 2. 재귀 조회가 이전 값에 증가값을 더함
-- 3. 종료 조건까지 반복
-- 4. 생성된 전체 결과 반환

 

EX]

-- EX] 1부터 5까지 연속된 숫자 생성
WITH RECURSIVE numbers AS ( -- 괄호 안에서 만들어지는 결과를 numbers라고 부름.
    -- 초기 조회: 첫 번째 값 생성
    SELECT 1 AS number -- 첫 번째 값인 1이 생성됨

    UNION ALL

    -- 재귀 조회: 이전 값에 1을 더함
    SELECT number + 1
    FROM numbers
    WHERE number < 5
)
SELECT number
FROM numbers;

/** 결과
| number |
| -----: |
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
*/

/**
[과정] 

1단계 : 시작값 만들기
SELECT 1 AS number -- 1을 반환하고, 결과 컬럼의 이름을 number로 지정
결과)
| number |
| -----: |
|      1 |


2단계 : 다음 값 만들기 (재귀 조회 실행)
SELECT number + 1 -- 만족하므로 계산 
FROM numbers
WHERE number < 5 -- numbers에서 현재 값 1 조건 만족

union all 을 해서 
| number |
| -----: |
|      1 |
|      2 |

조건이 만족할때까지 반복 
*/

 

계층형 데이터 조회

WITH RECURSIVE 계층_결과 AS (
    SELECT
        식별_컬럼,
        상위_식별_컬럼,
        조회_컬럼,
        1 AS 깊이
    FROM 테이블명
    WHERE 시작_조건

    UNION ALL

    SELECT
        t.식별_컬럼,
        t.상위_식별_컬럼,
        t.조회_컬럼,
        c.깊이 + 1
    FROM 테이블명 AS t
    JOIN 계층_결과 AS c
        ON t.상위_식별_컬럼 = c.식별_컬럼
)
SELECT
    식별_컬럼,
    상위_식별_컬럼,
    조회_컬럼,
    깊이
FROM 계층_결과;

-- 초기 조회에서 시작 항목을 찾고, 재귀 조회에서 그 항목의 하위 항목을 계속 찾는다.
/**
시작 항목
├─ 하위 항목
│  └─ 더 하위 항목
└─ 하위 항목
*/

 

 EX]

-- EX] 특정 카레고리부터 모든 하위 카테고리 조회

/** 
categories)
| id | parent_id | name    |
| -: | --------: | ------- |
|  1 |    `NULL` | 전자제품    |
|  2 |         1 | 컴퓨터     |
|  3 |         1 | 스마트폰    |
|  4 |         2 | 노트북     |
|  5 |         2 | 데스크톱    |
|  6 |         4 | 게이밍 노트북 |
|  7 |    `NULL` | 가구      |
|  8 |         7 | 의자      |

- parent_id => 해당 카테고리의 상위 카테고리 id를 저장함. 
전자제품(id: 1)
├─ 컴퓨터(id: 2)
│  ├─ 노트북(id: 4)
│  │  └─ 게이밍 노트북(id: 6)
│  └─ 데스크톱(id: 5)
└─ 스마트폰(id: 3)

가구(id: 7)
└─ 의자(id: 8)
*/

WITH RECURSIVE category_tree AS (
    -- 초기 조회: 시작 카테고리 조회
    SELECT
        id,
        parent_id,
        name,
        1 AS depth
    FROM categories
    WHERE id = 1

    UNION ALL

    -- 재귀 조회: 현재 카테고리의 하위 카테고리 조회
    SELECT
        t.id,
        t.parent_id,
        t.name,
        c.depth + 1
    FROM categories AS t -- 자식을 찾을 원본 categories 테이블
    JOIN category_tree AS c -- 이전 단계에서 찾은 category_tree 결과
        ON t.parent_id = c.id 
)
SELECT
    id,
    parent_id,
    name,
    depth
FROM category_tree
ORDER BY depth, id;

/** 결과
| id | parent_id | name    | depth |
| -: | --------: | ------- | ----: |
|  1 |    `NULL` | 전자제품    |     1 |
|  2 |         1 | 컴퓨터     |     2 |
|  3 |         1 | 스마트폰    |     2 |
|  4 |         2 | 노트북     |     3 |
|  5 |         2 | 데스크톱    |     3 |
|  6 |         4 | 게이밍 노트북 |     4 |
*/

/**
과정]

1단계 : 시작 항목 찾기 

| id | parent_id | name | depth |
| -: | --------: | ---- | ----: |
|  1 |    `NULL` | 전자제품 |     1 |

2단계 : 전자제품의 자식 찾기

parent_id = 1인 행 찾기 
| id | parent_id | name |
| -: | --------: | ---- |
|  2 |         1 | 컴퓨터  |
|  3 |         1 | 스마트폰 |

깊이는 부모의 깊이에 1을 더함
| id | parent_id | name | depth |
| -: | --------: | ---- | ----: |
|  1 |    `NULL` | 전자제품 |     1 |
|  2 |         1 | 컴퓨터  |     2 |
|  3 |         1 | 스마트폰 |     2 |

3단계 : 컴퓨터와 스마트폰의 자식 찾기
컴퓨터의 id = 2, 스마트폰의 id=3

원본 테이블에서 다음 조건을 만족하는 행을 찾기
parent_id=2인 행 or parent_id =3인 행

parent_id=2인 행
| id | parent_id | name |
| -: | --------: | ---- |
|  4 |         2 | 노트북  |
|  5 |         2 | 데스크톱 |

parent_id=3인 행 => 없음

현재까지 결과)
| id | parent_id | name | depth |
| -: | --------: | ---- | ----: |
|  1 |    `NULL` | 전자제품 |     1 |
|  2 |         1 | 컴퓨터  |     2 |
|  3 |         1 | 스마트폰 |     2 |
|  4 |         2 | 노트북  |     3 |
|  5 |         2 | 데스크톱 |     3 |

4단계 - 노트북과 데스트톱의 자식 찾기

직전에 찾은 항목의 id 사용
노트북의 id=4, 데스크톱의 id=5

원본 테이블에서 다음 행 찾기
parent_id = 4 or 5인 행

parent_id = 4인 행
| id | parent_id | name    |
| -: | --------: | ------- |
|  6 |         4 | 게이밍 노트북 |

parnet_id = 5인 행은 없음

현재까지의 결과)
| id | parent_id | name    | depth |
| -: | --------: | ------- | ----: |
|  1 |    `NULL` | 전자제품    |     1 |
|  2 |         1 | 컴퓨터     |     2 |
|  3 |         1 | 스마트폰    |     2 |
|  4 |         2 | 노트북     |     3 |
|  5 |         2 | 데스크톱    |     3 |
|  6 |         4 | 게이밍 노트북 |     4 |

5단계 - 재귀종료 

게이밍 노트북의 id = 6
원본 테이블에서 parent_id - 6인 행 찾기 => 없음 => 재귀 종료 

전체 흐름)
전자제품을 찾음
        ↓
전자제품의 자식 찾기
→ 컴퓨터, 스마트폰
        ↓
컴퓨터와 스마트폰의 자식 찾기
→ 노트북, 데스크톱
        ↓
노트북과 데스크톱의 자식 찾기
→ 게이밍 노트북
        ↓
게이밍 노트북의 자식 없음
        ↓
종료
*/