CTEs(일반 테이블 식) 관련 작업하기

참고 항목:

CONNECT BY , WITH

이 항목의 내용:

CTE란 무엇입니까?

CTE(일반 테이블 식)은 WITH 절에 정의된 명명된 하위 쿼리입니다. CTE는 CTE를 정의하는 문에서 사용하기 위한 임시 로 생각할 수 있습니다. CTE는 임시 뷰의 이름, 선택 사항 열 이름 목록 및 쿼리 식(즉, SELECT 문)을 정의합니다. 쿼리 식의 결과는 사실상 테이블입니다. 이러한 테이블의 각 열은 열 이름 (선택 사항) 목록의 열에 해당합니다.

다음 코드는 CTE를 사용하는 쿼리의 예입니다.

WITH
    my_cte (cte_col_1, cte_col_2) AS (
        SELECT col_1, col_2
            FROM ...
    )
SELECT ... FROM my_cte;
Copy

위의 예에서 CTE는 my_cte (cte_col_1, cte_col_2) AS ( 를 포함하는 줄에서 시작하고 ) 를 포함하는 줄에서 끝납니다.

다음과 일치하는 CTE 이름은 선택하지 마십시오.

  • SQL 함수 이름

  • 테이블, 뷰 또는 구체화된 뷰. 쿼리가 특정 이름으로 CTE를 정의하는 경우, CTE가 테이블 등에 비해 우선 적용됩니다.

CTE는 재귀적 또는 비재귀적일 수 있습니다. 재귀적 CTE는 자체를 참조하는 CTE입니다. 재귀적 CTE는 필요한 횟수만큼 자체와 테이블을 조인하여 테이블에서 계층적 데이터를 처리할 수 있습니다.

CTEs는 모듈성을 향상하고 유지 관리를 간소화합니다.

재귀적 CTEs 및 계층적 데이터

재귀적 CTEs를 사용하면 부품 전개(구성 요소, 하위 구성 요소) 또는 관리 계층 구조(관리자, 직원) 등의 계층적 데이터를 처리할 수 있습니다. 계층적 데이터 및 계층적 데이터를 쿼리하기 위한 다른 방법에 대한 자세한 내용은 계층 구조 데이터 쿼리하기 를 참조하십시오.

재귀적 CTE를 사용하면 레벨의 수를 사전에 알 필요 없이 계층 구조의 모든 레벨을 조인할 수 있습니다.

재귀적 CTE 구문의 개요

이 섹션에서는 구문에 대한 개요 및 재귀적 방식으로 구문을 사용하는 방법에 대해 설명합니다.

WITH [ RECURSIVE ] <cte_name> AS
(
  <anchor_clause> UNION ALL <recursive_clause>
)
SELECT ... FROM ...;
Copy
여기서
anchor_clause

계층 구조의 최상단을 나타내는 행 세트 또는 초기 행을 선택합니다. 예를 들어, 회사의 모든 직원을 표시하려면 앵커 절은 회사의 사장을 선택할 수 있습니다.

앵커 절은 SELECT 문이며 지원되는 SQL 구문이 포함될 수 있습니다. 앵커 절은 cte_name 을 참조할 수 없습니다.

recursive_clause

이전 레이어를 기준으로 계층 구조의 다음 레이어를 선택합니다. 첫 번째 반복에서 이전 레이어는 앵커 절로부터의 결과 세트입니다. 이후 반복에서 이전 레이어는 가장 최근 완료된 반복입니다.

recursive_clauseSELECT 문이지만, 문은 프로젝션, 조인 및 필터로 제한됩니다. 또한, 문에서는 다음이 허용되지 않습니다.

  • 집계 또는 윈도우 함수.

  • GROUP BY, ORDER BY, LIMIT 또는 DISTINCT.

재귀적 절은 일반 테이블 또는 뷰와 같이 cte_name 을 참조할 수 있습니다.

구문에 대한 자세한 설명은 WITH 를 참조하십시오.

