[MySQL] Recursive CTE

코딩테스트 문제를 연습하다가 기존 테이블을 활용하지 않는 새로운 테이블을 만들어야하는 문제에 닥쳤다. 

이를 해결하기 위해 찾아보다가 Recursive CTE라는 새로운 문법을 알게 되었다. 

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

프로그래머스

 

 

Recursive CTE

Recursive CTE는 재귀 CTE로, 서브쿼리에서 스스로를 참조하는 CTE이다. 

이는 시리즈 생성 혹은 계층적, 트리 구조의 데이터 순회에 특히 유용하다. 

 

기본적인 Recursive CTE (재귀 CTE)의 구성 요소는 아래와 같다. 

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

 

# 결과
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

 

Recursive CTE의 일반적인 형태는 위와 같으며, UNION으로 구분된 2개의 파트로 나누어져 있다. 

SELECT ...    -- 최초 행 반환 (non recursive)
UNION ALL
SELECT ...    -- 추가 행 반환 (recursive)

 

즉, 1부터 5까지 출력한 위의 SQL 쿼리는

처음 1을 출력하는 SELECT 문과

이후 5보다 작은 n에 재귀적으로 n + 1을 반복하는 두 번째 SELECT로 구성되어 있다. 

 

이러한 CTE 구문은 두 번째 SELECT 문이 더 이상 행을 생성하지 않을 때, 재귀문이 끝나게 된다. 

이와 같은 부분은 파이썬의 while 문과 굉장히 비슷하다! 

이 부분에서 메모리를 줄이기 위해 효율적인 반복 조건을 명시하는 것이 중요하다. 

 

Recursive CTE 데이터 크기

재귀 CTE에서 행의 데이터 크기는 재귀적이지 않은 파트(첫 번째 SELECT문)에 의해 정해진다. 

즉, 아래와 같은 SQL 쿼리가 있을 때, 

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

 

'abcabc....'와 같은 문자열이 생성되는 것이 아니라, 아래와 같이 char(3)의 크기로 고정된 abc만 출력되는 것이다. 

# 결과
+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

 

만약, 이와 같은 형태를 원하지 않는다면, 형 변환을 통해 이를 해결해줘야 한다. 

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, CAST('abc' AS CHAR(20)) 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 |
+------+--------------+

 

 

Recursive CTE 예시

단순하게 차례대로 데이터를 생성하는 것 말고, 좀 더 효율적으로 사용할 수 있는 예시를 알아보자. 

 

가장 전형적인 예는 피보나치 수열을 만드는 것이다. 

이는 앞의 두 숫자의 합을 게산하는 것이다. 

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS (   
      -- 첫 값 지정
      SELECT 1, 0, 1   
      UNION ALL   
      SELECT n + 1, next_fib_n, fib_n + next_fib_n     
      FROM fibonacci 
      WHERE n < 7
)
SELECT * FROM fibonacci;

 

# 결과
+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
+------+-------+------------+

 

 

다음으로, 날짜 순서를 만드는 예시이다. 

처음의 날짜를 지정해준 후, 하나씩 더해주는 쿼리를 작성해주면

원하는 날짜에서 하나씩 더한 날짜 테이블을 얻을 수 있다.!

WITH RECURSIVE dates(date) AS (
   SELECT '2020-02-01' 
   UNION ALL
   SELECT date + INTERVAL 1 DAY 
   FROM dates
   WHERE date < '2020-02-07' 
)

 

# 결과
+------------+
| date       |
+------------+
| 2020-02-01 |
| 2020-02-02 |
| 2020-02-03 |
| 2020-02-04 |
| 2020-02-05 | 
| 2020-02-06 |
| 2020-02-07 |
+------------+

 

 

제한 사항

Recursive CTE를 사용할 때는 아래와 같은 구문을 사용할 수 없다.

• SUM() 과 같은 집계 함수
• GROUP BY
• ORDER BY
• DISTINCT
• Window functions

이러한 구문은 일반 CTE에서 쉽게 사용할 수 있지만, 재귀적 CTE에서는 사용할 수 없다! 

 

 

느낀점

일반적인 CTE는 자주 사용했었는데도, 이런 문법은 처음봤다. 

신기하면서도 잘 익혀두면 편리할 것 같은 구문이다. 

잘 기억해둬야겠다!