[프로그래머스] MySQL Recursive CTE 동작원리 (입양 시각 구하기)

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

입양시각 구하기 문제 풀던 중, 분명 정답이 맞는데 왜 틀리지? 싶어서 보니 count가 0인 hour도 출력해야 정답처리가 되는 것이었다.

hour	count
0	0
1	2
2	0
3	4

 

위와 같은 느낌으로 출력해야 되는 것이었다.

 

그냥 하드코딩으로 0부터 23까지 SELECT해서 LEFT JOIN 하는 방법도 있겠지만 좀 더 효율적인 방법이 있을 것이라 생각했다.

질문하기 보니까 다들 Recursive 얘기를 하길래 '오 SQL에서도 그런 기능이 있구나' 싶어서 찾아봤는데, Python처럼 직관적으로 for문이 돌아가는 로직이 아니라서 좀 헷갈렸다.

 

아래는 나의 정답 코드.

WITH RECURSIVE hour_table AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour+1
    FROM hour_table
    WHERE hour < 23
)

, animal AS (
    SELECT hour(datetime) AS hour, animal_id
    FROM animal_outs
)

SELECT h.hour, count(animal_id) AS count
FROM hour_table h
LEFT JOIN animal a
ON a.hour = h.hour
GROUP BY 1
ORDER BY 1

 

한 줄 한 줄 돌아가는 걸 설명해보자면, 일단 RECURSIVE CTE의 기본 문법은

WITH RECURSIVE 테이블명 AS (
    SELECT 초기 테이블 세팅값
    UNION ALL
    SELECT 반복할 조건
    FROM 테이블명
    WHERE 정지조건
)

 

이다.

 

내 코드를 통해 테이블 순서가 어떻게 만들어지는 지를 살펴보면..

SELECT 0 AS hour를 통해 아래와 같은 테이블이 만들어질 것이다.

hour

0

 

그러고나면 UNION ALL 다음부터 반복문이 계속 시작되는 것이다.

초기에는 WHERE절에서 걸릴 일이 없다. hour_table의 hour가 0이기 때문에 +1을 해주고 아래로 UNION ALL을 하면

hour

0

1

 

이후 hour는 1이 되었고, 조건절 hour<23에 부합하므로 다시 RECURSIVE 돌고 UNION ALL을 하게 된다.

hour

0

1

2

 

.

.

.

.

 

이런 식으로 쭉 나가다 보면 hour가 22가 되는 경우 hour+1=23이므로 마지막 UNION ALL을 수행하고

hour
0
1
2
3
.
.
.
.
20
21
22
23

 

이제는 hour가 23이기 때문에 WHERE hour<23에 부합하지 않기 때문에 실행 종료된다.

 

 

이후 hour_table을 기준으로 LEFT JOIN 하면 아래와 같은 테이블이 나온다. (실제 정답 화면)

hour	count
0	0
1	0
... (중략)	(중략)
11	13
12	10
... (중략)	(중략)
22	0
23	0

 

공교롭게도 예제 문제는 0~6시, 20~23시에 입양이 발생하지 않은 것으로 되어서 앞뒤가 다 count=0이다.

 

현업에서 (MySQL은 아니지만) SQL을 쓰면서도 파이썬이 뭔가 훨씬 편해서, SQL로는 그냥 대충 파티션만 나눠서 추출한 다음 csv로 뽑아서 판다스로 데이터 가공하는 경우가 있었는데, 점점 쓰다보니 SQL도 잘만 쓰면 할 수 있는 게 많더라.

RECURSIVE는 앞으로도 뭔가 쓸 일이 있지 않을까 싶어서 기록해둠.

 

끝.