재귀 쿼리 WITH RECURSIVE

프로그래머스 문제를 풀면서 오늘도 어려웠던 것 중에 재귀 쿼리? WITH RECIRSIVE라는 것을 처음 봤다!

이렇게 처음보는 문법 같은 경우엔 신기하기도 하고, 내가 배운 파이썬이랑은 또 어떤 점이 비슷한지 비교하면서 배우면 더욱 빨리 습득할 수 있는 것 같다. 

그래서 오늘도 정리를 해보잣!

 

 

지시사항

---

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

 

 

 

 

정답 코드

---

WITH RECURSIVE NUMBER AS (
	SELECT 0 AS N
	UNION ALL
	SELECT N+1
	FROM NUMBER
	WHERE N < 23
)

SELECT N, COUNT(ANIMAL_OUTS.DATETIME) AS COUNT
FROM NUMBER
LEFT JOIN ANIMAL_OUTS
ON HOUR(ANIMAL_OUTS.DATETIME) = NUMBER.N
GROUP BY N;

 

 

 

 

 

실행 결과

---

 

 

 

 

 

 

설명

---

재귀 쿼리

WITH RECURSIVE

 

RECURSIVE는 재귀라는 의미를 갖고 있고 의미대로 쿼리를 반복적으로 실행할 때 사용한다.

 

 

재귀 쿼리를 작성하는 법

 

보통 UNION 혹은 UNION ALL을 함께 사용해서 위와 같은 형식으로 사용한다.

Anchor member에서 시작할 지점을 select 한 후에 어떻게 반복할 것인가는 Recursive member에 넣는다.

Where절에는 부등호를 통한 끝 지점을 알려주는 Termination condition 구간을 만든다.

그리고 만든 쿼리를 사용하는 Query that uses CTE 파트를 만든다.

 

 

 

 

<활용 예시>

WTIH RECURSIVE NUMBER AS (
	SELECT 0
	UNION ALL
	SELECT N+1
	FROM NUMBER
	WHERE N+1 <= 10
)
SELECT N
FROM NUMBER;

⇒ ‘N+1 <= 10’이라는 것은 SELECT N+1으로 사용되기 때문에 10 이하 숫자까지만 추출된다.

 

 

 

WTIH RECURSIVE NUMBER AS (
	SELECT 0
	UNION ALL
	SELECT N+1
	FROM NUMBER
	WHERE N <= 10
)

⇒ 단 위와 같은 상황의 경우 SELECT N+1 이므로 N ≤ 10으로 했기 때문에 N이 11까지 숫자가 추출된다. 처음 예시와 같은 결과를 갖기 위해서는 N ≤ 11로 바꾸든지, N을 N+1로 바꿔야 한다.

 

 

 

다시 오늘의 문제로 돌아와서 살펴보자!

WITH RECURSIVE NUMBER AS (
	SELECT 0 AS N
	UNION ALL
	SELECT N+1
	FROM NUMBER
	WHERE N < 23
)

SELECT N, COUNT(ANIMAL_OUTS.DATETIME) AS COUNT
FROM NUMBER
LEFT JOIN ANIMAL_OUTS
ON HOUR(ANIMAL_OUTS.DATETIME) = NUMBER.N
GROUP BY N;

재귀 쿼리로 NUMBER라는 쿼리를 만들었고 NUMBER를 기준으로 ANIMAL_OUTS를 조인했음을 알 수 있다.

SELECT N은 두 개의 테이블이 조인 후 실행되는 것이기 때문에 사용할 수 있다.

 

GROUP BY N으로 묶어주는 이유는 뭘까?

=> N으로 묶어야 값이 없더라도 0이 들어갈 수 있다. 

 

 

이게 무슨 말이냐 하면...

SELECT N, COUNT(ANIMAL_OUTS.DATETIME) AS COUNT
FROM ANIMAL_OUTS
RIGHT JOIN NUMBER
ON HOUR(ANIMAL_OUTS.DATETIME) = NUMBER.N
GROUP BY HOUR(ANIMAL_OUTS.DATETIME);

예를 들어 다음과 같이 GROUP BY를 HOUR(ANIMAL_OUTS.DATETIME)을 기준으로 바꾸어봤을 때

아래와 같은 결과를 볼 수 있다.

 

 

여기서 값이 없는 DATETIME에 대해서는 추출되지 않는 것을 볼 수 있다.

 

하지면 지시 사항에서 

" 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회"라는 말이 들어갔기 때문에 값이 없는 DATETIME의 경우에도 0으로 값이 들어가야 한다는 의미이다.

 

 

그래서 최종 코드를 살펴보자.

N으로 grouping을 하고 ANIMAL_OUTS.DATETIME에 대한 데이터만 COUNT 하는 값을 추출하는 코드이다.

WITH RECURSIVE NUMBER AS (
	SELECT 0 AS N
	UNION ALL
	SELECT N+1
	FROM NUMBER
	WHERE N < 23
)

SELECT N, COUNT(ANIMAL_OUTS.DATETIME) AS COUNT
FROM NUMBER
LEFT JOIN ANIMAL_OUTS
ON HOUR(ANIMAL_OUTS.DATETIME) = NUMBER.N
GROUP BY N;

 

 

REF

---

SQL 문법 | 데이터 조회 및 필터 | 재귀 쿼리 RECURSIVE QUERY

[SQL] Recursive 표현으로 쿼리 만들기