カテゴリ:

クエリ構文

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> ]
           ...
  ...
列リスト

これは通常、 SELECT ステートメントの射影句の規則に従います。

レベル式

CONNECT BY クエリはいくつかの擬似列を許可します。これらの疑似列の1つは LEVEL で、これは階層の現在のレベルを示します(レベル1は階層の最上位を表します)。クエリの射影句では、列として LEVEL を使用できます。

情報源

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

述語

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

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

colN識別子

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

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

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

キーワード PRIOR は、前の(上位/親)レベルから値を取得する必要があることを示します。

この例では、現在の従業員の manager_ID は前のレベルの employee_ID と一致する必要があります。

CONNECT BY 句には、次のような式を複数含めることができます。

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

次のような各式には、キーワード PRIOR が1つだけ含まれている必要があります。

CONNECT BY <col_1_identifier> = <col_2_identifier>

例:

CONNECT BY <col_1_identifier> = PRIOR <col_2_identifier>

または

CONNECT BY PRIOR <col_1_identifier> = <col_2_identifier>

使用上の注意

  • CONNECT BY 句は、テーブルを別のテーブルではなく常に自身に結合します。

  • デフォルトでは、クエリの無限ループを防ぐために、反復回数に上限があります。

  • プロジェクション句内のいくつかのバリエーションは有効です。構文は 列リスト の後に レベル式 が発生することを示していますが、レベル式は任意の順序で発生させることができます。

  • キーワード PRIOR は、各 CONNECT BY 式で正確に1回出現する必要があります。 PRIOR は、式の左側または右側のいずれかで使用できますが、両方で使用することはできません。

  • 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 句はデータを処理するのに必要な回数だけ繰り返すことができますが、Snowflakeは現在、クエリが不適切に構築された場合にクエリが無期限に実行されるのを防ぐために反復回数を100に制限しています。

この例では、CONNECT BY を使用して、従業員情報のテーブルに管理階層を表示します。表とデータを以下に示します。

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);
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);

クエリと出力は次のとおりです。

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   |
+-------------+------------+----------------------------+

この例では、 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   |
+----------------------------------------------------------------+-------------+------------+----------------------------+

この例では、 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  |
+-------------+------------+----------------------------+------------+

この例では、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
    ;

クエリと出力は次のとおりです。

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         |
+----------------+----------+--------------+---------------------+----------------------------+