階層データのクエリ

このトピックでは、以下を使用して階層データを格納およびクエリする方法について説明します。

  • JOINs

  • 再帰的CTEs（一般的なテーブル式）

  • CONNECT BY

こちらもご参照ください。

CONNECT BY 、 WITHコマンドの再帰的な CTE 部分 、 CTEs（共通テーブル式）の使用 、 表形式 SQL UDFs （UDTFs）

このトピックの内容:

階層データの格納

多くのタイプのデータは、ツリーなどの階層として最適に表示されます。

例えば、従業員は通常、階層で編成され、会社の社長が階層の最上位にいます。

階層の別の例は、「部品展開」です。例えば、車にはエンジンが含まれ、エンジンには燃料ポンプが含まれ、燃料ポンプにはホースが含まれているという具合です。

階層データは、次に格納できます。

  • テーブルの階層。

  • 階層を表す1つ（または複数）の列を持つ単一のテーブル（例えば、各従業員の直属の上司を示す）。

両方の手法を以下で説明します。

注釈

このトピックでは、 構造化 データとして保存された階層データに焦点を当てます。階層データは、半構造化データとして保存することもできます（例: JSON データは ARRAY、 OBJECT、または VARIANT データ型で保存できます）。半構造化データについては、以下をご参照ください。

複数のテーブルにわたる階層データ

リレーショナルデータベースは、多くの場合、異なるテーブルを使用して階層データを格納します。例えば、あるテーブルに「親」データが含まれ、別のテーブルに「子」データが含まれる場合があります。階層全体が事前にわかっている場合、階層内の各レイヤーに1つのテーブルを作成できます。

例えば、従業員情報と管理者情報を格納する人事データベースを考えてみましょう。会社が小さい場合、マネージャーが1人と従業員が2人のように、レベルが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

階層のすべてのレベルが同じデータ（この例では従業員 ID、役職など）を格納する場合、1つのテーブルにデータの階層全体を格納するのが最適です。異なるレベルのデータが同じレコード構造に適合しない場合、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には、レベル数が事前にわからない階層データをクエリする2つの方法があります。

  • 再帰的 CTEs（共通テーブル式）。

  • CONNECT BY 句。

再帰的 CTE を使用すると、それ自体を参照できる WITH 句を作成できます。これにより、階層の各レベルを反復処理し、結果を蓄積できます。

CONNECT BY 句を使用すると、階層を一度に1レベルずつ処理するタイプの JOIN 操作を作成でき、各レベルで前のレベルのデータを参照できます。

詳細については、以下をご参照ください：

自己結合、再帰的 CTE、および CONNECT BY の違い

CONNECT BY は、自己結合のみを許可します。再帰的 CTEs はより柔軟性があり、テーブルを1つ以上の他のテーブルに結合できます。

CONNECT BY 句には、再帰的 CTE のほとんどの機能が備わります。ただし、再帰的 CTE は、 CONNECT BY では不可能なことを実行できます。

たとえば、再帰的 CTE の例を見ると、クエリの1つが出力をインデントし、各「子」が対応する「親」の下に表示されるように出力を並べ替えていることがわかります。並び替えは、上から現在のレベルまでの IDs のチェーンを含むソートキーを作成することによって行われます。マネージャー/従業員の例では、チェーンには社長の ID が含まれ、その後に副社長の ID などが続きます。このソートキーは、横向きツリーに似た方法で行をグループ化します。「START WITH」句ではコードがsort_keyなどの（テーブル自体の列を超える）追加の列を指定できないため、 CONNECT BY 構文はこれをサポートしません。以下の2つのコードスニペットを比較してください。

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つのツリーのみであり、そのツリーは連続している必要があります。

言語: 日本語