특정 세대의 대장균 찾기

💡 문제설명

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.

다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다. ECOLI_DATA 테이블의 구조는 다음과 같으며, ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE 은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다.

최초의 대장균 개체의 PARENT_ID 는 NULL 값입니다.

💡 문제

3세대의 대장균의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 이때 결과는 대장균의 ID 에 대해 오름차순 정렬해주세요.

 

예시

예를 들어 ECOLI_DATA 테이블이 다음과 같다면

PARENT ID 가 NULL 인 ID 1, ID 2가 1 세대이며 ID 1에서 분화된 ID 3, ID 2에서 분화된 ID 4, ID 5 가 2 세대입니다. ID 4 에서 분화된 ID 6, ID 3에서 분화된 ID 7 이 3 세대이며 ID 6에서 분화된 ID 8은 4 세대입니다.

따라서 결과를 ID 에 대해 오름차순 정렬하면 다음과 같아야 합니다.

✨ 풀이

WITH RECURSIVE cte_gen 
AS
(
    -- Non-Recursive 문장( 첫번째 루프에서만 실행됨 )
    -- 1세대 대장균 조회
    SELECT ID, PARENT_ID, 1 AS gen
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL 
    UNION ALL
    
    -- Recursive 문장(읽어 올 때마다 행의 위치가 기억되어 다음번 읽어 올 때 다음 행으로 이동함)
    -- n세대 대장균 조회 (where 조건문에 < 4를 추가하여 3세대 이상 조회하는 불필요한 계산을 막음)
    SELECT ED.ID, ED.PARENT_ID, CG.gen + 1 AS gen
    FROM ECOLI_DATA ED INNER JOIN
        cte_gen CG 
    WHERE ED.PARENT_ID = CG.ID
    AND CG.gen < 4
)

SELECT ID FROM cte_gen
WHERE gen = 3;

 

출처

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit

참고 재귀쿼리 사용방법

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-RECURSIVE-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC