[프로그래머스][SQL][Lv05] 멸종위기의 대장균 찾기

[프로그래머스][SQL][Lv05] 멸종위기의 대장균 찾기

 

풀이

ecoli_data 테이블의 데이터를 재귀적으로 탐색하여, 각 id의 세대를 계산한 후, 마지막 세대에 속한 항목들을 세어 세대별로 출력하는 방식으로 진행했다.

 

1. WITE RECURSIVE 문

재귀적 CTE(Common Table Expression)를 사용하여 데이터의 부모-자식 관계를 따라 세대를 계산하는 부분이다.

SELECT id, 
       parent_id, 
       1 AS gen
  FROM ecoli_data
WHERE parent_id IS NULL

parent_id가 NULL인 데이터를 선택한다, 즉, parent_id가 없는 최상위 항목들이며, 이 항목들은 gen값이 1인, 즉 1세대다.

SELECT e.id,
       e.parent_id, 
       g.gen + 1 AS gen
  FROM ecoli_data e
    INNER JOIN gen g
    ON e.parent_id = g.id

이 부분에서 ecoli_data 테이블의 각 항목(e)이 parent_id를 통해 부모 항목(g.id)과 연결된다.

부모 항목의 세대에 1을 더하여 자식 항목의 세대를 계산한다. 이 과정에서 세대 별로 데이터를 탐색하게 된다.

 

즉, 부모의 id를 기준으로 자식 항목들의 id와 gen값을 계속 찾아 나가는 것이다.

 

2. UNION

UNION은 두 쿼리를 합쳐서 실행하는 역할을 한다.

첫 번째 쿼리에서는 parent_id가 NULL인 최상위 항목을 가져오며

두 번째 쿼리에서는 재귀적으로 최상위 항목의 자식들을 찾아가면서 각 항목이 어느 세대에 속하는지 계산한다.

 

즉, 처음에 최상위 항목들을 시작점으로 삼고 그 후에 재귀적으로 자식 항목들을 찾아서 트리 구조를 완성해 나가는 과정이다. 재귀적으로 부모-자식 관계를 따라 내려가며 각 항목이 어느 세대에 속하는지를 계산한다.

 

3. SELECT 문

id가 다른 항목들의 parent_id로 존재하지 않는 항목을 선택하여 세대별로 그룹화 한 후 count를 출력한다.

SELECT count(*) as COUNT,
       gen as GENERATION
  FROM gen
WHERE id not in (SELECT parent_id
                   FROM ecoli_data
                 WHERE parent_id is not null)
GROUP BY gen
ORDER BY gen

 

정답

WITH RECURSIVE gen AS(
    SELECT id,
           parent_id,
           1 as gen
      FROM ecoli_data
    WHERE parent_id is null
    
    UNION ALL
    
    SELECT e.id,
           e.parent_id,
           g.gen + 1 as gen
      FROM ecoli_data e
      INNER JOIN gen g
      ON e.parent_id = g.id
)
SELECT count(*) as COUNT,
       gen as GENERATION
  FROM gen
WHERE id not in (SELECT parent_id
                   FROM ecoli_data
                 WHERE parent_id is not null)
GROUP BY gen
ORDER BY gen