SQL

CTE 사용법과 재귀함수

silver-w 2025. 5. 12. 15:23

□ 비교

  일반 CTE 재귀 CTE
개념 일시적으로 결과 집합을 변수처럼 저장해서
쿼리 전체에서 재사용 가능
자기 자신을 참조하며 반복적으로 실행 
→ 계층형 데이터를 쉽게 설계할 수 있게 함. 
(오라클에 start with silbling ~ 의 쓰임새와 동일)
형식 WITH CTE_NAME AS (
  서브쿼리
)
WITH RECURSIVE CTE_NAME AS (
  기본 쿼리
  UNION ALL
   재귀 쿼리
)
※ UNION ALL을 통해 루트에서 시작해
    자식 → 손자 ... 순으로 전개
CTE를 여러번 사용해야 하는 경우 

(1) 일반 CTE 여러개 사용

WITH

cte1 AS (...),  -- 첫 번째 CTE
cte2 AS (...),  -- 두 번째 CTE
cte3 AS (...)   -- 세 번째 CTE


(2) 재귀 CTE를 여러개 사용 / 재귀 CTE와 일반 CTE를 혼합해서 사용하는 경우 

WITH RECURSIVE

cte1 AS (),   -- 첫 번째 재귀 CTE
cte2 AS ()    -- 두 번째 재귀 CTE 혹은 일반 CTE

 

 

□ 프로그래머스 - 멸종위기의 대장균 문제관련 예시

 - 계층별로 select 하기 
 

(1) 세대별로 ID 정렬하기

1단계 : 최상위계층 노드 조회 → 2단계 : 자기 참조 및 테이블을 join 하여 재귀 조회
WITH RECURSIVE tmp AS (
    -- 1단계: 최상위 노드 탐색
    SELECT ID, PARENT_ID, 1 AS generation
    FROM ecoli_data
    WHERE PARENT_ID IS NULL
    
    UNION ALL

    -- 2단계 이상: 자기참조를 계속 하여 재귀
    SELECT c.ID, c.PARENT_ID, p.generation + 1
    FROM ecoli_data c
    JOIN tmp p ON c.PARENT_ID = p.ID
)

SELECT * FROM tmp;
 

 

 

(2) 세대별 자식이 없는 대장균 ID 조회

 세대별 자식이 없는 대장균 ID = 'PARENT_ID 중에 없는 ID'

WITH RECURSIVE 
tmp AS (
    -- 1단계 : 부모가 없는 루트 노드
    SELECT ID, PARENT_ID, 1 AS GENERATION
      FROM ecoli_data
     WHERE PARENT_ID IS NULL
    
    UNION ALL

    -- 2단계 : 자식을 따라가며 세대 증가
    SELECT c.ID, c.PARENT_ID, p.generation + 1
      FROM ecoli_data c
     inner JOIN tmp p ON c.PARENT_ID = p.ID
)

-- 3단계 : 부모ID에 해당되지 않는 ID 조회
SELECT COUNT(*) AS COUNT, GENERATION
  FROM tmp
 WHERE ID NOT IN (SELECT PARENT_ID
                    FROM tmp 
                   WHERE PARENT_ID IS NOT NULL)
 GROUP BY GENERATION
 ORDER BY GENERATION;