[MySQL] WITH, WITH RECURSIVE 사용법

 

---

CTE (Common Table Expression) 이란?

 

CTE란 기존의 뷰나 파생 테이블, 임시 테이블 등으로 사용되는 것들을 대신할 수 있고, 보다 더 간결한 표현을 사용할 수 있따는 장점이 있다. CTE는 Non-Recursive(비재귀) CTE 와 Recursive(재귀) CTE 로 두가지 종류가 있다.

 

WITH 구문은 메모리 상에 가상의 테이블을 저장할 때 사용된다.

RECURSIVE의 여부에 따라 재귀, 비재귀 두 가지 방법으로 사용 가능하다.

 

WITH CTE_테이블명(열이름1, 열이름2, 열이름3...)
AS
(
	<쿼리문>
)
SELECT 열이름1, 열이름2, 열이름3 ... FROM CTE_테이블명
--------------------------------------------------------------
WITH [RECURSIVE] TABLE명 AS (
    SELECT - # 비반복문. 무조건 필수
    [UNION ALL] # RECURSIVE 사용 시 필수. 다음에 이어붙어야 할 때 사용
    SELECT - 
    [WHERE -] # RECURSIVE 사용 시 필수. 정지 조건 필요할 때 사용
)

 

---

❓프로그래머스 59413번

프로그래머스 SQL. 입양 시각 구하기2 

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

프로그래머스

 

ANIMAL_OUTS 테이블로부터 

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

에 대한 결과를 출력해야한다.

 

|  시도

처음에는 ANIMAL_OUTS에 있는 DATETIME 값에 대해 깊게 생각해보지 않은 채로 바로 아래와 같이 시도하였다.

SELECT HOUR(DATETIME) AS HOUR, COUNT(DATETIME) AS COUNT
FROM ANIMAL_OUTS
WHERE  HOUR(DATETIME) >= 0 and HOUR(DATETIME) < 23
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME) ASC;

 

실행결과는 0부터 23시까지에 대한 결과가 아닌, ANIMAL_OUTS에 존재하는 시간 값에 대해서만 나와 9시에 대한 정보부터 나오는 문제가 있었다. 풀다가 도저히 감이 오지 않아 해결법을 찾아 보던 중 WITH RECURSIVE문을 사용하는 방법을 알게 되었다. 

 

위에서 언급한 Recursive CTE를 통해 WITH  RECURSIVE구문을 사용한다. WITH RECURSIVE를 통해 가상의 테이블을 생성하고 문제에서 주어지는 ANIMAL_OUTS와 JOIN 연산을 통해 해결하는 방법이다. 

 

우선 'SELECT ROW_NUMBER() OVER(ORDER BY DATETIME)-1 AS HOUR'을 통해 초기값으로 하는 가상의 테이블 Y를 생성한다. 

 

특정 테이블의 행번호를 붙여서 데이터를 조회를 해야할 때, ROW_NUMBER()을 사용하면 된다.

아래와 같은 경우, ANIMAL_OUTS 테이블에서  DATETIME을 기준으로 행번호를 붙여 정렬하는 쿼리다.

문제에서 0시부터 23시까지를 요구하고 있으므로, LIMIT 24와 HOUR에 대해 -1 처리를 해주었다.

 

WITH RECURSIVE Y AS (
SELECT ROW_NUMBER() OVER(ORDER BY DATETIME)-1 AS HOUR
FROM ANIMAL_OUTS 
LIMIT 24 )

 

그 다음으로는 가상의 테이블 Y와 ANIMAL_OUTS에 대해 LEFT JOIN을 해준다. JOIN문은 생각보다 간단하지만, 처음 접하는 WITH RECURSIVE 개념으로 해결하는데 많은 자료를 찾아보게 되었던 문제이다.

 

SELECT HOUR , COUNT(ANIMAL_ID) AS COUNT
FROM Y
LEFT JOIN ANIMAL_OUTS
ON DATE_FORMAT(DATETIME,"%H") = Y.HOUR
GROUP BY HOUR
ORDER BY HOUR

 

아래는 성공적으로 제출된 최종 답안이다.

 

✅ 최종 답안

WITH RECURSIVE Y AS (
SELECT ROW_NUMBER() OVER(ORDER BY DATETIME)-1 AS HOUR
FROM ANIMAL_OUTS 
LIMIT 24 )

SELECT HOUR , COUNT(ANIMAL_ID) AS COUNT
FROM Y
LEFT JOIN ANIMAL_OUTS
ON DATE_FORMAT(DATETIME,"%H") = Y.HOUR
GROUP BY HOUR
ORDER BY HOUR