계층 구조 데이터 쿼리하기¶

이 항목에서는 다음을 사용하여 계층 구조 데이터를 저장 및 쿼리하는 방법에 대해 설명합니다.

JOINs
재귀 CTEs(일반 테이블 식)
CONNECT BY

참고 항목:: CONNECT BY, WITH 명령의 재위 CTE 부분, CTEs(일반 테이블 식) 관련 작업하기, 테이블 형식 SQL UDF(UDTF)

이 항목의 내용:

계층 구조 데이터 저장하기¶

여러 데이터 타입은 계층 구조로 표현하는 것이 가장 적합하며, 그러한 예는 트리입니다.

예를 들어, 직원은 회사의 사장이 계층 구조의 최상단에 위치하는 계층 구조로 구성되는 경우가 많습니다.

계층 구조의 다른 예는 “부품 전개”입니다. 예를 들어, 자동차에는 엔진이 포함되고 엔진에는 연료 펌프가 포함되며 연료 펌프에는 호스가 포함됩니다.

계층 구조 데이터를 저장할 수 있는 위치는 다음과 같습니다.

테이블의 계층 구조.
계층 구조(예: 각 직원의 직속 상사를 나타냄)를 나타내는 열이 1개(이상)인 단일 테이블

두 방법 모두 아래에서 설명이 제공됩니다.

참고

이 항목에서는 정형 데이터로 저장된 계층적 데이터에 초점을 맞춥니다. 계층적 데이터는 반정형 데이터로 저장할 수도 있습니다(예: JSON 데이터는 ARRAY, OBJECT 또는 VARIANT 데이터 타입으로 저장할 수 있음). 반정형 데이터에 대한 자세한 내용은 다음을 참조하십시오.

반정형 데이터

반정형 데이터 쿼리하기

여러 테이블에 걸친 계층 구조 데이터¶

관계형 데이터베이스에서는 다른 테이블을 사용하여 계층 구조 데이터를 저장하는 경우가 많습니다. 예를 들어, 한 테이블에는 “상위” 데이터가 포함되고 다른 테이블에는 “하위” 데이터가 포함될 수 있습니다. 전체 계층 구조를 미리 알고 있는 경우, 계층 구조의 각 레이어를 위한 테이블을 1개 생성할 수 있습니다.

예를 들어, 직원 정보 및 관리자 정보를 저장하는 Human Resources 데이터베이스에 대해 생각해 보겠습니다. 회사의 규모가 작으면 레벨이 2개만(예: 관리자 1명 및 직원 2명) 있을 수 있습니다.

CREATE OR REPLACE TABLE managers  (title VARCHAR, employee_ID INTEGER);

Copy

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);

Copy

INSERT INTO managers (title, employee_ID) VALUES
    ('President', 1);
INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('Vice President Engineering', 10, 1),
    ('Vice President HR', 20, 1);

Copy

단일 테이블의 계층 구조 데이터¶

일부 상황에서는 계층 구조에서 레벨의 수가 변경될 수 있습니다.

예를 들어, 회사가 성장함에 따라 2개 레벨 계층 구조(사장 및 기타 직원)로 시작된 회사에서 레벨의 수가 증가할 수 있습니다. 이러한 회사는 사장, 부사장 및 일반 직원이 포함되도록 확장될 수 있습니다.

레벨의 수를 알 수 없기 때문에 알려진 개수의 테이블을 사용하여 계층 구조를 생성할 수 없는 경우에는 계층 구조 데이터를 1개 테이블에 저장할 수 있는 경우가 있습니다. 예를 들어, 1개 테이블에 모든 직원을 포함하고 동일 테이블의 다른 직원을 가리키는 각 직원의 관리자_ID를 저장하는 열을 포함할 수 있습니다. 예:

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

