[MYSQL] 📚 RECURSIVE (재귀 쿼리)

WITH RECURSIVE 문 (재귀 쿼리)

프로그래밍에서 재귀 함수를 들어봤듯이, SQL에서도 재귀 쿼리 기법이 존재한다.

다만 문법이 굉장히 해괴한데 우선 WITH RECURSIVE 쿼리문을 작성하고 내부에 UNION을 통해 재귀를 구성하는 것이 포인트이다.

WITH RECURSIVE cte_count 
AS ( 
    -- Non-Recursive 문장( 첫번째 루프에서만 실행됨 )
    SELECT 1 AS n
    UNION ALL
    -- Recursive 문장(읽어 올 때마다 행의 위치가 기억되어 다음번 읽어 올 때 다음 행으로 이동함)
    SELECT n + 1 AS num 
    FROM cte_count
    WHERE n < 3 
)

SELECT * FROM test;

1. 메모리 상에 가상의 테이블을 저장한다.
2. 반드시 UNION 사용해야한다.
3. 반드시 비반복문(Non-Recursive)도 최소한 1개 요구된다. 처음 한번만 실행
4. SubQuery에서 바깥의 가상의 테이블을 참조하는 문장(반복문)이 반드시 필요하다.
5. 반복되는 문장은 반드시 정지조건(Termination condition)이 요구된다.
6. 가상의 테이블을 구성하면서 그 자신(가상의 테이블)을 참조하여 값을 결정할 때 유용한다.

---

재귀 쿼리 문제 예시

 

Q. h(컬럼)이 초기값 1 부터 제어문에 합당하는 5까지의 데이터를 갖는 가상 테이블 생성

with recursive rc as (
  select 1 as h -- 재귀 초깃값
  union all
  select h + 1 -- 재귀
  from rc 
  where h < 5 -- 재귀 정지 조건
)

select * from rc

※ 결과 확인

---

Q. ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다.
ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

​보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다.

0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

SQL문을 실행하면 다음과 같이 나와야 합니다.

HOUR COUNT
0 0
1 0
2 0
3 0
4 0
5 0
6 0
7 3
8 1
9 1
10 2
11 13
12 10
13 14
14 9
15 7
16 10
17 12
18 16
19 2
20 0
21 0
22 0
23 0

​

정답)

with recursive rc as (
    select 0 as hour
    union all
    select hour+1 from rc where hour<23
)

select 
    rc.hour, 
    count(hour(A.datetime)) as count
from rc
left join animal_ins as A
on rc.hour = hour(A.datetime) -- 재귀숫자와 datetime시간숫자가 같은것 만
group by rc.hour -- 집계함수를 썼으니 그룹핑, 안그러면 다 더해서 레코드 하나만 반환

---

# 참고자료

https://velog.io/@cyanred9/SQL-Recursive