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

무한 루프 가능성¶

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

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

비연속 계층 구조¶

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

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

정렬되지 않은 출력¶

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

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

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

• 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

원인 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 = 1 및 manager_id = NULL 값을 선택한다고 가정해 보겠습니다.

CTE:

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

Copy

2단계:

재귀 절의 첫 번째 반복 중에, table1 의 employee_id = 2 및 manager_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_id 및 manager_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_id 는 1 입니다.

• manager_id 는 2 입니다.

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

무한 루프를 생성한 경우:

해결책 2:

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

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