MySQL 프로그래머스 | 멸종 위기의 대장균 찾기 (재귀쿼리로 자식 없는 개체수 구하기)

 

문제

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

프로그래머스

• 각 세대별, 자식이 없는 개체의 수(COUNT)와 세대(GENERATION)를 출력하는 SQL문을 작성
• 결과는 세대 기준 오름차순 정렬
• 모든 세대에는 자식이 없는 개체가 적어도 1개체는 존재함

 

테이블

ECOLI_DATA

• 자식이 없는 개체 : 빨간 박스 → 1, 3, 5, 7, 8
  • 자식이 없으면 PARENT_ID에 자신의 ID가 없음

 

풀이과정

# 세대구분 재귀쿼리 만들기

• 재귀쿼리 첫 번째 테이블은 PARENT_ID가 NULL인 1세대의 경우, GENERATION을 1로 명명
• 재귀쿼리 두 번째 테이블부터는 자신의 PARENT_ID가 이전 재귀쿼리 결과 테이블의 ID와 같을 때
  GENERATION이 +1씩 되도록 함 

WITH RECURSIVE GE AS (SELECT ID, 
                             1 AS GENERATION
                      FROM   ECOLI_DATA
                      WHERE  PARENT_ID IS NULL
                      
                      UNION ALL
                      
                      SELECT A.ID, 
                             B.GENERATION + 1 AS GENERATION
                      FROM   ECOLI_DATA A, GE B
                      WHERE  A.PARENT_ID = B.ID
                     )

 

 

# 원래 테이블과 세대구분 테이블 조인

• 원래 테이블의 ID가 세대 구분 테이블의 ID와 같을 때 조인되게 함 

WITH RECURSIVE GE AS (SELECT ID, 
                             1 AS GENERATION
                      FROM   ECOLI_DATA
                      WHERE  PARENT_ID IS NULL
                      
                      UNION ALL
                      
                      SELECT A.ID, 
                             B.GENERATION + 1 AS GENERATION
                      FROM   ECOLI_DATA A, GE B
                      WHERE  A.PARENT_ID = B.ID
                     )
                     
SELECT *
FROM ECOLI_DATA A, GE B
WHERE A.ID = B.ID

 

 

# 자식 개체가 없는 ID 필터링하기

• 자식 개체가 없으면, 자신의 ID가 PARENT_ID에 속하지 않아야 함
• (주의) IN 또는 NOT IN 함수를 사용할 때는 안에 NULL 데이터가 있으면 안됨! (NULL이 있으면 결과가 안 나옴)
  전체 PARENT_ID를 조회하면 NULL (1세대) 데이터가 존재하기 때문에 서브쿼리 내에 WHERE절을 사용해 
  PARENT ID IS NOT NULL 조건을 추가함

 WITH RECURSIVE GE AS (SELECT ID, 
                             1 AS GENERATION
                      FROM   ECOLI_DATA
                      WHERE  PARENT_ID IS NULL
                      
                      UNION ALL
                      
                      SELECT A.ID, 
                             B.GENERATION + 1 AS GENERATION
                      FROM   ECOLI_DATA A, GE B
                      WHERE  A.PARENT_ID = B.ID
                     )
                     
SELECT *
FROM ECOLI_DATA A, GE B
WHERE A.ID = B.ID
AND A.ID NOT IN(SELECT DISTINCT PARENT_ID FROM ECOLI_DATA WHERE PARENT_ID IS NOT NULL)

 

 

# 정답

• 각 세대 별로 자식 개체가 없는 ID들의 COUNT값을 구해야 하기 때문에 
  GENERATION으로 GROUP BY 해준 뒤 ID를 카운트 해줌
• 세대 기준 오름차순 정렬까지 해주면 끝!

WITH RECURSIVE GE AS (SELECT ID, 
                             1 AS GENERATION
                      FROM   ECOLI_DATA
                      WHERE  PARENT_ID IS NULL
                      
                      UNION ALL
                      
                      SELECT A.ID, 
                             B.GENERATION + 1 AS GENERATION
                      FROM   ECOLI_DATA A, GE B
                      WHERE  A.PARENT_ID = B.ID
                     )
                     
SELECT COUNT(A.ID) COUNT, GENERATION
FROM ECOLI_DATA A, GE B
WHERE A.ID = B.ID
AND A.ID NOT IN(SELECT DISTINCT PARENT_ID FROM ECOLI_DATA WHERE PARENT_ID IS NOT NULL)
GROUP BY GENERATION
ORDER BY GENERATION