[PS] 멸종위기의 대장균 찾기 ⭐️

멸종위기의 대장균 찾기

재귀 필요
1.세대(GENERATION)를 계산하기 위해서는 최초의 대장균부터 시작하여, 해당 개체의 자식들이 몇 번째 세대인지 추적해야 합니다.
2.각 개체가 자식이 있는지 여부를 확인하기 위해 자식이 없는 개체를 필터링합니다.
3.각 세대별로 자식이 없는 개체의 수를 구합니다.

WITH RECURSIVE GenerationTree AS (
    -- 최초 세대 (PARENT_ID가 NULL인 개체는 0세대)
    SELECT ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE, 1 AS GENERATION
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    
    UNION ALL
    
    -- 부모 세대에서 자식 세대로 이동 (재귀적으로 세대 정보 확장)
    SELECT E.ID, E.PARENT_ID, E.SIZE_OF_COLONY, E.DIFFERENTIATION_DATE, E.GENOTYPE, GT.GENERATION + 1
    FROM ECOLI_DATA E
    JOIN GenerationTree GT ON E.PARENT_ID = GT.ID
)

-- 각 세대별 자식이 없는 개체 카운트
SELECT GENERATION, COUNT(*) AS COUNT
FROM GenerationTree GT
WHERE GT.ID NOT IN (SELECT PARENT_ID FROM ECOLI_DATA WHERE PARENT_ID IS NOT NULL)
GROUP BY GENERATION
ORDER BY GENERATION ASC;

최초 세대와 그 이후의 세대를 union all 한다. => ECOLI_DATA 테이블에서 각 데이터마다의 세대를 구한다.

1. WITH RECURSIVE GenerationTree: 재귀 CTE(Common Table Expression)를 사용하여 각 개체의 세대를 계산합니다.

• PARENT_ID가 NULL인 경우 최초 세대로 간주하고 GENERATION = 1을 부여합니다.
• 이후 각 자식 개체는 부모 개체의 세대에 1을 더한 세대로 설정됩니다.

2. WHERE GT.ID NOT IN (SELECT PARENT_ID FROM ECOLI_DATA WHERE PARENT_ID IS NOT NULL) : 자식이 없는 개체를 찾기 위해 해당 개체가 PARENT_ID에 나타나지 않는 경우만 필터링합니다.

3. GROUP BY GENERATION: 세대별로 자식이 없는 개체의 수를 집계합니다.

4. ORDER BY GENERATION ASC: 세대를 오름차순으로 정렬하여 결과를 출력합니다.