[SQL] 재귀 쿼리 (WITH RECURSIVE) 이해하기

SQL 계의 반복문

 

WITH RECURSIVE란?

재귀적(반복적)으로 어떤 쿼리를 실행하기 위해 만든 임시 테이블이다.

with RECURSIVE hour_tbl as
(
    SELECT 0 as HOUR -- 초기 구문
    UNION ALL 
    -- 반복해서 실행할 구문
    SELECT HOUR+1 FROM hour_tbl WHERE HOUR < 12
)

 

문법 형태는 위와 같다.

SELECT 0 as HOUR 구문을 초기 구문으로 하고 UNION ALL 이후 구문을 반복해서 실행할 구문으로 두면 된다.

 

실행하면 아래와 같다.

 

이 구문의 원리를 쉽게 이해해보자.

SELECT 0 as HOUR 는 마치 int HOUR = 0; 과 같은 초기 선언과 같다.

UNION ALL 구문은 두 SQL문의 결과를 합치는 것이다.

SELECT HOUR+1 as 'HOUR' FROM hour_tbl WHERE HOUR < 12 구문은 HOUR = HOUR + 1; 과 같은 구문이다. 그런데, WHERE HOUR < 12 이므로 HOUR가 12 이상이면 반복되던 구문이 종료된다.

 

팩토리얼로 WITH RECURSIVE를 이해해보자.

1!	1	1
2!	1*2	1!*2
3!	1*2*3	2!*3
4!	1*2*3*4	3!*4
n!	1*2*3*4*....*n	(n-1)!*n

 

위의 표에서 우리는 n!를 구하려면 (n-1)! 에 n을 곱하면 된다는 것을 알 수 있다.

재귀적으로 이전 값에 특정 n을 곱하는 연산을 반복하고 있다.

 

이를 SQL으로 구현하면 아래와 같다. (단, n < 7까지만 구한다.)

with RECURSIVE fact as
(
    SELECT 1 as 'n', 1 as 'result'
    UNION ALL
    SELECT n+1 as 'n', (n+1)*result as 'result' FROM fact WHERE n < 7
)

SELECT *
FROM fact

 

초기값은 n = 1 (1!) 일 때 그 값은 1 이다.

그리고 재귀적으로 n이 늘어나면서 이전의 팩토리얼 연산값과 n을 곱해준다.

n+1이 8이 되는 시점에는 WHERE 조건문에 걸리므로 RECURSIVE 문을 빠져나오게 된다.

 

 

언제 필요할까?

1. 가상의 테이블을 만들어서 붙이고 싶은 경우

예제 : 프로그래머스_입양 시각 구하기(2)

 

 

쿼리를 돌렸더니 왼쪽과 같은 결과가 나온다.

그런데, 나는 HOUR이 없는 경우 0으로 출력하도록 오른쪽처럼 만들고 싶다. 그런데 지금 조회되는 테이블에는 1,2,3,4,5,6 과 같은 레코드가 없다.

 

이런 경우 가상의 HOUR 테이블을 하나 만들어두고 LEFT JOIN으로 붙이면 된다.

with RECURSIVE hour_tbl as
(
    SELECT 0 as HOUR
    UNION ALL
    SELECT HOUR+1 FROM hour_tbl WHERE HOUR < 23
)

 

위와 같은 코드를 실행하면 아래와 같이 뜬다.

 

위 테이블과 기존에 조회했던 테이블을 아래와 같이 LEFT JOIN 하면 된다.

with RECURSIVE hour_tbl as
(
    SELECT 0 as HOUR
    UNION ALL
    SELECT HOUR+1 FROM hour_tbl WHERE HOUR < 23
),
hour_to_count as
(
    SELECT hour(datetime) as 'HOUR', count(*) as 'COUNT'
    FROM ANIMAL_OUTS
    GROUP BY hour(datetime)
)

SELECT h.hour as 'HOUR', ifnull(c.count, 0) as 'COUNT'
FROM hour_tbl h LEFT JOIN hour_to_count c ON h.hour = c.hour
ORDER BY 1 asc

 

2. 특정 SQL 구문이 반복되는 경우

SELF JOIN을 하여 특정 부모 자식의 세대수를 구하는 유형에 해당한다.

예제 : 프로그래머스_특정 세대의 대장균 찾기

 

with first_gen as
(
    SELECT ID
    FROM ECOLI_DATA
    WHERE parent_id is null
),
# # 2세대의 자식 ID 조회
second_gen as
(
    SELECT p.ID
    FROM ECOLI_DATA p INNER JOIN first_gen e ON p.parent_id = e.id
),
# # 3세대의 자식 ID 조회
third_gen as
(
    SELECT p.ID
    FROM ECOLI_DATA p INNER JOIN second_gen e ON p.parent_id = e.id
)

 

위 코드를 보면 공통되는 부분이 보이는가?

눈치가 빠른 독자라면 2세대와 3세대에서 ECOLI_DATA 테이블과 그 이전 세대의 자식 ID를 조회한 테이블을 INNER JOIN하는 비슷한 형태를 눈치챘을 것이다.

 

이렇게 반복되는 부분을 WITH RECURSIVE 구문으로 아래와 같이 해결할 수 있다.

with RECURSIVE generation as
(	-- basecase : 1세대
    SELECT ID, 1 as 'gen'
    FROM ECOLI_DATA
    WHERE parent_id is null
    UNION ALL
    -- RECURSIVE : 2세대 이후 (2세대는 1세대를 참조, 3세대는 2세대를 참조, 4세대는 3세대를 참조...)
    SELECT p.ID, gen+1 as 'gen'
    FROM ECOLI_DATA p INNER JOIN generation e ON p.parent_id = e.id
)

 

이해 자료

아래 유튜브 영상을 통해 더 잘 이해할 수 있다.

https://www.youtube.com/watch?v=x-fatv0cd6c