1개 테이블에 전체 데이터 계층 구조를 저장하는 것은 계층 구조의 모든 레벨이 동일한 데이터(이 예에서의 직원 ID, 직책 등)를 저장하는 경우에 가장 적합합니다. 다른 레벨의 데이터가 동일한 레코드 구조에 적합하지 않은 경우 모든 데이터를 1개 테이블에 저장하는 것은 적합하지 않습니다.

조인을 사용하여 계층 구조 데이터 쿼리하기¶

2개 레벨 계층 구조(예: 관리자 및 직원)에서 데이터는 양방향 조인을 사용하여 쿼리할 수 있습니다.

SELECT 
        employees.title, 
        employees.employee_ID, 
        managers.employee_ID AS MANAGER_ID, 
        managers.title AS "MANAGER TITLE"
    FROM employees, managers
    WHERE employees.manager_ID = managers.employee_ID
    ORDER BY employees.title;
+----------------------------+-------------+------------+---------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE |
|----------------------------+-------------+------------+---------------|
| Vice President Engineering |          10 |          1 | President     |
| Vice President HR          |          20 |          1 | President     |
+----------------------------+-------------+------------+---------------+

Copy

3개 레벨 계층 구조의 경우에는 3방향 조인을 사용할 수 있습니다.

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

이러한 개념은 필요한 레벨의 수에 따라 확장할 수 있습니다. 단, 필요한 레벨의 수를 알아야 합니다. 그러나 레벨의 개수가 변경되면 쿼리를 변경해야 합니다.

CONNECT BY 또는 재귀 CTEs를 사용하여 계층 구조 데이터 쿼리하기¶

Snowflake는 레벨의 개수를 미리 알 수 없는 경우 계층 구조 데이터를 쿼리하기 위한 두 가지 방법을 제공합니다.

재귀 CTEs(일반 테이블 식).
CONNECT BY 절.

재귀 CTE를 사용하면 자신을 참조할 수 있는 WITH 절을 만들 수 있습니다. 이를 통해 계층 구조의 각 레벨을 반복하고 결과를 누적할 수 있습니다.

CONNECT BY 절을 사용하면 한 번에 한 계층 구조 레벨을 소유하는 JOIN 연산의 타입을 생성하고 각 레벨이 이전 레벨의 데이터를 참조하도록 할 수 있습니다.

자세한 내용은 다음을 참조하십시오.

셀프 조인, 재귀 CTE 및 CONNECT BY 사이의 차이¶

CONNECT BY 에서는 셀프 조인만 사용할 수 있습니다. 재귀 CTEs는 보다 유연하며 테이블을 1개 이상의 다른 테이블과 조인할 수 있습니다.

CONNECT BY 절은 가장 강력한 재귀 CTE입니다. 그러나 재귀 CTE를 사용하면 CONNECT BY 로는 불가능한 일부 작업을 수행할 수 있습니다.

예를 들어, 재귀 CTE의 예를 살펴보면 한 쿼리는 출력을 들여쓰고 각 “하위” 항목이 해당 “상위” 항목 아래에 표시되도록 출력을 정렬하는 것을 볼 수 있습니다. 정렬은 최상위부터 현재 레벨까지 IDs 체인을 포함하는 정렬 키를 생성하여 수행됩니다. 관리자/직원의 예에서 체인에는 사장 ID, 부사장 ID 등의 순서로 포함됩니다. 이러한 정렬 키는 측면 트리와 유사한 방식으로 행을 그룹화합니다. “START WITH” 절에서는 코드가 정렬_키와 같은 추가 열을 지정(테이블 내의 열이 아닌)할 수 없으므로 CONNECT BY 구문은 이를 지원하지 않습니다. 아래 두 코드 조각을 비교해 보겠습니다.

SELECT indent(LEVEL) || employee_ID, manager_ID, title
  FROM employees
    -- This sub-clause specifies the record at the top of the hierarchy,
    -- but does not allow additional derived fields, such as the sort key.
    START WITH TITLE = 'President'
    CONNECT BY ...

