[TIL] SQL WITH recursive (재귀)

오늘 SQL 문제를 풀면서 만났던 문제에 대해서 정리해본다.

 

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

프로그래머스

처음 접근은 DATETIME 컬럼에서 HOUR()를 사용하여 시간만 추출하면 되는 줄 알았으나 예시를 확인해보면 COUNT가 0인 즉, 0시부터 23시까지의 모든 원소가 있어야 했다.

 

이 부분에서는 알고 있는 지식이 부족해서 다른 분들의 풀이를 참고했고, 그 결과 다음과 같은 내용을 새로 알게 되었다.
https://www.mysqltutorial.org/mysql-recursive-cte/

MySQL Recursive CTE

 

우선 WITH 절은 '이름을 가진 SUBQUERY를 정의하는 구문'이다

WITH name (column1 [,column2]) ] AS (
    SUB QUERY
)
MAIN QUERY
..

 

이 중에서 재귀를 이용하면 위에 내가 직면한 문제를 해결할 수 있었다.

 

WITH recursive의 구문은 다음과 같다.

WITH recursive cte-name as(
    initial_query -- anchor member
    UNION ALL 
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;

 

• anchor member : 앵커 멤버, 초기 쿼리 부분
• recursive member : 재귀 멤버, UNION ALL로 인해서 재귀 멤버는 or 연산자로 앵커 멤버와 결합된다.
• 재귀 멤버가 행을 반환하지 않을 때 재귀가 중지된다. (where 절 사용 등)

이렇게 작성하게 되면 0시 부터 23시까지의 HOUR 컬럼을 구성할 수 있다.

WITH recursive A as(
    select 0 as HOUR
    union all
    select HOUR+1 from A
    where HOUR < 23 
)