논리적으로 재귀적 CTE는 다음과 같이 평가됩니다.

  1. anchor_clause 를 평가한 후 최종 결과 세트 및 작업 테이블 모두에 결과가 작성됩니다. cte_name 은 사실상 작업 테이블에 대한 별칭입니다. 즉, cte_name 을 참조하는 쿼리는 작동하는 테이블에서 읽습니다.

  2. 작업 테이블이 비어 있지 않은 경우:

    1. recursive_clausecte_name 이 참조되는 작업 테이블의 현재 내용을 사용하여 평가됩니다.

    2. recursive_clause 의 결과는 최종 결과 세트 및 임시 테이블 모두에 작성됩니다.

    3. 작업 테이블을 임시 테이블의 내용으로 덮어써집니다.

사실상, 이전 반복의 출력은 작업 테이블인 cte_name 테이블에 저장되며 해당 테이블은 다음 반복에 대한 입력 중 하나입니다. 작업 테이블에는 가장 최근 반복의 결과만 포함됩니다. 현재까지의 모든 반복에서 누적된 결과는 다른 위치에 저장됩니다.

마지막 반복 이후에는 cte_name 을 참조하는 기본 SELECT 문에서 누적된 결과를 사용할 수 있습니다.

재귀 CTE 고려 사항

무한 루프 가능성

재귀 CTE를 올바르지 않게 구성하면 무한 루프가 발생할 수 있습니다. 이러한 경우에는 쿼리가 성공하거나 쿼리 시간이 초과되거나(예: STATEMENT_TIMEOUT_IN_SECONDS 매개 변수로 지정된 시간(초) 초과) 쿼리를 취소 할 때까지 쿼리가 계속 실행됩니다.

무한 루프가 발생할 수 있는 방법에 대한 정보와 이 문제를 방지하는 방법에 대한 지침은 재귀 CTE 문제 해결 섹션을 참조하십시오.

비연속 계층 구조

이 항목에서는 계층 구조 및 재귀 CTEs에서 상위-하위 관계를 사용하는 방법에 대해 설명합니다. 이 항목의 모든 예에서는 계층 구조가 연속입니다.

비연속 계층 구조에 대한 정보는 계층 구조 데이터 쿼리하기 를 참조하십시오.

이 섹션에서는 두 타입을 대비하기 위해 비연속 및 연속 CTEs 예시 모두가 제공됩니다.

비연속, 2 레벨, 셀프 조인 CTE

이 예에서는 직원 및 관리자 테이블을 사용합니다.

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);
Copy
INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);
Copy

이 직원 테이블의 2 레벨 셀프 조인은 다음과 같습니다.

SELECT
     emps.title,
     emps.employee_ID,
     mgrs.employee_ID AS MANAGER_ID, 
     mgrs.title AS "MANAGER TITLE"
  FROM employees AS emps LEFT OUTER JOIN employees AS mgrs
    ON emps.manager_ID = mgrs.employee_ID
  ORDER BY mgrs.employee_ID NULLS FIRST, emps.employee_ID;
+----------------------------+-------------+------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE              |
|----------------------------+-------------+------------+----------------------------|
| President                  |           1 |       NULL | NULL                       |
| Vice President Engineering |          10 |          1 | President                  |
| Vice President HR          |          20 |          1 | President                  |
| Programmer                 |         100 |         10 | Vice President Engineering |
| QA Engineer                |         101 |         10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | Vice President HR          |
+----------------------------+-------------+------------+----------------------------+
Copy

위의 쿼리는 모든 직원을 보여줍니다. 각 관리자의 직원은 보고서에서 관리자의 근처에 표시됩니다. 그러나 보고서에는 계층 구조가 시각적으로 표시되지 않습니다. 데이터를 자세히 보지 않으면 사용자는 조직에 몇 개의 레벨이 있는지 알 수 없으며, 특정 관리자에 연결된 직원을 확인하려면 각 행을 읽어야 합니다.

