엄밀히 말하면 이 과정은 반복(iteration)이지 재귀(recursion)는 아니지만, RECURSIVE는 SQL 표준 위원회에서 선택한 용어입니다.
Postgres 재귀 쿼리의 일반적인 구조는 다음과 같습니다.
- 비재귀적 select 문
- Union 또는 union all
- 재귀적 select 문
WITH RECURSIVE name_cte AS (
SELECT statement /* non-recursive statement */
UNION [ALL]
SELECT statement /*recursive statement referencing the above select statement */
)
SELECT * FROM name_cte;
Postgres 재귀 쿼리 작동 방식
- 비재귀적 문을 평가하고 임시 테이블을 생성합니다.
- 재귀 항을 평가하고 임시 테이블에 추가합니다.
- 작업 테이블이 비워질 때까지 2단계를 반복합니다.
Union과 union all의 차이점은 union all은 중복을 허용하는 반면 union은 중복을 허용하지 않는다는 것입니다.
예제
처음 10개의 자연수
WITH RECURSIVE tens AS (
SELECT 1 as n
UNION ALL
SELECT n+1 FROM tens
)
SELECT n FROM tens limit 10;
이것은 처음 10개의 자연수를 출력하는 Postgres 재귀 쿼리의 기본 예제입니다.
자연수의 팩토리얼을 구하는 Postgres 재귀 쿼리:
WITH RECURSIVE fact (n, factorial)
AS (
SELECT 1 as n, 5 as factorial
union all
SELECT n+1, factorial*n FROM fact where n < 5
)
SELECT * FROM fact;
이 쿼리는 두 개의 테이블을 출력하는데, 하나는 처음 5개의 자연수를 포함하고 다른 테이블에는 팩토리얼을 구하기 위해 수행된 계산이 포함됩니다.
마지막 행만 출력할 수도 있지만 여기서는 반복과 계산이 어떻게 이루어지는지 볼 수 있습니다.
피보나치 수열을 출력하는 Postgres 재귀 쿼리:
WITH RECURSIVE fibb
AS (
SELECT 1::bigint as n, 0::bigint as a, 1::bigint as b
UNION ALL
SELECT n+1, b as a, (a+b) as b FROM fibb
)
SELECT b FROM fibb limit 10;
이것은 피보나치 수열을 10까지 출력합니다.
조직 계층 구조
Postgres 재귀 쿼리의 도움으로 조직 계층 구조를 찾을 수 있습니다.
테이블 생성:
INSERT INTO employees (
employee_id,
full_name,
manager_id
)
VALUES
(1, 'Abhi', NULL),
(2, 'Bhargav', 1),
(3, 'Chay', 1),
(4, 'Dravid', 1),
(5, 'Erin', 1),
(6, 'Ford', 2),
(7, 'Gagan', 2),
(8, 'Harry', 3),
(9, 'Isaac', 3),
(10, 'Jack', 4),
(11, 'Kiran', 5);
Abhi는 상사이므로 첫 번째 레벨에 있게 됩니다. Bhargav, Chay, Dravid, Erin은 다음 레벨에 있고 나머지는 마지막 레벨에 있게 됩니다.
쿼리:
WITH RECURSIVE subordinates AS (
SELECT employee_id, manager_id, full_name, 0 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.full_name, level+1
FROM employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;
출력은 다음과 같습니다.
