[PostgreSQL] BASIC - Recursive Query

 

 

재귀 공통 테이블 표현식 또는 CTE를 사용하는 PostgreSQL 재귀 쿼리 사용하는 방법

 

 

WITH 에 사용할 보조 명령문을 구성할 수 있는 명령문을 제공합니다 .

이러한 명령문은 흔히 공통 테이블 표현식 또는 CTE라고 합니다. 

CTE는 쿼리 실행 중에만 존재하는 임시 테이블과 같습니다.

재귀 쿼리는 재귀 CTE를 참조하는 쿼리입니다. 

재귀 쿼리는 조직 구조, BOM 등과 같은 계층적 데이터 쿼리와 같은 많은 상황에서 유용합니다.

 

WITH RECURSIVE cte_name AS(
    CTE_query_definition -- non-recursive term
    UNION [ALL]
    CTE_query definion  -- recursive term
) SELECT * FROM cte_name;

• 비재귀적 용어: 비재귀적 용어는 CTE 구조의 기본 결과 집합을 형성하는 CTE 쿼리 정의입니다.
• 재귀 용어: 재귀 용어는 UNION 또는 UNION ALL연산자를 사용하여 비재귀 용어와 결합된 하나 이상의 CTE 쿼리 정의 입니다. 재귀 용어는 CTE 이름 자체를 참조합니다.
• 종료 확인: 이전 반복에서 반환된 행이 없으면 재귀가 중지됩니다.

 

PostgreSQL은 다음 순서로 재귀적 CTE를 실행합니다.

1. 비재귀 항을 실행하여 기본 결과 집합(R0)을 만듭니다.
2. Ri를 입력으로 사용하여 재귀 항을 실행하여 결과 집합 Ri+1을 출력으로 반환합니다.
3. 빈 집합이 반환될 때까지 2단계를 반복합니다. (종료 확인)
4. UNION 또는 UNION ALL결과 집합 R0, R1, … Rn 의 최종 결과 집합을 반환합니다.

반응형

---

CREATE TABLE employees (
	employee_id serial PRIMARY KEY,
	full_name VARCHAR NOT NULL,
	manager_id INT
);

INSERT INTO employees (
	employee_id,
	full_name,
	manager_id
)
VALUES
	(1, 'Michael North', NULL),
	(2, 'Megan Berry', 1),
	(3, 'Sarah Berry', 1),
	(4, 'Zoe Black', 1),
	(5, 'Tim James', 1),
	(6, 'Bella Tucker', 2),
	(7, 'Ryan Metcalfe', 2),
	(8, 'Max Mills', 2),
	(9, 'Benjamin Glover', 2),
	(10, 'Carolyn Henderson', 3),
	(11, 'Nicola Kelly', 3),
	(12, 'Alexandra Climo', 3),
	(13, 'Dominic King', 3),
	(14, 'Leonard Gray', 4),
	(15, 'Eric Rampling', 4),
	(16, 'Piers Paige', 7),
	(17, 'Ryan Henderson', 7),
	(18, 'Frank Tucker', 8),
	(19, 'Nathan Ferguson', 8),
	(20, 'Kevin Rampling', 8);

 

WITH RECURSIVE subordinates AS (
	SELECT
		employee_id,
		manager_id,
		full_name
	FROM
		employees
	WHERE
		employee_id = 2
	UNION
		SELECT
			e.employee_id,
			e.manager_id,
			e.full_name
		FROM
			employees e
		INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
	*
FROM
	subordinates;

 

작동 방식:

• 재귀 CTE인 subordinates 는 하나의 비재귀 용어와 하나의 재귀 용어를 정의합니다.
• 비재귀적 용어는 ID가 2인 직원인 기본 결과 집합 R 0을 반환합니다 .

 employee_id | manager_id |  full_name
-------------+------------+-------------
           2 |          1 | Megan Berry

 

재귀적 용어는 직원 ID 2의 직속 부하를 반환합니다. 이것은 직원  테이블과  부하 직원  CTE 를 조인한 결과입니다 . 재귀 항의 첫 번째 반복은 다음 결과 집합을 반환합니다.

 employee_id | manager_id |    full_name
-------------+------------+-----------------
           6 |          2 | Bella Tucker
           7 |          2 | Ryan Metcalfe
           8 |          2 | Max Mills
           9 |          2 | Benjamin Glover

 

PostgreSQL은 재귀 용어를 반복적으로 실행합니다. 재귀 멤버의 두 번째 반복은 단계 위의 결과 집합을 입력 값으로 사용하고 다음 결과 집합을 반환합니다.

 employee_id | manager_id |    full_name
-------------+------------+-----------------
          16 |          7 | Piers Paige
          17 |          7 | Ryan Henderson
          18 |          8 | Frank Tucker
          19 |          8 | Nathan Ferguson
          20 |          8 | Kevin Rampling

 

세 번째 반복은 ID가 16, 17, 18, 19 및 20인 직원에게 보고하는 직원이 없기 때문에 빈 결과 집합을 반환합니다.

PostgreSQL은 비재귀 및 재귀 용어에 의해 생성된 첫 번째 및 두 번째 반복의 모든 결과 집합의 합집합인 최종 결과 집합을 반환합니다.

 employee_id | manager_id |    full_name
-------------+------------+-----------------
           2 |          1 | Megan Berry
           6 |          2 | Bella Tucker
           7 |          2 | Ryan Metcalfe
           8 |          2 | Max Mills
           9 |          2 | Benjamin Glover
          16 |          7 | Piers Paige
          17 |          7 | Ryan Henderson
          18 |          8 | Frank Tucker
          19 |          8 | Nathan Ferguson
          20 |          8 | Kevin Rampling
(10 rows)