다음 섹션의 그림과 같이 재귀 CTE는 이러한 계층 구조 데이터를 측면 트리로 표시합니다.

출력이 들여쓰기 된 재귀 CTE

아래는 재귀 CTE를 사용하는 두 가지 예입니다.

  • 첫 번째 예에서는 들여쓰기를 사용하여 계층 구조의 다른 레벨을 표시합니다. 이 예를 간소화하기 위해 코드에서는 특정 순서로 행을 생성하지 않습니다.

  • 두 번째 예에서는 들여쓰기를 사용하고 각 관리자의 직원을 관리자 바로 아래에 표시합니다.

정렬되지 않은 출력

첫 번째 예는 아래와 같습니다.

 1)    WITH RECURSIVE managers
 2)          (indent, employee_ID, manager_ID, employee_title)
 3)        AS
 4)          (
 5)
 6)            SELECT '' AS indent, employee_ID, manager_ID, title AS employee_title
 7)              FROM employees
 8)              WHERE title = 'President'
 9)
10)            UNION ALL
11)
12)           SELECT indent || '--- ',
13)               employees.employee_ID, employees.manager_ID, employees.title
14)             FROM employees JOIN managers
15)               ON employees.manager_ID = managers.employee_ID
16)         )
17)
18)     SELECT indent || employee_title AS Title, employee_ID, manager_ID
19)       FROM managers
20)     ;
Copy

쿼리에는 다음 섹션이 포함됩니다.

  • 2행에는 “뷰”에 대한 열 이름(CTE)이 포함됩니다.

  • 4~16행에는 CTE가 포함됩니다.

  • 6~8행에는 CTE의 앵커 절이 포함됩니다.

  • 12~15행에는 CTE의 재귀 절이 포함됩니다.

  • 18~19행에는 CTE를 뷰로 사용하는 메인 SELECT가 포함됩니다. 이 SELECT는 다음을 참조합니다.

    • 1행에 정의된 CTE 이름(managers).

    • 2행에 정의된 CTE의 열(indent, employee_id 등).

CTE에는 다음과 같은 2개의 SELECT 문이 포함됩니다.

  • 앵커 절의 SELECT 문은 1회 실행되며 계층 구조의 첫 번째(최상위) 레벨에서 행 세트를 제공합니다.

  • 재귀 절의 SELECT는 CTE를 참조할 수 있습니다. 이 쿼리는 이전 반복의 쿼리 결과를 바탕으로 각 반복이 수행되는 반복으로 간주할 수 있습니다.

관리자/직원 예에서, 앵커 절은 첫 번째 행을 내보내며, 이 행은 회사의 사장을 설명하는 행입니다.

재귀 절의 다음 반복에서, 재귀 절은 관리자가 회사 사장인 모든 행을 찾습니다(즉, 모든 부사장을 찾음). 세 번째 반복에서는 관리자가 부사장 중 한 명인 모든 직원을 찾습니다. 검색한 모든 행이 다른 직원의 관리자가 아닌 최하위 수준 직원인 반복이 있을 때까지 반복이 계속됩니다. 이 문에서는 1회 이상 반복이 수행되며 관리자가 최하위 수준 직원인 직원을 검색합니다(그러나 찾지 않음). 이 반복에서는 0개 행이 생성되며 반복이 중단됩니다.

이 반복 전체에서 UNION ALL 절은 결과를 누적합니다. 각 반복의 결과는 이전 누적의 결과에 추가됩니다. 마지막 반복이 완료되면, 누적된 행(WITH 절에서 생성된 모든 행과 같이)을 쿼리의 기본 SELECT 절에서 사용할 수 있습니다. 그러면 해당 기본 SELECT는 그러한 행을 쿼리할 수 있습니다.

이러한 특정 예시 쿼리에서는 들여쓰기를 사용하여 데이터의 계층 구조 특성을 보여줍니다. 출력을 살펴보면, 직원의 레벨이 낮을수록 직원의 데이터의 추가적으로 들여쓰기 되었음을 볼 수 있습니다.

