[MySQL] WITH RECURSIVE

WITH (Common Table Expressions)

A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times. The following discussion describes how to write statements that use CTEs.

Recursive Common Table Expressions

A recursive common table expression is one having a subquery that refers to its own name. For example:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

When executed, the statement produces this result, a single column containing a simple linear sequence:

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

 

프로그래머스의 SQL 고득점 Kit에 출제된 문제의 My SQL 솔루션 중 하나입니다. 

(문제 참조는 https://school.programmers.co.kr/learn/courses/30/lessons/59413)

 

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

SELECT HOUR, IF(SELECTED.CNT IS NULL, 0, SELECTED.CNT) AS COUNT 
FROM HOUR_TABLE
LEFT JOIN 
    (SELECT HOUR(DATETIME) AS H, COUNT(ANIMAL_ID) AS CNT
    FROM ANIMAL_OUTS 
    GROUP BY 1) SELECTED
ON HOUR_TABLE.HOUR = SELECTED.H

 

이 쿼리는 "ANIMAL_OUTS"라는 테이블에서 동물이 나간 시간을 기반으로 시간대별 동물 수를 계산하는 것을 목표로 합니다.

먼저, WITH RECURSIVE 문을 사용하여 "HOUR_TABLE"이라는 임시 테이블을 생성합니다. 이 테이블은 "HOUR"이라는 하나의 열을 가지며, 0부터 시작하여 1씩 증가하며 값을 가집니다. UNION ALL과 WHERE 조건을 사용하여 23보다 작은 값까지 재귀적으로 행을 생성합니다. 이를 통해 시간대(0부터 23까지)를 나타내는 임시 테이블을 만듭니다.

그런 다음, 하위 쿼리를 사용하여 "ANIMAL_OUTS" 테이블에서 시간대별 동물 수를 계산합니다. HOUR 함수를 사용하여 "DATETIME" 열에서 시간을 추출하고, "ANIMAL_ID" 열의 개수를 COUNT 함수를 사용하여 계산합니다. GROUP BY 문을 사용하여 시간대별로 그룹화합니다.

마지막으로, LEFT JOIN을 통해 "HOUR_TABLE" 테이블과 "ANIMAL_OUTS" 테이블의 결과를 조인한 후, IF 문을 사용하여 동물 수가 NULL인 경우에는 0을 반환하고, 그렇지 않은 경우에는 해당 동물 수를 반환합니다. 이를 통해 모든 시간대에 대한 동물 수를 조회할 수 있습니다.

결과적으로, 이 쿼리는 0부터 23까지의 시간대별로 "ANIMAL_OUTS" 테이블에서 동물이 나간 횟수를 계산하고, 결과를 시간대와 함께 반환합니다.

 

[출처]

https://dev.mysql.com/doc/refman/8.0/en/with.html

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