[MySQL] WITH RECURSIVE

WITH RECURSIVE

📌 

프로그래머스 59413 번 문제를 풀다가 0시부터 23시까지의 모든 시간에 대한 COUNT 를 해야 하는 경우가 생겼다. 언어로 그걸 구현하는 것은 단순 for() 를 사용해서 처리하는 등의 다양한 방법이 있지만 SQL 에서는 어떠한 방법으로 구현을 해야 할 지 막막했다. UNION 으로 해결할 수는 있지만 예쁜 코드? 가 아닌 것 같아 다양한 풀이를 찾아보다 with recursive 라는 기능을 찾게 되었다. 추후에도 유용하게 사용할 수 있을 것 같아 정리하고 넘어가고자 이 글을 작성한다.

 

📚 WITH RECURSIVE

MySQL에서 제공하는 WITH RECURSIVE 구문은 말 그대로 재귀함수처럼 동작한다.

WITH RECURSIVE 생성할_뷰_이름 AS
(
	초기_SQL
    
    	UNION ALL

	반복할_SQL_반복을_멈출_WHERE절_포함
)

 

구성은 위와 같다. 이렇게 문법만 보기에는 이해가 어려우니 몇가지 예시를 통해 사용법에 대해 알아보자

 

⭐ 사용법

----------------
| col1 |  col2 |
----------------
|  1   |   2   |
|  3   |   4   |
----------------

위와 같은 결과가 나오게 WITH RECURSIVE 를 사용해보자

 

WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

임시 테이블명 (컬럼, ..) AS 로 지정해준다.

WITH cte AS
(
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

AS 로 직접 컬럼을 지정해줘도 된다.

 

그렇다면 0 ~ 23 까지를 나타내려면 어떻게 사용하면 될까 ? 

WITH RECURSIVE cte (HOUR) AS
(
    SELECT 0
    UNION ALL
    SELECT HOUR + 1 FROM cte WHERE HOUR < 23
)

 

위에서의 SQL 처럼 HOUR 이라는 컬럼을 지정해주고 HOUR 이 23보다 작을 때까지 재귀 호출을 해준다고 생각하면 된다.

 

간단한 사용법까지 알아봤으니 해결하지 못했던 프로그래머스의 문제를 풀이해보자.

 

 

📚 프로그래머스, 59413

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

프로그래머스

 

요구사항

: ANIMAL_OUTS 테이블에서 시간 별 입양 건을 출력.

 

풀이

WITH RECURSIVE T (HOUR) AS
(
    SELECT 0
    UNION ALL
    SELECT HOUR + 1 FROM T WHERE HOUR < 23
)

0 ~ 23까지의 시간을 나타낼 수 있는 임시 테이블, T를 생성한다.

WITH RECURSIVE T (HOUR) AS
(
    SELECT 0
    UNION ALL
    SELECT HOUR + 1 FROM T WHERE HOUR < 23
)

SELECT t.HOUR AS HOUR, COUNT(a.ANIMAL_ID) AS COUNT
FROM T t
LEFT JOIN ANIMAL_OUTS a
ON t.HOUR = HOUR(a.DATETIME)
GROUP BY HOUR
ORDER BY HOUR ASC

하나하나 풀이해보자. COUNT 를 해야 한다. 기준이 되는 T 테이블 말고 ANIMAL_OUTS 에 컬럼이 없는 경우도 있기 때문에 INNER JOIN 이 아닌 LEFT JOIN 을 해야 한다. (COUNT = 0 의 경우) 그렇기 때문에 COUNT 의 기준의 ANIMAL_OUTS 테이블의 ANIMAL_ID로 잡아줬다. 마지막으로 정렬만 해주면 문제를 해결할 수 있다.