[MySQL] With recursive / With 구문

코딩테스트 문제를 풀어보다가 한번도 만나 보지 못한 예제를 만났다.

바로 이 예제였는데, 

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

프로그래머스

 

처음에 이 문제를 봤을 때 상당히 쉽다고 생각했었다. 어? 이 문제, 그냥 Date format 사용해서 DateTime에서 시간 값만 빼서 사용하고, 그 값을 기준으로 COUNT 값을 찍으면 되지 않을까? 라고만 생각했는데, 그렇게 풀었더니 오류가 나왔다.

SELECT CAST(DATE_FORMAT(DATETIME, "%H") AS SIGNED) HOUR, COUNT(*) COUNT

FROM ANIMAL_OUTS

GROUP BY DATE_FORMAT(DATETIME, "%H")

ORDER BY 1

 

자세히 문제를 다시 살펴보니, 예시 결과에 COUNT가 0인 값들도 표시가 되어 있는 것이 아닌가. 아니 COUNT를 썼는데 왜 한번도 사용된 적이 없는 HOUR가 COLUMN 에 있는거지? 라고 생각했는데, 다시 보니 0부터 23까지 모든 숫자가 써 있는걸 볼 수 있었다. 

 

데이터베이스에 저 시간대에 입양된 동물들의 데이터가 없는데도 모든 시간대에 COUNT를 사용한 걸 봤을 때, 아. 반복문같은걸 사용해야하나? 하는 생각이 들었다. 처음 보는 유형이었기에 정답을 먼저 살펴봤고, 하나같이 With문을 사용하고 있는 것을 봐서, 이게 내가 갖고 있던 히든 키구나 하는 생각이 들었다. 

 

그래서 오늘은 With문에 대해서 끄적여보고, 다시 한번 문제를 풀어보려고 한다.

 

 

처음 With문을 봤을 때, for 문이나 while 문을 떠올렸다. 마치 조건문에 갇혀 루프를 빠져나올 때까지 노동을 하고 있는 모습을 상상하게 된달까. 그런데 문법이 좀 많이 해괴해서 바로 동정심은 싹 사라졌다. 한번 간단한 예제를 통해서 구조를 파악해보자.

 

With 문을 사실 recursive를 쓰지 않았을 때는 subquery 느낌이 났다. 주로 반복된 쿼리문을 실행해야 할 때, 특히 간단한 반복 쿼리일수록 그 쿼리 전체에 별명을 줘서 그 쿼리를 포함하는 더 큰 쿼리 안에서 편하게 호출되어 사용되는 것이 바로 With 문이다.

 

이 구문은 CTE, 즉 Common table expression을 생성하는데, 이것은 SQL 문에서만 존재하는 일시적인 테이블(가상 테이블)을 말한다. 

 

WITH
    cte1 AS (SELECT a, b FROM table1)
    cte2 AS (SELECT c, d, FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

 

이 구문은, 작은 쿼리 각각을 cte1, cte2로 이름 짓고, JOIN을 사용해서 그것들을 더 큰 쿼리문에서 사용했다.

그리고 Recursive 키워드를 추가하면 재귀형 CTE를 만들 수 있는데, 이는 다음과 같다.

 

WITH RECURSIVE cte_count 
AS ( 
    -- Non-Recursive 문장( 첫번째 루프에서만 실행됨 )
    SELECT 1 AS n
    UNION ALL
    -- Recursive 문장(읽어 올 때마다 행의 위치가 기억되어 다음번 읽어 올 때 다음 행으로 이동함)
    SELECT n + 1 AS num 
    FROM cte_count
    WHERE n < 3 
)

SELECT * FROM test;

 

AS 내부를 보면, 우선 Non-Recursive 부분과 Recursive 부분으로 나뉘는데, Non-Recursive 부분은 첫번째 루프에서만 실행되지만, Recursive 부분은 말 그대로 조건이 끝날 때까지 작업을 계속해서 수행한다. 여기서 말하는 작업이란, 이 예제에서는 SELECT n + 1 AS num이 되겠다. 비 반복문에서 1로 시작한 n은, 반복된 작업(n+1)을 마지막 줄인 WHERE n < 3에 도달할 때까지 진행하게 된다.

 

결국 이 전체 재귀 구조는 3번 돌아가고, n이라는 컬럼 이름 아래에 1, 2, 3이 출력되게 될 것이다.

 

with recursive rc as (
  select 1 as h -- 재귀 초깃값
  union all
  select h + 1 -- 재귀
  from rc 
  where h < 5 -- 재귀 정지 조건
)

select * from rc 

 

같은 원리로 이 쿼리 역시 1부터 시작한 h가 5가 되면 반복에서 나오는 구조로, h라는 컬럼에 1, 2, 3, 4, 5가 찍히게 된다.

 

다시 문제로 돌아가서 With문을 사용해 HOURS를 만들어 주면, 다음과 같이 될것이다.

with recursive HourList as (
    select 0 as HOUR
    union all
    select HOUR+1 
    from HourList 
    where HOUR<23
)

이렇게 작성하면 0부터 23까지의 숫자를 나열할 수 있는 작은 쿼리, HourList가 완성되었다.

 

나머지 부분을 같이 적으면 다음과 같았다.

WITH RECURSIVE HourList AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1
    FROM HourList
    WHERE HOUR < 23
)
SELECT H.HOUR, IFNULL(A.COUNT, 0) AS COUNT
FROM HourList H
LEFT JOIN (
    SELECT CAST(DATE_FORMAT(DATETIME, "%H") AS SIGNED) AS HOUR, COUNT(*) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY HOUR
) A ON H.HOUR = A.HOUR
ORDER BY H.HOUR;

 

새로 만든 HourList를 FROM문에서 Left Join을 사용해서 기존 데이터베이스 내용과 합쳐줬다.