[프로그래머스] 멸종위기의 대장균 찾기

문제

제출

WITH RECURSIVE Generations AS (
    -- 1세대 찾기 (parent_id가 NULL인 경우)
    SELECT id, 1 AS generation
    FROM ECOLI_DATA
    WHERE parent_id IS NULL
    UNION ALL
    -- 부모의 generation + 1을 부여하여 재귀적으로 세대 계산
    SELECT e.id, g.generation + 1
    FROM ECOLI_DATA e
    JOIN Generations g ON e.parent_id = g.id
)
SELECT count(*) COUNT,b.generation
from ECOLI_DATA a
join Generations b on a.id = b.id
# id 중에서 null을 제외한 parent_id에 없는 id (1,3,5,7,8)
WHERE a.id NOT IN (SELECT parent_id FROM ECOLI_DATA WHERE parent_id IS NOT NULL)
group by b.generation
order by 2 asc

📌 새로 배운 내용

✅ 재귀 CTE

WITH RECURSIVE
반복해서(재귀적으로) 계산하는 SQL문

WITH RECURSIVE CTE명 AS (
    -- 1️⃣ **기본 쿼리 (Anchor Query)**: 시작점을 찾음
    기본_쿼리 (WHERE로 특정 데이터 찾기)
    
    UNION ALL

    -- 2️⃣ **재귀 쿼리 (Recursive Query)**: 이전 결과를 기반으로 다음 데이터를 찾음
    재귀_쿼리 (JOIN으로 부모 → 자식 관계 탐색)
)
SELECT * FROM CTE명;

1️⃣ 기본 쿼리 : 한번 실행됨
2️⃣ 재귀 쿼리 : 이전 결과를 사용해서 반복 실행됨

• 문제에서의 재귀 CTE문
  

WITH RECURSIVE Generations AS (
	# 1️⃣ 기본 쿼리
    -- 1세대 찾기 (parent_id가 NULL인 경우)
    SELECT id, 1 AS generation
    FROM ECOLI_DATA
    WHERE parent_id IS NULL
    UNION ALL
    # 2️⃣ 재귀 쿼리
    -- 부모의 generation + 1을 부여하여 재귀적으로 세대 계산
    SELECT e.id, g.generation + 1
    FROM ECOLI_DATA e
    JOIN Generations g ON e.parent_id = g.id
)

1️⃣ 기본 쿼리
parent_id가 null 값인 개체를 generation 1 (1세대)로 정의한 임시 테이블 Generation을 만든다. (id, generation 컬럼 존재)
→ id = 1,2가 1세대

2️⃣ 재귀 쿼리
generation에 +1 씩 하면서 반복한다.

• 2세대
  Generations 테이블에서 id = 1을 부모로 가지는 데이터(parent_id = 1,2)를 찾고, generation = 2 (2세대)로 정의한다.
  → id = 3,4,5가 2세대

• 3세대
  Generations 테이블에서 id = 3,4,5을 부모로 가지는 데이터(parent_id = 3,4,5)를 찾고, generation = 3 (3세대)로 정의한다.
  → id = 6,7이 3세대

• 4세대
  Generations 테이블에서 id = 6,7을 부모로 가지는 데이터(parent_id = 6,7)를 찾고, generation = 4 (4세대)로 정의한다.
  → id = 8이 4세대

❗️ 주의할 점

3세대 찾을 때, Generations.id에는 1,2,3,4,5가 존재하지만,
우리가 새로운 데이터를 찾을 때는 이전 반복에서 찾은 “2세대” 개체들(id=3,4,5)만 기준이 된다.
따라서, parent_id = 3 또는 4 또는 5인 데이터를 찾는 것!