[MYSQL] 프로그래머스: 재귀문 (recursive) 사용하기 + 예제

 

 

WITH와 함께 recursive를 쓰면 재귀 서브쿼리를 만들 수 있다.

구조는 do while문과 비슷한데, 

anchor member 부분에 초기 세팅 쿼리를 작성하고

아래의 recursive member에 반복할 쿼리를 작성한 뒤

union all로 두 쿼리를 이어주면 된다.

가장 아래에는 재귀쿼리릐 중단조건을 작성해줘야 한다.

 

1. 프로그래머스 : 입양 시각 구하기(2)

WITH RECURSIVE hours AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1 FROM hours WHERE hour < 23
)
SELECT h.hour, COUNT(a.animal_id) AS count
FROM hours h
LEFT JOIN animal_outs a 
ON h.hour = HOUR(a.datetime)
GROUP BY h.hour
ORDER BY h.hour;

0시부터 23시까지 각 시간대별로 발생한 입양 건수를 구하는 문제입니다.

입양이 없었던 시간도 표시해야 하므로 0부터 23까지 틀을 만들어주기 위해 재귀쿼리가 필요합니다.

select 0으로 초기 세팅 해주고 필드명은 hour로 설정합니다.

여기까지 하면 hour 필드에 0이 있는 hours 릴레이션이 생성됩니다.

이 hours 릴레이션을 사용해서 재귀를 돌립니다.

hour가 23이 되기 전까지 hour에 1을 더해주는 쿼리를 쓰고

union all로 이미 생성되있는 hours 릴레이션과 합쳐주면

hour 필드에 0부터 23까지 들어있는 hours 릴레이션이 완성됩니다.

재귀 결과

여기에 더해서 문제를 풀어나가면 됩니다.

 

2. 프로그래머스 : 멸종위기의 대장균 찾기

WITH RECURSIVE gens AS (
    -- Anchor member: 첫 세대 고르기
    SELECT id, 1 AS gen
    FROM ecoli_data
    WHERE parent_id IS NULL
    -- 결합
    UNION ALL
    -- Recursive member: 시전 세대로부터 자식세대 고르기
    SELECT e.id, g.gen + 1
    FROM ecoli_data e
    INNER JOIN gens g ON e.parent_id = g.id
),
children AS (
    SELECT a.id AS parent, b.id AS child
    FROM ecoli_data a
    LEFT JOIN ecoli_data b ON a.id = b.parent_id
)
SELECT COUNT(*) as count, g.gen as generation
FROM gens g
LEFT JOIN children c ON g.id = c.parent
WHERE c.child IS NULL
GROUP BY g.gen;

 

응용문제입니다. 

join을 이용해 재귀적으로 이어지는 세대를 찾는 문제입니다.

ecoli_data 릴레이션

 

첫 세대는 parent_id  정보가 없어서 찾기 쉽습니다.

하지만 다음 세대들은 parent_id가 몇 번 타고 올라가는지 알아야 해서 재귀문이 필요합니다.

anchor memeber에 첫 세대를 찾는 gens 릴레이션을 생성하는 쿼리를 작성해주고

recursive member에 gens를 이용해서 두번째 세대부터 재귀적으로 세대를 계산하는 쿼리를 작성해줍니다.

그리고 이 둘을 union all로 결합해주면 전체 세대를 표시하는 릴레이션이 생성됩니다.

재귀 결과

 

이렇게 최종 생성된 gens를 활용해 문제를 풀어나가면 됩니다.

 

recursive를 알아두면 복잡한 쿼리문도 잘 쓸 수 있을 것입니다.

감사합니다.