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;