카테고리:: 쿼리 구문

CONNECT BY¶

테이블의 계층적 데이터를 처리하기 위해 테이블 자체를 조인합니다. FROM 절의 CONNECT BY 하위 절은 데이터 처리를 반복합니다.

예를 들어, 구성 요소와 해당 구성 요소의 하위 구성 요소를 재귀적으로 나열하는 《부품 전개》를 표시하는 쿼리를 만들 수 있습니다.

CONNECT BY용 Snowflake 구문은 대부분 Oracle 구문과 호환됩니다.

참고 항목:: WITH

구문¶

CONNECT BY가 있는 문의 일반적인 형식은 다음과 유사합니다(순서에 따라 일부 변형이 허용되지만, 표시되지는 않음).

SELECT <column_list> [ , <level_expression> ]
  FROM <data_source>
    START WITH <predicate>
    CONNECT BY [ PRIOR ] <col1_identifier> = [ PRIOR ] <col2_identifier>
           [ , [ PRIOR ] <col3_identifier> = [ PRIOR ] <col4_identifier> ]
           ...
  ...

Copy

column_list

이는 일반적으로 SELECT 문의 프로젝션 절에 대한 규칙을 따릅니다.

level_expression

CONNECT BY 쿼리는 일부 의사 열을 허용합니다. 이러한 의사 열 중 하나는 계층 구조의 현재 수준을 나타내는 LEVEL 입니다(수준 1은 계층 구조의 최상위를 나타냄). 쿼리의 프로젝션 절은 LEVEL을 열로 사용할 수 있습니다.

data_source

데이터 소스는 일반적으로 테이블이지만 뷰, UDTF 등 테이블과 유사한 다른 데이터 소스일 수 있습니다.

predicate

조건자는 계층 구조의 첫 번째 《수준》을 선택하는 식입니다(예: 회사 사장 또는 부품 전개의 최상위 구성 요소). 조건자는 WHERE 절과 유사하되 WHERE 키워드가 없어야 합니다.

조건자 예는 이 항목에 있는 예 섹션을 참조하십시오.

colN_identifier

CONNECT BY 절에는 조인에 사용된 것과 유사한 하나 이상의 식이 포함되어야 합니다. 특히, 테이블의 《현재》 수준에 있는 열은 테이블의 《이전》(상위) 수준에 있는 열을 참조해야 합니다.

예를 들어, 관리자/직원 계층 구조에서 절은 다음과 유사할 수 있습니다.

... CONNECT BY manager_ID = PRIOR employee_ID ...

Copy

PRIOR 키워드는 이전(상위/부모) 수준에서 값을 가져와야 함을 나타냅니다.

이 예에서 현재 직원의 manager_ID 는 이전 수준의 employee_ID 와 일치해야 합니다.

CONNECT BY 절에는 다음과 같이 식이 둘 이상 포함될 수 있습니다.

... CONNECT BY y = PRIOR x AND b = PRIOR a ...

Copy

다음과 유사한 각 식에는 PRIOR 키워드가 정확히 한 번 발생해야 합니다.

CONNECT BY <col_1_identifier> = <col_2_identifier>
Copy
PRIOR 키워드는 = 기호의 왼쪽이나 오른쪽에 있을 수 있습니다. 예:
CONNECT BY <col_1_identifier> = PRIOR <col_2_identifier>
Copy
또는
CONNECT BY PRIOR <col_1_identifier> = <col_2_identifier>
Copy

사용법 노트¶

항상 CONNECT BY 절은 다른 테이블이 아닌 자체에 테이블을 조인합니다.
프로젝션 절 내의 일부 변형은 유효합니다. 구문에서는 level_expression 다음에 발생하는 column_list 를 보여주지만, 수준 식은 어떤 순서로든 발생할 수 있습니다.
PRIOR 키워드는 각 CONNECT BY 식에서 정확히 한 번 발생해야 합니다. PRIOR 는 식의 좌변이나 우변에 발생할 수 있지만, 둘 다에는 발생할 수 없습니다.
CONNECT BY를 포함한 쿼리는 다음 중 하나 또는 둘 다를 포함할 수도 있습니다.
- WHERE 절의 필터.
- JOINs (FROM 절 또는 WHERE 절에 있을 수 있음).
실행 순서는 다음과 같습니다.
1. JOINs(WHERE 절 또는 FROM 절에 지정되었는지 여부에 관계없이).
2. CONNECT BY
3. 필터(JOIN 필터 제외).
예를 들어, WHERE 절의 필터는 CONNECT BY 뒤에 처리됩니다.
CONNECT BY의 Snowflake 구현은 대부분 Oracle 구현과 호환됩니다. 그러나 Snowflake는 다음을 지원하지 않습니다.
- NOCYCLE
- CONNECT_BY_ISCYCLE
- CONNECT_BY_ISLEAF

Snowflake는 CONNECT BY 절과 함께 사용할 때 SYS_CONNECT_BY_PATH 함수를 지원합니다. SYS_CONNECT_BY_PATH 는 루트에서 현재 요소까지의 경로가 포함된 문자열을 반환합니다. 아래의 예 섹션에 예가 포함되어 있습니다.

Snowflake는 CONNECT BY 절과 함께 사용할 때 CONNECT_BY_ROOT 연산자를 지원합니다. CONNECT_BY_ROOT 연산자를 사용하는 경우, 루트 수준이 현재 수준의 직계 부모가 아니더라도 현재 수준에서 계층 구조의 루트 수준 정보를 사용할 수 있습니다. 아래의 예 섹션에 예가 포함되어 있습니다.
CONNECT BY 절은 데이터를 처리하기 위해 필요한 만큼 반복할 수 있습니다. 쿼리를 잘못 구성하면 무한 루프가 발생할 수 있습니다. 이러한 경우에는 쿼리가 성공하거나 쿼리 시간이 초과되거나(예: STATEMENT_TIMEOUT_IN_SECONDS 매개 변수로 지정된 시간(초) 초과) 쿼리를 취소 할 때까지 쿼리가 계속 실행됩니다.

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

