각 세대별 자식이 없는 개체의 수(COUNT)와 세대(GENERATION)를 출력하는 SQL문을 작성해주세요. 이때 결과는 세대에 대해 오름차순 정렬해주세요. 단, 모든 세대에는 자식이 없는 개체가 적어도 1개체는 존재합니다.
WITH RECURSIVE ECOLI AS (
SELECT ID, PARENT_ID, 1 AS GENERATION
FROM ECOLI_DATA
WHERE PARENT_ID IS NULL
UNION ALL
SELECT A.ID, A.PARENT_ID, B.GENERATION + 1
FROM ECOLI_DATA AS A
INNER JOIN ECOLI AS B ON A.PARENT_ID = B.ID
)
SELECT COUNT(A.GENERATION) AS COUNT, A.GENERATION
FROM ECOLI AS A
LEFT OUTER JOIN ECOLI AS B ON A.ID = B.PARENT_ID
WHERE B.ID IS NULL
GROUP BY GENERATION;
이 SQL 쿼리는 대장균 데이터에서 세대별로 자식이 없는 개체의 수를 계산한다. 먼저, WITH RECURSIVE ECOLI라는 공통 테이블 식(CTE)을 정의하여 대장균 개체의 세대를 계산한다. 이 과정은 부모-자식 관계를 기반으로 하며, PARENT_ID가 NULL인 최초 개체를 1세대로 설정하고, 자식 개체는 부모의 세대 번호에 1을 더해 재귀적으로 세대를 결정한다. 그 후, LEFT OUTER JOIN을 통해 각 개체의 ID를 부모로 참조하는 자식 개체를 탐색한다. 이때, 특정 개체가 다른 개체의 부모로 참조되지 않는 경우를 자식이 없는 개체로 간주한다. WHERE B.ID IS NULL 조건은 이러한 자식이 없는 개체를 식별하는 역할을 한다. 마지막으로, 세대(GENERATION)를 기준으로 그룹화하여 자식이 없는 개체의 수를 계산하고, 세대별로 정렬된 결과를 반환한다. 이 쿼리를 통해 세대별로 자식이 없는 대장균 개체의 분포를 명확히 확인할 수 있다.
https://school.programmers.co.kr/learn/courses/30/lessons/301651