RECURSIVE CTE란?

자기 자신을 반복적으로 호출해서
계층적(부모-자식) 구조 데이터를
단계적으로 탐색하는 SQL 구문

RECURSIVE 사용 전

SELECT COUNT(ID) AS COUNT ,GENERATION
FROM (SELECT ID,4 AS GENERATION
FROM ECOLI_DATA
WHERE PARENT_ID IN (SELECT ID
FROM ECOLI_DATA
WHERE PARENT_ID IN (SELECT ID
FROM ECOLI_DATA
WHERE PARENT_ID IN (SELECT ID
FROM ECOLI_DATA
WHERE PARENT_ID IS NULL)))

UNION

SELECT ID,3 AS GENERATION
FROM ECOLI_DATA
WHERE PARENT_ID IN (SELECT ID
FROM ECOLI_DATA
WHERE PARENT_ID IN (SELECT ID
FROM ECOLI_DATA
WHERE PARENT_ID IS NULL))

UNION

SELECT ID,2 AS GENERATION
FROM ECOLI_DATA
WHERE PARENT_ID IN (SELECT ID
FROM ECOLI_DATA
WHERE PARENT_ID IS NULL)

UNION

SELECT ID, 1 AS GENERATION
FROM ECOLI_DATA
WHERE PARENT_ID IS NULL) AS VIEW1
WHERE ID NOT IN (SELECT DISTINCT PARENT_ID
FROM ECOLI_DATA
WHERE PARENT_ID IS NOT NULL)
GROUP BY GENERATION
ORDER BY GENERATION 

RECURSIVE 사용 후

WITH RECURSIVE generations AS (
    SELECT ID, 1 AS GENERATION
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL

    UNION ALL

    SELECT e.ID, g.GENERATION + 1
    FROM ECOLI_DATA e
    JOIN generations g
    ON e.PARENT_ID = g.ID
)
SELECT COUNT(g.ID) AS COUNT, g.GENERATION
FROM generations g
LEFT JOIN ECOLI_DATA c
ON g.ID = c.PARENT_ID
WHERE c.ID IS NULL -- 자식이 없는 것만
GROUP BY g.GENERATION
ORDER BY g.GENERATION;

학습 계획

1️⃣ CTE 기본 개념 익히기
2️⃣ RECURSIVE CTE 문법 익히기
3️⃣ Self-Join 이해하기
4️⃣ 계층형 데이터 트리구조 연습하기
5️⃣ RECURSIVE CTE 예제 실습