[프로그래머스] 멸종위기의 대장균 찾기 (MySQL) - WITH RECURSIVE cte

 

https://school.programmers.co.kr/learn/courses/30/lessons/301651

프로그래머스

 

2024.09.09 - [MySQL] - WITH RECURSIVE CTE 사용하기 (MySQL)

WITH RECURSIVE cte를 사용해야 풀 수 있는 문제였다.

 

 

제출한 쿼리

WITH RECURSIVE CTE AS (
SELECT ID, PARENT_ID, 1 AS GENERATION
FROM ECOLI_DATA
WHERE PARENT_ID IS NULL
UNION ALL        
SELECT E.ID, E.PARENT_ID, C.GENERATION+1
FROM CTE C INNER JOIN ECOLI_DATA E ON C.ID = E.PARENT_ID)

    ,NON_PARENTS AS (
    SELECT ID, GENERATION FROM CTE
    WHERE ID NOT IN (SELECT E.ID
                        FROM ECOLI_DATA E 
                        INNER JOIN ECOLI_DATA D ON E.ID = D.PARENT_ID))

SELECT COUNT(*) AS "COUNT", GENERATION FROM NON_PARENTS
GROUP BY GENERATION
ORDER BY GENERATION;

 

 

가장 먼저 ECOLI_DATA에서 PARENT_ID가 null인 데이터를 골라 GENERATION 컬럼을 만든 뒤 1을 반환하고

→ RECURSIVE를 이용해 전체 데이터에 GENERATION을 만들어준다. 

여기서 RECURSIVE CTE 안에서 JOIN해야 하는 게 어려웠다. 

RECURSIVE CTE의 기본은 UNION ALL 다음 SELECT에서는 FROM CTE를 하는 것이다. = CTE를 정의하는 쿼리 내에서 다시 CTE를 사용한다. (재귀)

 

NON_PARENTS는 GENERATION 컬럼이 정의된 CTE를 다시 사용해, 자식이 없는 데이터의 ID와 GENERATION을 골라낸 테이블이다.

 

마지막으로 메인 쿼리에서 NON_PARENTS를 세대별로 그룹핑하고 count해 반환했다.