멸종위기의 대장균 찾기

Link

Tables

erDiagram
	ECOLI_DATA {
		ID INTEGER PK
		PARENT_ID INTEGER
		SIZE_OF_COLONY INTEGER
		DIFFERENTIATION_DATE DATE
		GENOTYPE INTEGER
	}
Mermaid
복사

Question

각 세대별 자식이 없는 개체의 수(COUNT)와 세대(GENERATION)를 출력하는 SQL문을 작성해주세요. 이때 결과는 세대에 대해 오름차순 정렬해주세요. 단, 모든 세대에는 자식이 없는 개체가 적어도 1개체는 존재합니다.

Dataflow

•

RECURSIVE 사용 -> 부모로 한번도 사용되지 않은 leef

•

FROM 다음에 등장하는 테이블을 중심으로 생각한다.

•

join혹은 exists의 조건은 간단하면 간단 할 수록 좋다

Answer

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

    UNION ALL

    SELECT
        g.ID,
        g.PARENT_ID,
        f.GENERATION + 1 AS GENERATION
    FROM ECOLI_DATA g
    JOIN family f
        ON g.PARENT_ID = f.ID
),
gen AS (
    SELECT g1.*
    FROM family g1
    LEFT JOIN family g2
        ON g2.PARENT_ID = g1.ID
    WHERE g2.ID IS NULL
)

SELECT
    COUNT(ID) AS COUNT,
    GENERATION
FROM gen
GROUP BY GENERATION
ORDER BY GENERATION
SQL
복사