예¶

이 예에서는 CONNECT BY를 사용하여 직원 정보 테이블에 관리 계층 구조를 표시합니다. 테이블과 데이터는 다음과 같습니다.

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

쿼리 및 출력은 다음과 같습니다.

SELECT employee_ID, manager_ID, title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+-------------+------------+----------------------------+
| EMPLOYEE_ID | MANAGER_ID | TITLE                      |
|-------------+------------+----------------------------|
|           1 |       NULL | President                  |
|          10 |          1 | Vice President Engineering |
|          20 |          1 | Vice President HR          |
|         100 |         10 | Programmer                 |
|         101 |         10 | QA Engineer                |
|         200 |         20 | Health Insurance Analyst   |
+-------------+------------+----------------------------+

Copy

이 예에서는 SYS_CONNECT_BY_PATH 함수를 사용하여 사장부터 현재 직원까지의 계층 구조를 표시합니다.

SELECT SYS_CONNECT_BY_PATH(title, ' -> '), employee_ID, manager_ID, title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+----------------------------------------------------------------+-------------+------------+----------------------------+
| SYS_CONNECT_BY_PATH(TITLE, ' -> ')                             | EMPLOYEE_ID | MANAGER_ID | TITLE                      |
|----------------------------------------------------------------+-------------+------------+----------------------------|
|  -> President                                                  |           1 |       NULL | President                  |
|  -> President -> Vice President Engineering                    |          10 |          1 | Vice President Engineering |
|  -> President -> Vice President HR                             |          20 |          1 | Vice President HR          |
|  -> President -> Vice President Engineering -> Programmer      |         100 |         10 | Programmer                 |
|  -> President -> Vice President Engineering -> QA Engineer     |         101 |         10 | QA Engineer                |
|  -> President -> Vice President HR -> Health Insurance Analyst |         200 |         20 | Health Insurance Analyst   |
+----------------------------------------------------------------+-------------+------------+----------------------------+

Copy

이 예에서는 CONNECT_BY_ROOT 키워드를 사용하여 출력의 각 행에 계층 구조의 맨 위에 있는 정보를 표시합니다.

SELECT 
employee_ID, manager_ID, title,
CONNECT_BY_ROOT title AS root_title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+-------------+------------+----------------------------+------------+
| EMPLOYEE_ID | MANAGER_ID | TITLE                      | ROOT_TITLE |
|-------------+------------+----------------------------+------------|
|           1 |       NULL | President                  | President  |
|          10 |          1 | Vice President Engineering | President  |
|          20 |          1 | Vice President HR          | President  |
|         100 |         10 | Programmer                 | President  |
|         101 |         10 | QA Engineer                | President  |
|         200 |         20 | Health Insurance Analyst   | President  |
+-------------+------------+----------------------------+------------+

Copy

이 예에서는 CONNECT BY를 사용하여 《부품 전개》를 표시합니다.

데이터는 다음과 같습니다.

-- The components of a car.
CREATE TABLE components (
    description VARCHAR,
    quantity INTEGER,
    component_ID INTEGER,
    parent_component_ID INTEGER
    );

INSERT INTO components (description, quantity, component_ID, parent_component_ID) VALUES
    ('car', 1, 1, 0),
       ('wheel', 4, 11, 1),
          ('tire', 1, 111, 11),
          ('#112 bolt', 5, 112, 11),
          ('brake', 1, 113, 11),
             ('brake pad', 1, 1131, 113),
       ('engine', 1, 12, 1),
          ('piston', 4, 121, 12),
          ('cylinder block', 1, 122, 12),
          ('#112 bolt', 16, 112, 12)   -- Can use same type of bolt in multiple places
    ;

Copy

쿼리 및 출력은 다음과 같습니다.

SELECT
  description,
  quantity,
  component_id, 
  parent_component_ID,
  SYS_CONNECT_BY_PATH(component_ID, ' -> ') AS path
  FROM components
    START WITH component_ID = 1
    CONNECT BY 
      parent_component_ID = PRIOR component_ID
  ORDER BY path
  ;
+----------------+----------+--------------+---------------------+----------------------------+
| DESCRIPTION    | QUANTITY | COMPONENT_ID | PARENT_COMPONENT_ID | PATH                       |
|----------------+----------+--------------+---------------------+----------------------------|
| car            |        1 |            1 |                   0 |  -> 1                      |
| wheel          |        4 |           11 |                   1 |  -> 1 -> 11                |
| tire           |        1 |          111 |                  11 |  -> 1 -> 11 -> 111         |
| #112 bolt      |        5 |          112 |                  11 |  -> 1 -> 11 -> 112         |
| brake          |        1 |          113 |                  11 |  -> 1 -> 11 -> 113         |
| brake pad      |        1 |         1131 |                 113 |  -> 1 -> 11 -> 113 -> 1131 |
| engine         |        1 |           12 |                   1 |  -> 1 -> 12                |
| #112 bolt      |       16 |          112 |                  12 |  -> 1 -> 12 -> 112         |
| piston         |        4 |          121 |                  12 |  -> 1 -> 12 -> 121         |
| cylinder block |        1 |          122 |                  12 |  -> 1 -> 12 -> 122         |
+----------------+----------+--------------+---------------------+----------------------------+

Copy