[프로그래머스] (MySQL) ⭐⭐⭐ 멸종위기의 대장균 찾기 💯

1. 문제 설명

---

2. 접근방식

ECOLI_DATA는 부모-자식간의 계층 관계를 이루고 있기 때문에 각 세대별로 자식이 없는 개체의 수를 출력하려면 재귀 CTE를 사용해서 대장균의 세대generation)를 계산해 주어야 한다. WITH RECURSIVE 블록을 먼저 지정 해 주고, 블록 바깥에서 세대별로 자식이 없는 개체수를 조회하는 쿼리를 짜 주면 된다.

 

WITH RECURSIVE 블록은 또 두 부분으로 나뉘는데,

 

먼저, PARENT_ID IS NULL인 조건으로 데이터를 조회해서 1세대(AS GENERATION)를 규정 해 준다.

 

두 번째로 할 일은, 이 1세대 컬럼을 기준으로 +1을 해서 계층 구조를 조회 하는 것이다. 즉, 부모 개체로부터 자식의 세대 수를 계산 해 주는 것이다. Generation CTE와 ECOLI_DATA 테이블 간의 재귀적인 INNER JOIN을 해 준다. 이 JOIN이 어떻게 동작하냐면.. 음.. ECOLI_DATA의 PARENT_ID 컬럼과 Generation의 ID 컬럼을 계속 연결해서 부모-자식 관계를 추적 해 나가는 것이다. 재귀 조인으로 각 개체의 세대(GENERATION) 값이 계산되는 것이다.

 

이렇게 새로 조회된 데이터를 UNION ALL을 해 주면 테이블 밑으로 계속 데이터가 추가된다. 

 

계층 조회 문제 나오면 기억 할 것!
1. CTE 블럭 만들기
2. 0세대 혹은 1세대 규정
3. 부모 - 자식 관계의 컬럼으로 CTE와 해당 쿼리를 재귀적으로 INNER JOIN
4. UNION ALL

 

---

3. 정답코드

WITH RECURSIVE Generation AS (
    -- 최초 대장균을 1세대로 정의
    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,
           g.GENERATION + 1 AS GENERATION
    FROM ECOLI_DATA e
    INNER JOIN Generation g ON e.PARENT_ID = g.ID    
)

-- 각 세대별로 자식이 없는 개체 수 계산
SELECT COUNT(*) AS COUNT,
       GENERATION
FROM Generation gn
WHERE NOT EXISTS (
    SELECT 1
    FROM ECOLI_DATA e
    WHERE e.PARENT_ID = gn.ID
)
GROUP BY GENERATION
ORDER BY GENERATION

 

그리고 자식이 없는 개체를 찾는 쿼리 쓰는게 조금 어려웠다.

 

WHERE e.PARENT_ID = gn.ID 는`gn.ID`를 `부모`로 참조하는 `자식 개체`가 존재하는지 확인하는 쿼리이다.즉, e.PARENT_ID = gn.ID는 ECOLI_DATA 테이블에서 gn.ID를 부모로 가진 자식 개체를 찾는 조건인 것이다.

 

NOT EXISTS랑 같이 써주면 서브쿼리의 결과가 없는 경우 (e.PARENT_ID = gn.ID에 맞는 자식이 없으면), NOT EXISTS는 TRUE가 되고, 그 gn.ID가 결과에 포함된다.