개요
- CTE 구문으로 반복문 쿼리 호출하기 위함
- snowflake 뿐만 아니라 다른 DBMS(mysql, postgres,...) 에서도 같은 문법 사용됨
- 날짜 테이블 생성할 때도 사용 가능
기본 문법
[ WITH [ RECURSIVE ]
<cte_name1> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause )
[ , <cte_name2> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause ) ]
[ , <cte_nameN> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause ) ]
]
SELECT ...
-------------------------------------------------------
anchorClause ::=
SELECT <anchor_column_list> FROM ...
recursiveClause ::=
SELECT <recursive_column_list> FROM ... [ JOIN ... ]
-- CTE_NAME 옆 괄호 안에 들어가는 변수들이 첫 번째 서브쿼리문 컬럼들 > 반복문 첫 행 값이라 생각하면 됨
WITH RECURSIVE cte_name (X, Y) AS
(
SELECT related_to_X, related_to_Y FROM table1
UNION ALL
-- UNION ALL 절 다음으로 나오는 서브쿼리는 반복문이 실행될 쿼리문
SELECT also_related_to_X, also_related_to_Y
FROM cte_name
where <join_condition>
)
SELECT ... FROM ...
예제
1. factorial 연산
- WITH RECURSIVE factorial_calculation은 재귀 CTE의 이름을 정의합니다.
- 초기값으로 1! = 1을 설정합니다.
- 각 단계에서 n 값을 증가시키고, 그에 따라 팩토리얼 값을 계산합니다.
- n이 5에 도달할 때까지 계산을 반복하여 1부터 10까지의 팩토리얼을 출력합니다.
with recursive temp_factorial(n ,num )
as
(
select 0 as n, 1 as num
union all
select n + 1, num * (n+1)
from temp_factorial
where n<10
)
select * from temp_factorial;
----------------------------------------------
N NUM
0 1
1 1
2 2
3 6
4 24
5 120
6 720
7 5040
8 40320
9 362880
10 3628800
2. 피보나치 수열(fibonacci)
- WITH RECURSIVE fibonacci_sequence는 재귀 CTE의 이름을 정의합니다.
- 초기값으로 첫 번째와 두 번째 피보나치 수를 0과 1로 설정합니다.
- 재귀적으로 이전 두 수의 합을 계산하여 다음 수를 생성합니다.
- n이 21에 도달할 때까지 수열을 생성하여 1부터 20까지의 피보나치 수열을 출력합니다
with recursive temp_fibonacci (n, f1, f2)
as
(
select 1 as n, 0 as f1,1 as f2
union all
select n+1, f2, f1 + f2
from temp_fibonacci
where n<=20
)
select * from temp_fibonacci
;
----------------------------------------------
N F1 F2
1 0 1
2 1 1
3 1 2
4 2 3
5 3 5
6 5 8
7 8 13
8 13 21
9 21 34
10 34 55
11 55 89
12 89 144
13 144 233
14 233 377
15 377 610
16 610 987
17 987 1597
18 1597 2584
19 2584 4181
20 4181 6765
21 6765 10946
3. 계승(팩토리얼) 값 합산 (Sum of Factorials)
- WITH RECURSIVE factorial_sum는 재귀 CTE의 이름입니다.
- 초기값으로 1! = 1을 설정하고, 이후 각 재귀 단계에서 팩토리얼을 계산합니다.
- n이 5에 도달할 때까지 반복하며, 최종적으로 모든 팩토리얼 값의 합계를 계산하여 출력합니다
WITH RECURSIVE factorial_sum AS (
SELECT 1 AS n, 1 AS factorial
UNION ALL
SELECT n + 1, factorial * (n + 1)
FROM factorial_sum
WHERE n < 5
)
SELECT SUM(factorial) AS sum_of_factorials FROM factorial_sum;
4. 문자열 반복 생성 (String Repetition)
- WITH RECURSIVE string_repeater는 재귀 CTE의 이름입니다.
- 초기값으로 iteration = 1과 repeated_string = 'A'를 설정합니다.
- 각 단계에서 문자열 'A'를 반복하여 추가합니다.
- 5번 반복할 때까지 진행하며, 최종적으로 반복된 문자열을 출력합니다.
WITH RECURSIVE string_repeater AS (
SELECT 1 AS iteration, 'A' AS repeated_string
UNION ALL
SELECT iteration + 1, repeated_string || 'A'
FROM string_repeater
WHERE iteration < 5
)
SELECT repeated_string FROM string_repeater;
5. 날짜 생성기 (Date Generator)
- WITH RECURSIVE date_generator는 재귀 CTE의 이름입니다.
- 초기값으로 현재 날짜(CURRENT_DATE)를 설정합니다.
- 각 재귀 단계에서 날짜를 하루씩 증가시킵니다.
- 10일 간의 날짜를 생성하여 출력합니다.
WITH RECURSIVE date_generator AS (
SELECT CURRENT_DATE AS date_value
UNION ALL
SELECT date_value + INTERVAL '1 day'
FROM date_generator
WHERE date_value < CURRENT_DATE + INTERVAL '9 days'
)
SELECT date_value FROM date_generator;
참고
https://docs.snowflake.com/en/sql-reference/constructs/with
