TIL - SQL WITH 재귀 쿼리

프로그래머스에서 SQL 문제 중 입양 시각 구하기(2) 문제를 푸는데 결과에 없는 시간대를 어떻게 만들어야 할지 몰라서 질문하기를 참고 했는데 MySQL의 경우 with을 사용한 재귀 풀이가 많았다.

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

 

SQL 재귀 쿼리에 대해 찾다가 MySQL Recursive CTE에 대해 이미지와 함께 잘 설명되어 있는 페이지를 발견했다.

https://www.mysqltutorial.org/mysql-basics/mysql-recursive-cte/

 

CTE의 요소

이미지 출처 : https://www.mysqltutorial.org/mysql-basics/mysql-recursive-cte/

 

재귀적 CTE의 실행 단계

1. 앵커와 재귀 멤버를 분리
2. 앵커 멤버는 초기 행( SELECT 1)을 형성하므로 첫 번째 반복에서는 n = 1인 1 + 1 = 2가 생성
3. 두 번째 반복은 첫 번째 반복의 출력(2)에 대해 작동하고 n = 2인 2 + 1 = 3을 생성
4. 세 번째 작업(n = 3) 이전에 종료 조건( n < 3)이 충족되어 쿼리가 중지
5. UNION ALL 연산자를 사용하여 모든 결과 세트 1, 2, 3을 결합

재귀 멤버의 제한

• 재귀 멤버에는 다음 구문을 포함할 수 없음
  • 집계 함수(MAX, MIN, SUM, AVG, COUNT 등)
  • GROUP BY
  • ORDER BY
  • LIMIT
  • DISTINCT

RECURSIVE : RECURSIVE 유무에 따라 재귀, 비재귀 두가지 방법으로 사용 가능

https://www.mysqltutorial.org/mysql-basics/mysql-cte/

 

예제를 보면 RECURSIVE를 제외하면 서브쿼리를 만들어 사용할 때와 동일한 예제를 볼 수 있다.

with를 단순히 서브쿼리를 생성하는 용도로만 사용했었는데 재귀로도 사용하다는 걸 이번에 알게 되었다.

 

다음은 위 예시를 참고해 작성한 SQL 문제 정답이다.

WITH RECURSIVE hour AS (
    SELECT 0 AS h 
    UNION ALL 
    SELECT h + 1
    from hour 
    where h < 23
)
SELECT t1.h AS HOUR, IFNULL(count, 0) AS COUNT
FROM hour t1
    LEFT JOIN (
        SELECT HOUR(datetime) AS hour, COUNT(animal_id) AS count
        FROM animal_outs
        GROUP BY hour
        ORDER BY hour
    ) t2
    ON t1.h = t2.hour