[SQL] Recursive Common Table Expressions(CTE)

24시간(0~23시), 12개월(1~12월) 등 모든 시기에 대하여 집계를 하고자 한다. 그러나 테이블에 일부 시기에 대한 데이터가 빠져있는 경우가 있을 수 있는데, 이때 Recursive Common Table을 유용하게 사용해볼 수 있다.

• Recursive Common Table 구문

WITH RECURSIVE cte AS
(
  SELECT [초기값 as 변수명]  # 변수 여러 개 정의 가능
  UNION ALL
  SELECT [반복수식] FROM cte [WHERE 반복 조건]
)
SELECT * FROM cte;

• (예시 1) WITH문을 이용하여 테이블을 정의하며, n으로 정의한 변수가 1부 5까지 하나씩 증가하는 테이블은 아래와 같이 구성할 수 있다.

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

n
1
2
3
4
5

• (예시 2) RECURSIVE CTE는 문자열 데이터를 생성하는 데에도 유용하게 사용할 수 있다.
  아래 쿼리에서는 'abc'라는 문자를 str 변수에 담고 이를 계속해서 병합해나갔다.

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, CAST('abc' AS CHAR(1000)) AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

n	str
1	abc
2	abcabc
3	abcabcabcabc

• (예시 3) RECURSIVE CTE는 날짜 데이터를 생성하는 데에도 유용하게 사용할 수 있다.
  아래 쿼리에서는 '2024-07-08'라는 날짜데이터를 date 변수에 담고 하루씩 늘려나간다.

WITH RECURSIVE dates AS
(
  SELECT '2024-07-08' as `date`
  UNION ALL
  SELECT ADDDATE(date, 1) FROM dates
  WHERE ADDDATE(date, 1) <= ('2024-07-15')
)
SELECT * FROM dates;