WITH RECURSIVE current_layer
   (employee_ID, manager_ID, sort_key) AS (
     -- This allows us to add columns, such as sort_key, that are not part
     -- of the employees table.
     SELECT employee_ID, manager_ID, employee_ID AS sort_key
     ...
     )

Copy

그러나 SYS_CONNECT_BY_PATH 함수를 사용하면 CONNECT BY 절과 유사한 효과를 볼 수 있습니다.

START WITH 절은 행에 이미 있는 열에 열을 추가할 수 없으므로(행에 이미 있는 값에서 열이 파생된 경우에도) CONNECT BY 절의 버전은 제한적이지만, 다음과 같은 이점이 있습니다.

열 목록에 해당 열을 지정하지 않고 각 행의 모든 열에 액세스할 수 있습니다. 재귀 CTE에서 재귀 절은 CTE에 명시적으로 지정되지 않은 열에 액세스할 수 없습니다.
재귀 CTE에서는 CTE에 반드시 열을 지정해야 하며, 앵커 절 및 재귀 절의 선택 항목 프로젝트 목록은 모두 CTE의 열과 일치해야 합니다. 여러 프로젝션 절에서 열의 순서가 일치하지 않으면 무한 루프 등의 문제가 발생할 수 있습니다.
CONNECT BY 구문은 LEVEL, CONNECT_BY_ROOT 및 CONNECT_BY_PATH 등의 편리한 의사 열을 지원합니다.

CONNECT BY 와 재귀 CTE 사이의 사소한 차이점은 CONNECT BY 에서는 PRIOR 키워드를 사용하여 이전 반복에서 가져온 열 값을 나타내야 하는 반면에 재귀 CTE에서는 테이블 이름 및 CTE 이름을 사용하여 현재 반복에서 가져온 값 및 이전 반복에서 가져온 값을 나타내야 한다는 점입니다. (재귀 CTE에서는 소스 테이블 또는 테이블 식이 아닌 CTE 열 목록에서 다른 열 이름을 사용하여 현재와 이전 반복을 구분할 수 있습니다.)

비연속 계층 구조¶

이 항목에서는 계층 구조 및 재귀 CTEs(일반 테이블 식) 및 CONNECT BY 절에서 상위-하위 관계를 사용하는 방법에 대해 설명합니다. 이 항목의 모든 예 및 CONNECT BY 설명서와 재귀 CTE 설명서의 모든 예에서 계층 구조는 인접합니다. 어떠한 예에서도 상위 항목과 손자 항목 사이에 해당 하위 항목이 없는 상위 항목과 손자 항목은 없습니다.

예를 들어, 자동차의 “부품 전개”를 수행하는 경우 타이어가 포함된 휠에 대한 구성 요소(및 자동차에 포함된 구성 요소) 없이는 자동차에 대한 구성 요소 및 타이어에 대한 구성 요소를 가질 수 없습니다.

그러나 데이터가 불완전한 경우가 있을 수 있습니다. 예를 들어, 직원/관리자 계층 구조에서 엔지니어링 부문의 부사장이 퇴임하고 회사가 대체 임원을 즉시 고용하지 않는 경우를 가정해 보겠습니다. VP의 직원 레코드가 삭제되면 VP 하위의 직원은 계층 구조의 나머지에서 “절단”되므로 더 이상 직원 테이블에 단일의 연속 계층 구조가 포함되지 않습니다.

재귀 CTEs 또는 CONNECT BY 를 사용하여 데이터를 처리하는 경우에는 테이블의 데이터가 단일의 연속 트리를 나타내는지 고려해야 합니다. 여러 트리가 포함된 단일 테이블에서 재귀 CTEs 및 CONNECT BY 를 사용할 수 있지만, 한 번에 1개의 트리만을 쿼리할 수 있으며 해당 트리는 반드시 연속이어야 합니다.