대장균 세대별 자식 없는 개체 수 구하기 (CASE, JOIN 기반 풀이)

이 글은 프로그래머스 SQL 문제 중 "세대별 자식이 없는 대장균 개체 수"를 구하는 문제를 재귀 없이 JOIN 방식으로 푼 예시를 설명한 것이다. 정답 처리는 안 될 수 있으나, SQL 개념 학습에는 굉장히 유익하다.

---

🧩 문제 요약

ECOLI_DATA 테이블에는 대장균 개체들의 정보가 있다.

컬럼명	설명
ID	개체 고유 ID
PARENT_ID	부모 개체의 ID (최초 개체는 NULL)
SIZE_OF_COLONY	개체 크기
DIFFERENTIATION_DATE	분화 날짜
GENOTYPE	형질 정보

목표:

• 각 개체의 세대(GENERATION) 를 판별하고
• 자식이 없는 개체만 필터링해서
• 세대별로 개체 수를 세기

---

🧠 사용한 개념 정리

✅ SELF JOIN

• 자기 자신을 여러 번 조인하여 부모-자식 관계를 추적함
• 예: a가 현재 개체면, b는 a.parent_id = b.id로 부모

✅ 세대 구분 (GENERATION)

• CASE 문으로 세대 구분:

CASE
  WHEN a.parent_id IS NULL THEN 1
  WHEN b.parent_id IS NULL THEN 2
  WHEN c.parent_id IS NULL THEN 3
  ELSE 4
END AS GENERATION

• a는 현재 개체, b, c는 각각 상위 조인된 부모 개체들

✅ 자식 유무 판단

• 자식이 없는 개체를 찾기 위해 LEFT JOIN + WHERE child.id IS NULL 사용

LEFT JOIN ecoli_data child ON child.parent_id = a.id
WHERE child.id IS NULL

• 즉, a 입장에서 자식이 존재하지 않으면 child가 NULL로 남는다

✅ 집계 + 정렬

• GROUP BY GENERATION으로 세대별로 묶고 COUNT(*)로 개수 셈
• ORDER BY GENERATION으로 오름차순 정렬

---

🧪 최종 쿼리

SELECT
  COUNT(*) AS COUNT,
  GENERATION
FROM (
  SELECT
    a.id,
    CASE
      WHEN a.parent_id IS NULL THEN 1
      WHEN b.parent_id IS NULL THEN 2
      WHEN c.parent_id IS NULL THEN 3
      ELSE 4
    END AS GENERATION
  FROM ecoli_data a
  LEFT JOIN ecoli_data b ON b.id = a.parent_id
  LEFT JOIN ecoli_data c ON c.id = b.parent_id
  LEFT JOIN ecoli_data child ON child.parent_id = a.id
  WHERE child.id IS NULL
) AS SUB
GROUP BY GENERATION
ORDER BY GENERATION ASC;

---

⚠️ 주의사항

• 이 쿼리는 재귀 CTE (WITH RECURSIVE) 없이도 정확히 결과를 도출하지만,
• 프로그래머스 문제 채점기에서는 정답 처리되지 않을 수 있음
• 채점기는 재귀적으로 세대를 추적했는지를 내부적으로 검사할 수 있음

---

✅ 마무리 정리

개념	설명
SELF JOIN	부모-자식 관계 추적에 사용
CASE	세대 분류용 조건문
LEFT JOIN + IS NULL	자식 없는 개체 판별
GROUP BY + COUNT	세대별 개수 집계

실전 SQL 문제에서 재귀 없이도 트리 구조를 추적할 수 있다는 중요한 예시이며,
JOIN과 조건 조합만으로도 상당히 많은 계층적 데이터를 처리할 수 있다는 걸 보여주는 사례이다.