들여쓰기는 indent 열에 의해 관리됩니다. 들여쓰기는 0자(앵커 절의 빈 문자열)에서 시작하여 각 반복(즉, 계층 구조의 각 수준)에서 4자(---)씩 증가합니다.

당연하게, 조인을 올바르게 구성하고 재귀 절에서 올바른 열을 선택하는 것이 매우 중요합니다. 재귀 절의 SELECT 열은 앵커 절의 열과 정확하게 일치해야 합니다. 쿼리는 사장에서 시작하여, 부사장을 선택한 후 부사장의 직속 부하를 선택하는 순서로 진행된다는 점을 기억하십시오. 각 반복에서는 manager_id 필드가 이전 반복에서 생성된 managers.employee_id 값 중 1개에 해당하는 직원을 찾습니다.

즉, 관리자 “뷰”의 직원 ID는 직원의 다음 레벨에 대한 관리자 ID입니다. 직원 IDs는 각 반복 중에 계층 구조(사장, 부사장, 고위 간부, 하급 관리자 등)에서 아래로 진행해야 합니다. 직원 IDs가 진행되지 않으면, 쿼리가 무한 루프를 수행하거나(동일한 manager_ID 가 다른 반복의 managers.employee_ID 열에 계속 표시되는 경우) 레벨을 건너뛰거나 다른 방식으로 실패하게 됩니다.

정렬된 출력

이전 예에서는 ORDER BY 절이 없으므로 각 직원의 레코드가 올바르게 들여쓰기 된 경우에도 각 직원이 관리자 바로 아래에 표시되지 않는 경우가 있습니다. 아래 예는 들여쓰기가 올바른 출력을 생성하며 각 관리자의 직원은 관리자의 바로 아래에 표시됩니다.

쿼리의 ORDER BY 절에서는 추가 열인 sort_key 가 사용됩니다. 정렬 키는 재귀 절이 반복됨에 따라 누적되며, 정렬 키는 본인 상위의 전체 명령 체인(관리자, 관리자의 관리자 등)을 포함하는 문자열로 간주할 수 있습니다. 해당 명령 체인의 최고위 관리자(사장)는 정렬 키 문자열의 처음에 위치합니다. 일반적으로는 정렬 키를 표시하지 않지만, 출력을 보다 쉽게 이해할 수 있도록 아래 쿼리에서는 출력에 정렬 키를 포함합니다.

각 반복은 정렬 키의 길이와 동일한 길이(동일한 문자 수)로 증가하므로, 쿼리에서는 다음 정의와 함께 이름이 skey 인 UDF(사용자 정의 함수)를 사용하여 일정한 길이의 정렬 키 세그먼트를 생성합니다.

CREATE OR REPLACE FUNCTION skey(ID VARCHAR)
  RETURNS VARCHAR
  AS
  $$
    SUBSTRING('0000' || ID::VARCHAR, -4) || ' '
  $$
  ;
Copy

SKEY 함수 출력의 예는 다음과 같습니다.

SELECT skey(12);
+----------+
| SKEY(12) |
|----------|
| 0012     |
+----------+
Copy

쿼리의 최종 버전은 다음과 같습니다. 여기에서는 각 관리자의 직원을 해당 관리자의 바로 아래에 추가하고 직원의 “레벨”에 따라 들여씁니다.

