カテゴリ：: クエリ構文

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 クエリはいくつかの擬似列を許可します。これらの疑似列の1つは LEVEL で、これは階層の現在のレベルを示します（レベル1は階層の最上位を表します）。クエリの射影句では、列として LEVEL を使用できます。

data_source

通常、データソースはテーブルですが、ビュー、 UDTF など、別のテーブルのようなデータソースにすることもできます。

predicate

述語は、階層の最初の「レベル」を選択する式です（例えば、会社の社長または部品爆発の最上位コンポーネント）。述語は WHERE 句に類似していますが、キーワード WHERE がありません。

述語の例については、例セクション（このトピック内）をご参照ください。

colN_identifier

CONNECT BY 句には、結合で使用されるものと類似した1つ以上の式を含める必要があります。具体的には、テーブルの「現在の」レベルの列は、テーブルの「前の」（より高い）レベルの列を参照する必要があります。

例えば、マネージャー/従業員の階層では、句は次のようになります。

... 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 が1つだけ含まれている必要があります。

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 式で正確に1回出現する必要があります。 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