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

프로그래머스 멸종위기의 대장균 찾기 SQL 코딩 테스트 문제 풀이를 해보자.

제출 사이트를 보니 DBMS는 mysql만 지원된다.

문제 : 멸종위기의 대장균 찾기

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

프로그래머스

 

문제 설명

 

 

문제 해설

• 목표(이 문제를 풀면 아래 기능을 활용 할 수 있습니다.)
  • Mysql recursive 쿼리 작성방법(계층형 쿼리)
• 풀이방법
  • 무슨 말인지 보자마자 대장균, 분화, 배양, 세대 용어들이 나오면서 해석하는데도 어질어질합니다.
  • 훑어서 읽고 쭉쭉 내려서 예시를 보니 각 id별로 부모가되는 parent_id를 가지고 있고, 하나의 레벨(재귀깊이)를 세대라는 용어로 표현하고 있습니다.
  • 결국 계층형 데이터 중 parent_id로 쓰이지 못한, 부모가 되지 못한 id들을 세대별로 카운팅하라는 문제입니다.
  • mysql에서 문제 예시를 가지고 계층형쿼리 템플릿을 한번 알아보겠습니다. 

WITH RECURSIVE CTE AS (
    -- 최상위 부모에 대한 정의
    SELECT 
        id, 
        parent_id, 
        1 AS lvl
    FROM ecoli_data
    WHERE parent_id IS NULL    
    UNION ALL    
    -- 자식에 대한 정의 (부모와 관계)
    SELECT 
        c.id, 
        c.parent_id, 
        p.lvl + 1
    FROM CTE p JOIN ecoli_data c ON c.parent_id = p.id
)
select * from cte;

결과값

 

위 쿼리를 수행하면 최상위 부모에 대한 정의부터 시작해서 자식까지 전부 찾아 결과를 리턴합니다.

이 데이터에서 우리는 id가 parent_id로 쓰이지 않은 케이스를 확인하면 됩니다.

 

Mysql 풀이

WITH RECURSIVE CTE AS (
    -- 최상위 부모에 대한 정의
    SELECT 
        id, 
        parent_id, 
        1 AS lvl
    FROM ecoli_data
    WHERE parent_id IS NULL    
    UNION ALL    
    -- 자식에 대한 정의 (부모와 관계)
    SELECT 
        c.id, 
        c.parent_id, 
        p.lvl + 1
    FROM CTE p JOIN ecoli_data c ON c.parent_id = p.id
)
SELECT 
    COUNT(*) AS "COUNT", 
    lvl AS "GENERATION"
FROM CTE a
WHERE NOT EXISTS (SELECT 1 FROM ecoli_data b WHERE b.parent_id = a.id)
GROUP BY 2
ORDER BY 2;

 

맺음말

문제 이해하는데 시간이 꽤 걸렸는데, 주어지는 예시와 결과 값들을 보니 훨씬 빨리 이해 할 수 있었습니다. mysql이나 postgresql 등의 오픈소스계열 dbms는 계층형 쿼리를 recursive 쿼리인 재귀함수로 작성해야 하는데 대부분 저 템플릿에서 크게 벗어나지 않으니 알아두면 실무에서도 좋습니다. 참고로 오라클에서는 connect by prior 구문을 통해 더 쉽게 사용 할 수 있습니다.(돈이 최고지..😅)