[SQL] RECURSIVE CTE 이해하기

CTE란 무엇일까?

먼저, CTE(Common Table Expression)는 복잡한 쿼리를 단순화하고 가독성을 높이기 위해 일시적인 결과 집합을 정의하는 방법이다. CTE는 WITH 절을 사용하여 정의되며, 주로 재사용이 필요한 서브쿼리를 간결하게 표현할 때 유용하다.

CTE 기본 구조:

WITH CTE_이름 AS (
    -- CTE 내부의 쿼리 정의
    SELECT ...
)
SELECT * FROM CTE_이름;

---

Recursive CTE란?

Recursive CTE(재귀 공통 테이블 표현식)는 재귀적으로 데이터를 처리할 수 있는 CTE의 확장 버전입니다. 주로 계층적 데이터(예: 조직 구조, 트리 구조)를 탐색하거나, 반복적인 계산이 필요한 경우에 사용됩니다.

재귀 CTE의 구조:

WITH RECURSIVE CTE_이름 AS (
    -- Anchor Member: 재귀의 시작점
    SELECT ...
    UNION ALL
    -- Recursive Member: 재귀적으로 추가할 데이터
    SELECT ...
    FROM CTE_이름
    JOIN ...
    WHERE ...
)
SELECT * FROM CTE_이름;

• Anchor Member: 재귀의 시작점을 정의하는 쿼리
• Recursive Member: Anchor Member에서 생성된 결과를 기반으로 추가 데이터를 생성하는 쿼리
• UNION ALL: Anchor Member와 Recursive Member의 결과를 합칩. UNION 대신 UNION ALL을 사용하는 이유는 중복을 허용하여 모든 경로를 탐색하기 위함이다.

Recursive CTE 사용 예시

입양 시각 구하기(2)

문제 설명

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

NAME	TYPE	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_OUTCOME	VARCHAR(N)	FALSE

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

내 풀이

정답 풀이 1

WITH RECURSIVE cte (n)
AS (
    SELECT 0
    UNION ALL
    SELECT n+1
    FROM cte
    WHERE n < 23
)
SELECT cte.n, SUM(if(cte.n = HOUR(DATETIME), 1, 0)) COUNT
FROM cte, ANIMAL_OUTS
GROUP BY 1
ORDER BY 1

정답 풀이 2

with recursive time
as (
    select 0 as hour
    union all
    select hour + 1
    from time
    where hour < 23
)
select t.hour, count(animal_id)
from time t left join (
    select *, hour(DATETIME) hour
    from ANIMAL_OUTS 
) o on t.hour = o.hour 
group by t.hour
order by t.hour

숫자 세기

문제: 5부터 101까지 2씩 커지는 숫자 (5,7,9, 11,,, )숫자를 출력하는 Recursive CTE를 작성하세요.

WITH 숫자 AS(
	SELECT 5 AS 숫자
    UNION ALL
    SELECT 숫자 + 2 FROM 숫자 WHERE 숫자 < 101
)
SELECT * FROM 숫자;

조직의 계층 구조 찾기

회사에서 직원과 그들의 상사를 나타내는 테이블이 있을 때, 특정 직원의 모든 상사를 찾을 수 있다.

목표: Charlie의 모든 상사를 찾기

테이블 구조:

• employees
  - employee_id: 직원 ID
  - name: 직원 이름
  - manager_id: 상사 ID (상사가 없으면 NULL)

WITH 재귀_상사 AS (
    -- 초기 단계: Charlie의 정보부터 시작
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE name = 'Charlie'
    UNION ALL
    -- 재귀 단계: 현재 상사를 찾아 추가
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN 재귀_상사 rs ON e.employee_id = rs.manager_id
)
SELECT * FROM 재귀_상사;

폴더 및 하위 폴더 구조 탐색

문제: 파일 시스템의 폴더 테이블에서 특정 폴더의 모든 하위 폴더를 조회하세요.

테이블 구조:

• folders
  - folder_id: 폴더 ID
  - folder_name: 폴더 이름
  - parent_folder_id: 상위 폴더 ID (NULL이면 루트 폴더)

WITH RECURSIVE SubFolders AS (
    -- Anchor Member: Documents 폴더부터 시작
    SELECT folder_id, folder_name, parent_folder_id, 1 AS level
    FROM folders
    WHERE folder_name = 'Documents'
    UNION ALL
    -- Recursive Member: 현재 폴더의 하위 폴더를 추가
    SELECT f.folder_id, f.folder_name, f.parent_folder_id, sf.level + 1
    FROM folders f
    INNER JOIN SubFolders sf ON f.parent_folder_id = sf.folder_id
)
SELECT folder_id, folder_name, parent_folder_id, level
FROM SubFolders
ORDER BY level;

