[MY SQL] 재귀쿼리를 활용하여 특정 세대 찾기

출처: [프로그래머스 스쿨 - 코딩테스트 연습 - MY SQL 문제] :  특정 세대의 대장균 찾기

링크: https://school.programmers.co.kr/learn/courses/30/lessons/301650

문제(일부):  3세대 대장균ID를 출력하는 SQL작성

---

1. 중첩된 서브쿼리 사용하기

0) WHERE절에서 IN연산자로 ID와 PARENT_ID의 세대관계를 확인
1) 가장 내부의 서브쿼리는 PARENT_ID가 NULL인, 즉 1세대에 해당하는 모든 ID를 찾음

2) 두번째 서브쿼리는 1세대 ID에서 분화 된 2세대 ID를 찾음

3) 세번째 서브쿼리는 2세대 ID에서 분화 된 3세대 ID를 찾음

-- 3세대 ID
SELECT ID
FROM ECOLI_DATA
WHERE PARENT_ID IN (
    -- 2세대 ID
    SELECT ID
    FROM ECOLI_DATA
    WHERE PARENT_ID IN (
        -- 1세대 ID
        SELECT ID
        FROM ECOLI_DATA
        WHERE PARENT_ID IS NULL) )
ORDER BY ID;

 

2. self join 사용하기

0) JOIN으로 ID와 PARENT_ID가 일치하는 것을 조회
1) T는 데이터베이스에서 가장 첫 번째 세대, S는 두 번째 세대, 그리고 F는 세 번째 세대

SELECT F.ID
FROM ECOLIDATA AS F
    JOIN ECOLIDATA AS S ON F.PARENTID = S.ID
    JOIN ECOLIDATA AS T ON S.PARENTID = T.ID
WHERE T.PARENTID IS NULL
ORDER BY F.ID;

 

3. 재귀쿼리 이용하기

재귀쿼리의 간단한 설명은 아래 글에서 확인 가능합니다
https://o-s-o-jjj.tistory.com/21

 

풀이 작성자: https://happy-ryan.tistory.com/180

=> 재귀쿼리가 어떤 식으로 실행되는지 디버깅을 통한 해설이 있어 이해가 쉬웠습니다!

 

1) 2세대를 재귀쿼리의 초기값으로 사용
2) 재귀쿼리로 정해진 세대까지 재귀쿼리 실행(JOIN)
3) UNION ALL로 중복이 발생 4세대의 경우 1,2,3세대가 있음
    => ID로 GROUP BY한 후 HAVING에 MAX(세대) = 3인 것으로 필터링

WITH RECURSIVE FamilyTree AS (
    -- Non-recursive part
    SELECT  ID, 
            PARENT_ID,
            2 AS Generation
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NOT NULL

    UNION ALL

    -- Recursive part
    SELECT Child.ID, Child.PARENT_ID, Parent.Generation + 1 
    FROM ECOLI_DATA AS Child
    INNER JOIN FamilyTree AS Parent ON Child.PARENT_ID = Parent.ID
)

SELECT ID
FROM FamilyTree
GROUP BY ID
HAVING MAX(Generation) = 3
ORDER BY ID;