[SQL/오답] Self Join 대장균 3대 가계도 추적: 세대 고정 논리 (프로그래머스 Lv4)

1. Problem

대장균 배양 데이터에서 부모-자식 관계를 추적하여 **제3세대(3rd Generation)**에 해당하는 개체의 ID를 추출해야 한다. 데이터 내에는 1세대부터 4세대 이상까지 섞여 있으므로, 기준점(최초 개체)으로부터 정확히 3단계 아래에 위치한 개체들만 골라내는 것이 기술적 과제이다.

2. Solution: Self Join을 이용한 계층 연결

동일한 테이블을 세 번 연결(GEN1 - GEN2 - GEN3)하여 3대 가계도를 한 줄로 정렬한 뒤, 가장 윗세대가 최초 개체인 경우만 필터링한다.

3. Takeaway: 왜 '최초 부모'를 고정해야 하는가?

• 세대 고정의 원리: 단순히 조인만 두 번 수행하면 '할아버지-부모-자식' 관계인 모든 3대 집합이 추출된다. 만약 4세대가 있다면 2세대-3세대-4세대 조합도 조건을 만족하게 된다. WHERE GEN1.PARENT_ID IS NULL을 추가함으로써 가계도의 절대적인 시작점을 1세대로 고정할 수 있다.
• Self Join의 적재적소 활용: 계층의 깊이가 고정되어 있고 깊지 않은 경우(3~4단계), 재귀 쿼리(WITH RECURSIVE)보다 Self Join을 사용하는 것이 코드의 가독성과 작성 속도 측면에서 더 효율적이다.
• 계층 구조 데이터의 시각화: ID와 PARENT_ID 관계를 통해 테이블을 수평적으로 확장(Join)하는 사고방식은 조직도, 카테고리 트리, 로그 추적 등 실무 데이터 분석에서 매우 빈번하게 사용되는 필수 역량이다.

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

 

---

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.
다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다. ECOLI_DATA 테이블의 구조는 다음과 같으며, ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE 은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다.

 

Column Name	Type	Nullable
ID	INTEGER	FALSE
PARENT_ID	INTEGER	TRUE
SIZE_OF_COLONY	INTEGER	FALSE
DIFFERENTIATION_DATE	DATE	FALSE
GENOTYPE	INTEGER	FALSE

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

---

문제

3세대의 대장균의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 이때 결과는 대장균의 ID 에 대해 오름차순 정렬해주세요.

---

예시

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

ID	PARENT_ID	SIZE_OF_COLONY	DIFFERENTIATION_DATE	GENOTYPE
1	NULL	10	2019/01/01	5
2	NULL	2	2019/01/01	3
3	1	100	2020/01/01	4
4	2	16	2020/01/01	4
5	2	17	2020/01/01	6
6	4	101	2021/01/01	22
7	3	101	2022/01/01	23
8	6	1	2022/01/01	27

PARENT ID 가 NULL 인 ID 1, ID 2가 1 세대이며 ID 1에서 분화된 ID 3, ID 2에서 분화된 ID 4, ID 5 가 2 세대입니다. ID 4 에서 분화된 ID 6, ID 3에서 분화된 ID 7 이 3 세대이며 ID 6에서 분화된 ID 8은 4 세대입니다.

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

ID

6

7

---

SELECT GEN3.ID 
FROM ECOLI_DATA AS GEN3
JOIN ECOLI_DATA AS GEN2 ON GEN3.PARENT_ID = GEN2.ID
JOIN ECOLI_DATA AS GEN1 ON GEN2.PARENT_ID = GEN1.ID
WHERE GEN1.PARENT_ID IS NULL
ORDER BY GEN3.ID ASC;

 

Self-Join(셀프 조인)**을 통해 대장균의 3대 가계도를 한 줄로 이어 붙인 아주 명쾌한 풀이

 

1. 가계도의 '뿌리'를 찾는 기준

우리가 조인을 두 번 하면 총 3개의 테이블(GEN1, GEN2, GEN3)이 연결됩니다. 이때 컴퓨터는 단순히 '손자-부모-할아버지' 관계인 3개 층만 찾을 뿐, 그 '할아버지'가 가문의 시작인지 아니면 그 위에도 증조할아버지가 있는지 알지 못합니다.

• GEN1.PARENT_ID IS NULL이 있을 때: GEN1이 부모가 없는 **최초의 개체(1세대)**임을 확정 짓습니다. 자연스럽게 GEN2는 2세대, GEN3는 3세대가 됩니다.
• 이 조건이 없을 때: 만약 4세대 개체가 있다면, 4세대-3세대-2세대로 묶인 데이터도 조인 조건(PARENT_ID = ID)을 만족하므로 결과에 포함되어 버립니다. 즉, 몇 세대든 관계없이 '3대 연속된 관계'면 다 나오게 되는 것이죠.

결국 WHERE GEN1.PARENT_ID IS NULL은 **"가장 윗대의 부모가 없어야만 비로소 아래 세대를 1, 2, 3세대로 인정하겠다"**는 선언과 같습니다.

 

 

3. 쿼리 최적화 팁

사용자님은 3세대를 찾기 위해 조인을 사용하셨는데, 만약 세대가 더 깊어진다면(예: 10세대) 조인을 9번 하는 것은 매우 힘들어집니다. 그럴 때 WITH RECURSIVE를 쓰게 되는 것이죠!

하지만 3세대 정도의 짧은 관계에서는 지금처럼 조인을 쓰는 것이 훨씬 직관적이고 속도도 빠릅니다. 아주 적절한 도구를 선택하신 거예요.