제품의 구성 요소 계층 조회

bill_of_materials 테이블에서 특정 제품(예: Product A)의 모든 구성 요소(직접 및 간접)를 계층적으로 조회하세요.

테이블 구조:

• bill_of_materials
  - product_id: 제품 ID
  - component_id: 구성 요소 ID
  - component_name: 구성 요소 이름
  - quantity: 필요한 수량

WITH RECURSIVE ComponentsHierarchy AS (
    -- Anchor Member: Product A의 직접 구성 요소부터 시작
    SELECT product_id, component_id, 
    component_name, quantity, 1 AS level
    FROM bill_of_materials
    WHERE product_id = 'A'
    UNION ALL
    -- Recursive Member: 현재 구성 요소의 하위 구성 요소를 추가
    SELECT bm.product_id, bm.component_id, bm.component_name, 
    bm.quantity, ch.level + 1
    FROM bill_of_materials bm
    INNER JOIN ComponentsHierarchy ch 
    ON bm.product_id = ch.component_id
)
SELECT component_id, component_name, quantity, level
FROM ComponentsHierarchy
ORDER BY level;

예상 결과:

component_id	component_name	quantity	level
B	Component B	2	1
C	Component C	3	1
D	Component D	4	2
E	Component E	1	2
F	Component F	5	2
G	Component G	2	3

Product A의 직접 구성 요소인 Component B와 Component C를 시작으로, Component B의 하위 구성 요소인 Component D와 Component E, Component C의 하위 구성 요소인 Component F를 재귀적으로 추가. 이어서 Component E의 하위 구성 요소인 Component G를 추가.

프로젝트 작업 순서 결정

tasks 테이블에서 프로젝트의 작업(Task)이 서로 의존 관계에 있을 때, 작업의 순서를 결정하세요. (즉, 어떤 작업이 다른 작업에 선행되어야 하는지 파악)

테이블 구조:

• tasks
  - task_id: 작업 ID
  - task_name: 작업 이름
  - depends_on: 선행 작업 ID (NULL이면 선행 작업 없음)

예시 데이터:

task_id	task_name	depends_on
1	Design	NULL
2	Development	1
3	Testing	2
4	Deployment	3
5	Documentation	1
6	Review	5

WITH RECURSIVE TaskOrder AS (
    -- Anchor Member: 선행 작업이 없는 작업부터 시작
    SELECT task_id, task_name, depends_on, 1 AS level
    FROM tasks
    WHERE depends_on IS NULL
    UNION ALL
    -- Recursive Member: 현재 작업을 선행하는 작업을 추가
    SELECT t.task_id, t.task_name, t.depends_on, to.level + 1
    FROM tasks t
    INNER JOIN TaskOrder to ON t.depends_on = to.task_id
)
SELECT task_id, task_name, depends_on, level
FROM TaskOrder
ORDER BY level;

예상 결과:

task_id	task_name	depends_on	level
1	Design	NULL	1
2	Development	1	2
5	Documentation	1	2
3	Testing	2	3
6	Review	5	3
4	Deployment	3	4

Anchor Member: 선행 작업이 없는 Design을 시작점으로 설정하고, level을 1로 지정.
Recursive Member: Design을 선행으로 하는 Development와 Documentation을 추가하고, level을 2로 증가.
이어서 Development의 선행 작업인 Testing, Documentation의 선행 작업인 Review를 추가하고, level을 3으로 증가.
마지막으로 Testing의 선행 작업인 Deployment를 추가하고, level을 4로 설정.

Recursive CTE를 이용한 피보나치 수열 생성

피보나치 수열 생성 (0부터 시작하여 10개의 숫자)

WITH RECURSIVE FibonacciCTE AS (
    -- Anchor Member: 첫 두 숫자 정의
    SELECT 
        0 AS n,
        0 AS fibonacci
    UNION ALL
    SELECT 
        1 AS n,
        1 AS fibonacci
    UNION ALL
    -- Recursive Member: 이전 두 숫자의 합을 계산
    SELECT 
        f1.n + 1 AS n,
        f1.fibonacci + f0.fibonacci AS fibonacci
    FROM 
        FibonacciCTE f1
    JOIN 
        FibonacciCTE f0 ON f1.n = f0.n + 1
    WHERE 
        f1.n < 9  -- 원하는 피보나치 수열의 길이에 맞게 조정
)
SELECT 
    n, 
    fibonacci
FROM 
    FibonacciCTE
ORDER BY 
    n;