WITH RECURSIVE managers 
      -- Column list of the "view"
      (indent, employee_ID, manager_ID, employee_title, sort_key) 
    AS 
      -- Common Table Expression
      (
        -- Anchor Clause
        SELECT '' AS indent, 
            employee_ID, manager_ID, title AS employee_title, skey(employee_ID)
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT indent || '--- ',
            employees.employee_ID, employees.manager_ID, employees.title, 
            sort_key || skey(employees.employee_ID)
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT 
         indent || employee_title AS Title, employee_ID, 
         manager_ID, 
         sort_key
    FROM managers
    ORDER BY sort_key
  ;
+----------------------------------+-------------+------------+-----------------+
| TITLE                            | EMPLOYEE_ID | MANAGER_ID | SORT_KEY        |
|----------------------------------+-------------+------------+-----------------|
| President                        |           1 |       NULL | 0001            |
| --- Vice President Engineering   |          10 |          1 | 0001 0010       |
| --- --- Programmer               |         100 |         10 | 0001 0010 0100  |
| --- --- QA Engineer              |         101 |         10 | 0001 0010 0101  |
| --- Vice President HR            |          20 |          1 | 0001 0020       |
| --- --- Health Insurance Analyst |         200 |         20 | 0001 0020 0200  |
+----------------------------------+-------------+------------+-----------------+
Copy

다음 쿼리는 계층 구조의 이전(상위) 레벨에서 필드를 참조하는 방법을 보여주며, 특히 mgr_title 열에 유의하십시오.

WITH RECURSIVE managers 
      -- Column names for the "view"/CTE
      (employee_ID, manager_ID, employee_title, mgr_title) 
    AS
      -- Common Table Expression
      (

        -- Anchor Clause
        SELECT employee_ID, manager_ID, title AS employee_title, NULL AS mgr_title
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT 
            employees.employee_ID, employees.manager_ID, employees.title, managers.employee_title AS mgr_title
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT employee_title AS Title, employee_ID, manager_ID, mgr_title
    FROM managers
    ORDER BY manager_id NULLS FIRST, employee_ID
  ;
+----------------------------+-------------+------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_TITLE                  |
|----------------------------+-------------+------------+----------------------------|
| President                  |           1 |       NULL | NULL                       |
| Vice President Engineering |          10 |          1 | President                  |
| Vice President HR          |          20 |          1 | President                  |
| Programmer                 |         100 |         10 | Vice President Engineering |
| QA Engineer                |         101 |         10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | Vice President HR          |
+----------------------------+-------------+------------+----------------------------+
Copy

부품 전개

관리자/직원 계층 구조는 단일 테이블에 저장하여 재귀 CTE로 처리할 수 있는 유일한 변수-깊이 계층 구조의 형식이 아닙니다. 다른 일반적인 계층 구조 데이터의 예는 “부품 전개”로, 여기서는 각 구성 요소가 하위 구성 요소와 함께 나열되며 각각은 하위의 하위 구성 요소와 함께 나열됩니다.

예를 들어, 테이블에 계층 구조 데이터(예: 자동차의 구성 요소)가 포함되어 있다고 가정해 보겠습니다. 자동차에는 엔진, 휠 등의 구성 요소가 포함될 수 있습니다. 이러한 여러 구성 요소에는 하위 구성 요소(예: 엔진에는 연료 펌프가 포함될 수 있음)가 포함됩니다. 연료 펌프에는 모터, 배관 등이 포함될 수 있습니다. 모든 구성 요소 및 하위 구성 요소는 재귀 CTE를 사용하여 나열할 수 있습니다.

부품 전개를 생성하는 쿼리의 예는 WITH 를 참조하십시오.

재귀 CTE 문제 해결

재귀 CTE 쿼리는 성공하거나 시간 초과될 때까지 실행됨

이 문제는 다음의 다른 두 가지 상황에서 발생할 수 있습니다.

  • 데이터 계층 구조에 주기가 있습니다.

  • 사용자가 무한 루프를 생성했습니다.

원인 1: 주기적 데이터 계층 구조

데이터 계층 구조에 주기(즉, 진정한 트리가 아님)가 포함된 경우에는 가능한 해결책이 여러 개입니다.

해결책 1.1:

데이터에 주기가 포함되지 않은 경우 데이터를 수정합니다.

해결책 1.2:

일부 방법(예: 출력의 행 수 제한)으로 쿼리를 제한합니다. 예:

WITH RECURSIVE t(n) AS
    (
    SELECT 1
    UNION ALL
    SELECT N + 1 FROM t
   )
 SELECT n FROM t LIMIT 10;
Copy
해결책 1.3:

재귀 CTE가 포함된 쿼리를 사용하지 마십시오. 계층 구조 데이터가 필요합니다.

원인 2: 무한 루프

recursive_clause 의 프로젝션 절에서 “하위”(현재 반복)가 아닌 “상위”(이전 반복)의 값을 출력하고 다음 반복에서 조인에서 현재 반복의 값을 사용해야 하는 조인의 값을 사용하는 경우 무한 루프가 발생할 수 있습니다.

다음 의사 코드는 이러한 상황에 대한 대략적인 예를 보여줍니다.

CREATE TABLE employees (employee_ID INT, manager_ID INT, ...);
INSERT INTO employees (employee_ID, manager_ID) VALUES
        (1, NULL),
        (2, 1);

WITH cte_name (employee_ID, manager_ID, ...) AS
  (
     -- Anchor Clause
     SELECT employee_ID, manager_ID FROM table1
     UNION ALL
     SELECT manager_ID, employee_ID   -- <<< WRONG
         FROM table1 JOIN cte_name
           ON table1.manager_ID = cte_name.employee_ID
  )
SELECT ...
Copy

이 예에서 재귀 절은 현재/하위 값(employee_id)을 갖는 열의 상위 값(manager_id)을 전달합니다. 상위 값은 다음 반복에서 “현재” 값으로 표시되며 “다음 세대에 현재” 값으로 다시 전달되므로, 쿼리가 레벨을 통해 아래로 진행되지 않고 매번 같은 레벨에서 처리됩니다.

1단계:

앵커 절에서 employee_id = 1manager_id = NULL 값을 선택한다고 가정해 보겠습니다.

CTE:

employee_ID  manager_ID
-----------  ---------
      1         NULL
Copy
2단계:

재귀 절의 첫 번째 반복 중에, table1employee_id = 2manager_id = 1.

CTE:

employee_ID  manager_ID
-----------  ----------
       1         NULL
Copy

table1:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...
Copy

재귀 절에서 조인의 결과:

table1.employee_ID  table1.manager_ID  cte.employee_ID  cte.manager_ID
-----------------   -----------------  ---------------  --------------
 ...
       2                   1                 1                NULL
 ...
Copy

프로젝션:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...
Copy

그러나 employee_idmanager_id 열은 프로젝션에서 반전되므로 쿼리의 실제 출력(및 그러므로 다음 반복 시작 시 CTE의 내용)은 다음과 같습니다.

employee_ID  manager_ID
-----------  ----------
 ...
       1         2        -- Because manager and employee IDs reversed
 ...
Copy
3단계:

재귀 절의 두 번째 반복 중에:

CTE:

employee_ID  manager_ID
-----------  ----------
       1         2
Copy

table1:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...
Copy

재귀 절에서 조인의 결과:

table1.employee_ID  table1.manager_ID  cte.employee_ID  cte.manager_ID
-----------------   -----------------  ---------------  --------------
 ...
       2                   1                 1                2
 ...
Copy

프로젝션:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...
Copy

쿼리의 결과(다음 반복 시작 시 CTE의 내용):

employee_ID  manager_ID
-----------  ----------
 ...
       1         2        -- Because manager and employee IDs reversed
 ...
Copy

보다시피, 두 번째 반복의 마지막에 CTE의 행은 반복 시작 시의 행과 동일합니다.

  • employee_id1 입니다.

  • manager_id2 입니다.

그러므로 다음 반복 중에 조인의 결과는 현재 반복 중에 조인의 결과와 동일하며, 쿼리가 무한대로 순환합니다.

무한 루프를 생성한 경우:

해결책 2:

재귀 절이 올바른 변수를 올바른 순서로 전달하는지 확인합니다.

또한, 재귀 절의 JOIN 조건이 올바른지 확인합니다. 일반적인 사례에서 “현재” 행의 상위는 상위 행의 하위/현재 값으로 조인되어야 합니다.