[프로그래머스] 멸종위기의 대장균 찾기도움말

 

Question

 

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

 

Pseudocode

 

의사코드

1. CTE 정의 (재귀 쿼리):
   • id와 parent_id가 존재하는 대장균 데이터를 기반으로 트리 구조를 재귀적으로 탐색합니다.
   • parent_id가 NULL인 항목을 첫 번째 세대(gen = 1)로 시작하고, 재귀적으로 부모-자식 관계를 따라가며 gen 값을 1씩 증가시킵니다.
2. 메인 쿼리:
   • cte에서 각 개체가 마지막 세대인지 확인하기 위해 자기 자신(a)을 기준으로 자식(b)이 없는 경우를 확인합니다.
   • 즉, 자식이 더 이상 없는 개체들만을 대상으로 각 세대별 개체 수를 계산합니다.
   • 이를 세대(generation)로 그룹화하여 각 세대별 개체 수를 집계하고, 세대별로 오름차순으로 정렬하여 결과를 출력합니다.

주요 개념:

• 재귀 CTE: cte는 재귀적으로 parent_id를 기준으로 트리를 탐색합니다.
• 자식이 없는 개체 필터링: LEFT JOIN을 통해 b가 NULL인 경우, 즉 더 이상 자식이 없는 개체를 필터링하여 세대별 개체 수를 계산합니다.

 

 

Code

 

WITH RECURSIVE cte AS (SELECT id, parent_id, 1 AS gen
                       FROM ecoli_data
                       WHERE parent_id IS NULL

                       UNION ALL

                       SELECT e.id, e.parent_id, cte.gen + 1 AS gen
                       FROM ecoli_data e
                                JOIN cte ON cte.id = e.parent_id)
SELECT COUNT(a.id) AS 'COUNT', a.gen AS generation
FROM cte a
         LEFT JOIN cte b ON a.id = b.parent_id
WHERE b.id IS NULL
GROUP BY a.gen
ORDER BY a.gen