재귀적 CTE(Common Table Expression)

SQL 에서 반복적인 계산을 수행하거나 계층적 데이터를 쉽게 처리할 수 있도록 도와주는 기능이다.

임시 결과 집합을 정의하는데, 재귀적 CTE는 자기 자신을 참조하여 반복적으로 확장할 수 있다.

 

수많은 테스트 더미 데이터를 추가할 때 효과적이다.

 

• 앵커 멤버: 재귀가 시작되는 기본 쿼리
• 재귀 멤버: 앵커 멤버의 결과를 바탕으로 반복적으로 실행되는 쿼리

WITH RECURSIVE cte_name AS (
    -- 앵커 멤버
    SELECT ...
    UNION ALL
    -- 재귀 멤버
    SELECT ...
    FROM cte_name
    WHERE ...
)
SELECT * FROM cte_name;

 

예시로 유저 데이터를 10,000 개 삽입하는 쿼리를 재귀적 CTE 로 구현

cte_max_resursion_depth 는 재귀적 CTE 의 최대 깊이를 설정한다. (기본 값은 1,000)

user 테이블에는 id(PK), amount 필드가 있습니다.

amount 값을 모두 0으로 설정하는 쿼리입니다.

-- 재귀적 CTE를 사용하여 10,000개의 행을 생성하고 user 테이블에 삽입
SET SESSION cte_max_recursion_depth = 10000;

INSERT INTO user (amount)
WITH RECURSIVE cte AS (
    SELECT 1 AS n  -- 앵커 멤버: 시작 값을 정의 (여기서는 1)
    UNION ALL
    SELECT n + 1  -- 재귀 멤버: n을 1씩 증가시키면서 다음 행을 생성
    FROM cte
    WHERE n < 10000  -- 재귀 조건: n이 10,000보다 작은 동안 반복
)
SELECT 0  -- CTE가 생성한 각 행에 대해 amount 필드에 0을 삽입
FROM cte;

select 0 from cte; 를 통해 cte 결과를 사용하여 insert 합니다.

 

+ H2 DB 를 사용할 경우

INSERT INTO user (amount)
SELECT 0
FROM system_range(1, 10000);