[SQL] 재귀쿼리 (멸종위기의 대장균 찾기)

노가다하려다가 다른 사람 풀이를 참고했다..ㅠㅠ
참고 >> https://eunchaan.tistory.com/356

문제

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화되어 나온 개체를 자식 개체라고 한다. 이 문제는 각 세대별로 자식이 없는 대장균 개체의 수를 구하고, 세대별로 오름차순으로 출력하는 것이다.

with recursive tmp as (
    select id, parent_id, 1 as generation
    from ecoli_data
    where parent_id is null
    union all
    select s.id, s.parent_id, tmp.generation + 1
    from tmp join ecoli_data s
    on tmp.id = s.parent_id
)

select count(*) count, generation
from tmp
where id not in (
    select distinct parent_id
    from tmp
    where parent_id is not null)
group by generation
order by 2

---

union all & recursive

---

union all

1. 문자열 결합

select 'apple' AS fruit
UNION ALL
select 'banana'
UNION ALL
select 'apple';

2. 테이블 결합

• union : 중복제거
• union all : 중복포함

select id, name from table1
UNION ALL
select id, name from table2;

---

recursive
: 자기 자신을 참조하면서 반복적으로 데이터 생성

1. 기본구조

• 기본단계
• 재귀단계
• 종료조건

WITH RECURSIVE cte_name AS (
    -- 기본 단계 (초기값)
    SELECT ...

    UNION ALL

    -- 재귀 단계 (자기 자신을 참조)
    SELECT ...
    FROM cte_name  -- 자기 자신을 참조
    WHERE ...  -- 종료 조건
)
SELECT * FROM cte_name;