무슨 말인지 보자마자 대장균, 분화, 배양, 세대 용어들이 나오면서 해석하는데도 어질어질합니다.
훑어서 읽고 쭉쭉 내려서 예시를 보니 각 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 구문을 통해 더 쉽게 사용 할 수 있습니다.(돈이 최고지..😅)
댓글