78강 SQL 초석 다지기 : PostgreSQL Recursive Query

PostgreSQL 기초강의, PostgreSQL 기초강좌

78강 SQL 초석 다지기 : PostgreSQL Recursive Query   

 

SQL 초석 다지기 78강 시작합니다.

 

이번 시간에는

▶ PostgreSQL Recursive Query

에 대해서 알아보는 시간입니다.

 

이번 시간에는 재귀적 공통 테이블 표현식 또는 CTE를 사용한 PostgreSQL  쿼리에 대해 알아봅니다.

---

Introduction to the PostgreSQL recursive query   

PostgreSQL은 쿼리에 사용할 보조 문을 구성할 수 있는 WITH 문을 제공합니다.

이러한 문은 흔히 일반적인 테이블 표현식 또는 CTE로 지칭된다. CTE는 쿼리 실행 중에만 존재하는 임시 테이블과 같습니다.

recursive 쿼리는 recursive CTE를 참조하는 쿼리입니다. recursive 쿼리는 조직 구조, 재료 명세서 등과 같은 계층적 데이터를 쿼리 하는 것과 같은 많은 상황에서 유용합니다.

다음은 재귀 CTE의 구문을 보여줍니다:

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

 

recursive CTE는 세 가지 요소로 구성된다:

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

 

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

• 비재귀 항을 실행하여 기본 결과 집합(R0)을 생성합니다.
• 결과 집합 Ri+1을 출력으로 반환하려면 Ri를 입력으로 하여 재귀 항을 실행합니다.
• 빈 세트가 반환될 때까지 2단계를 반복합니다. (종료 확인)
• 결과 집합 R0, R1, … Rn의 UNION 또는 UNION ALL인 최종 결과 집합을 반환합니다

설명이 좀 어렵습니다.

재귀쿼리란? 재귀라는 것은 영어로는 RECURSIVE, 즉 반복의 의미를 담고 있다. 재귀쿼리는 다시 말해서 반복적으로 작동될 쿼리라고 할 수 있고 '어떠한 쿼리를 반복적으로 실행한다'라고 이해하면 좋다. 그렇다면 반복적인 작업이 왜 필요할까? 이유는 자료들의 계층을 파악하기 위해서이다. 데이터들은 각각 종속성과 피종속성을 갖지 고 있다. 단순하지 않은 그런 속성을 한 번 데이터를 읽음으로서 완벽히 파악하기 어렵다. 따라서 그럴 때 재귀쿼리를 이용 해 종속의 종속의 종속... 과 같이 반복되는 종속성을 파악해서 계급도나 가문도 같은 데이터의 계층 구조를 정확히 파악할 수 있다.

예제를 통해 이해해야 할 것 같습니다.

---

PostgreSQL recursive query example   

PostgreSQL  recursive 쿼리를 시연하기 위해 새 테이블을 만들 것이다.

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

 

직원 테이블에는 세 개의 열(employee_id, manager_id 및 full_name)이 있습니다.  manager_id 열은 직원의 관리자 ID를 지정합니다.

다음 문은 샘플 데이터를 직원 테이블에 삽입합니다.

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); 

 

다음 쿼리는 ID가 2인 관리자의 모든 하위 항목을 반환합니다.

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는 하나의 비재귀적 용어와 하나의 재귀적 용어를 정의한다.
• 비재귀 항은 ID가 2인 직원인 기본 결과 집합 R0을 반환합니다.

결과1

재귀 용어는 직원 ID 2의 직접 하위 항목을 반환합니다. 이것은 직원 테이블과 부하 CTE가 합류한 결과입니다. 재귀 용어의 첫 번째 반복은 다음 결과 집합을 반환합니다:

결과2

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

결과3

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

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

최종결과

employee_id = 2 인 사원부터 직원과 매니저로 구성된 계층구조를 반복해서 조회해서 보여줍니다.

---

이번 강의에서는 재귀 CTE를 사용하여 PostgreSQL  재귀쿼리를 구성하는 방법을 배웠습니다.

 

다 외울 필요는 없습니다. 재귀쿼리가 있다는 것만 기억하시면,  모를 때 찾아보고 하면 됩니다.

---

본 강의는 PostgreSQL Tutorial을 참조합니다.  https://www.postgresqltutorial.com/

Do it! SQL을 찾아 주셔서 감사합니다. 공감 ♥ , 댓글이 큰 힘이 됩니다.