[프로그래머스]멸종위기의 대장균 찾기 ⭐WITH RECURSIVE 구문 공부

문제

https://school.programmers.co.kr/learn/courses/30/lessons/301651

이번 문제는 재귀적 CTE로 풀어야 된다.

재귀적 CTE란?!

어떤 작업을 수행할 때, 그 작업 안에서 자기 자신을 호출하는 것을 의미

풀이

1. 먼저 각 ID별 세대 구하기

WITH RECURSIVE G_1 AS(
    SELECT -- 최상위 노드 생성
        ID, PARENT_ID, 1 AS GENERATION
    FROM
        ECOLI_DATA
    WHERE 1=1
        AND PARENT_ID IS NULL
    
    UNION ALL
    
    SELECT -- 재귀 쿼리 생성
        ED.ID, ED.PARENT_ID, G_1.GENERATION+1 AS GENERATION
    FROM
        ECOLI_DATA ED
        INNER JOIN G_1
            ON ED.PARENT_ID=G_1.ID -- 현재 노드의 PARENT_ID가 이전 단계 노드의 ID와 일치하는 경우 
)

2. 자식이 없는 개체 구하기
   ID컬럼이 PARENT_ID컬럼에 없으면 됨.

SELECT
    *
FROM 
    G_1
WHERE 1=1
    AND ID NOT IN (SELECT PARENT_ID
                   FROM G_1 
                   WHERE PARENT_ID IS NOT NULL) -- NULL값이 포함되면 값이 안나오기 때문에 IS NOT NULL.

3. 최종 코드

WITH RECURSIVE G_1 AS(
    SELECT -- 최상위 노드 생성
        ID, PARENT_ID, 1 AS GENERATION
    FROM
        ECOLI_DATA
    WHERE 1=1
        AND PARENT_ID IS NULL
    
    UNION ALL
    
    SELECT -- 재귀 쿼리 생성
        ED.ID, ED.PARENT_ID, G_1.GENERATION+1 AS GENERATION
    FROM
        ECOLI_DATA ED
        INNER JOIN G_1
            ON ED.PARENT_ID=G_1.ID -- 현재 노드의 PARENT_ID가 이전 단계 노드의 ID와 일치하는 경우 
)
SELECT
    COUNT(*) AS COUNT,
    GENERATION
FROM 
    G_1
WHERE 1=1
    AND ID NOT IN (SELECT PARENT_ID
                   FROM G_1 
                   WHERE PARENT_ID IS NOT NULL)
GROUP BY
    GENERATION
ORDER BY
    GENERATION

후기

재귀적 cte에 대해 잘모르고 있었는데
이번 기회에 제대로 짚고 넘어갈 수 있어서 좋았다.
각 노드의 정렬을 하고자 할때는 RECURSIVE를 사용하자!