[프로그래머스 SQL 코딩테스트 연습] SELECT - 멸종위기의 대장균 찾기

🤔 문제

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

다음은 실험실에서 배양한 대장균들의 정보를 담은 `ECOLI_DATA` 테이블입니다.

`ECOLI_DATA` 테이블의 구조는 다음과 같으며, `ID`, `PARENT_ID`, `SIZE_OF_COLONY`, `DIFFERENTIATION_DATE`, `GENOTYPE` 은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다.

 

 

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

각 세대별 자식이 없는 개체의 수(`COUNT`)와 세대(`GENERATION`)를 출력하는 SQL문을 작성해주세요. 이때 결과는 세대에 대해 오름차순 정렬해주세요. 단, 모든 세대에는 자식이 없는 개체가 적어도 1개체는 존재합니다.

 

 

예시

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

각 세대별 대장균의 ID는 다음과 같습니다.

 

1 세대 : ID 1, ID 2

2 세대 : ID 3, ID 4, ID 5

3 세대 : ID 6, ID 7

4 세대 : ID 8

 

이 때 각 세대별 자식이 없는 대장균의 ID는 다음과 같습니다.

 

1 세대 : ID 1

2 세대 : ID 3, ID 5

3 세대 : ID 7

4 세대 : ID 8

 

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

 

 

 

 

 

 

 

 

---

🖍 오답노트

틀린 이유

이 문제는 접근 방법과 더불어 `WITH RECURSIVE AS`문법을 알지 못해서 못 풀었다.

 

 

 

 

정답 풀이

WITH RECURSIVE GEN AS(
    SELECT ID, PARENT_ID, 1 AS GENERATION -- (1)
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    
    UNION ALL
    
    SELECT e.ID, e.PARENT_ID, g.GENERATION + 1 AS GENERATION -- (2)
    FROM ECOLI_DATA e
    JOIN GEN g ON e.PARENT_ID = g.ID
)

SELECT COUNT(ID) AS COUNT, GENERATION -- (4)
FROM GEN
WHERE ID NOT IN( --(3)
    SELECT DISTINCT PARENT_ID
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NOT NULL
)
GROUP BY 2
ORDER BY 2;

 

(1) 초기 쿼리

최상위 세대에서 출발하여 각 대장균의 세대를 가져온다.

부모 세대가 없는(`PARENT_ID`가 NULL인 것들) 1세대 대장균의 ID와 부모ID, 각 대장균의 세대(1로 초기화)를 조회한다.

 

(2) 재귀 부분

`GEN` 테이블에 새로운 행을 추가한다.

이전 세대의 대장균의 자식을 가져온다. → 현재 세대의 대장균(`e.PARENT_ID`)과 이들의 자식들(`g.ID`)을 연결한다.

각 대장균의 세대인 `GENERATION` 컬럼값(다음 세대)은 이전 세대의 GENERATION(`g.GENERATION`)에 +1 한 값으로 설정된다.

 

(3) 각 세대별 자식이 없는 대장균 찾기

부모가 아닌 대장균을 필터링해서 자식이 없는 대장균을 찾는다.

`ECOLI_DATA` 테이블에서 `PARENT_ID`가 NULL이 아닌 것들 중에서 유일한 부모ID(`DISTINCT PARENT_ID`)를 찾는다.

 

(4) 결과 조회 쿼리

앞서 생성한 `GEN` 테이블을 기반으로, 각 세대별 자식이 없는 대장균 수를 계산, 세대에 따라 오름차순 정렬해서 조회한다.

 

 

 

 

 

 

✅ 핵심 정리

1. WITH RECURSIVE (재귀 쿼리)

가상 테이블을 생성하면서 가상 테이블 자신의 값을 참조하여 값을 결정할 때 사용한다.

WITH RECURSIVE CTE AS(
	SELECT -- return initial row set
	FROM
	...
	UNION ALL
	
	SELECT -- return additional row sets
	FROM
	...
)

 

첫 번째 `SELECT` 문

CTE에 대한 초기 행을 생성하며 CTE 이름을 참조하지 않는다.

 

`UNION ALL`

테이블들을 합쳐주는 역할

 

두 번째 `SELECT` 문

추가 행을 생성하고 `FROM` 절에서 CTE 이름을 참조하여 재귀한다.

새로운 행을 생성하지 않으면 재귀가 종료된다.

 

 

 

 

 

 

 

---

🔗 References

[WITH RECURSIVE CTE 개념 참고]

https://yooloo.tistory.com/258

MySQL - recursive SQL

 

 

[정답 풀이 참고]

https://jaimemin.tistory.com/2464

[Programmers] 멸종위기의 대장균 찾기