CTEs(共通テーブル式)の使用

このトピックの内容:

CTEとは何ですか?

CTE (共通テーブル式)は、 WITH 句で定義された名前付きサブクエリです。CTE は、CTE を定義するステートメントで使用するための一時的な ビュー と考えることができます。CTE は、一時ビューの名前、オプションで列名のリスト、およびクエリ式(SELECTステートメント)を定義します。クエリ式の結果は事実上テーブルになります。そのテーブルの各列は、列名の(オプションの)リスト内の列に対応しています。

CTE を使用するクエリの例を次に示します。

with
    my_cte (cte_col_1, cte_col_2) AS (   -- begin CTE
        select col_1, col_2
            from ...
    )                                    -- end CTE
select ... from my_cte;

テーブル、ビュー、またはマテリアライズドビューの名前と一致する CTE 名を選択しないでください。クエリが特定の名前で CTE を定義する場合、 CTE はテーブルなどに優先します。

CTE は、再帰的または非再帰的のいずれかになります。再帰的 CTE は、それ自体を参照する CTE です。再帰的 CTE は、テーブル内の階層データを処理するために必要な回数だけテーブルをそれ自体に結合できます。

CTEs は、モジュール性を高め、メンテナンスを簡素化します。

再帰的 CTEs および階層データ

再帰的 CTEs を使用すると、部品展開(コンポーネント、サブコンポーネント)や管理階層(マネージャー、従業員)などの階層データを処理できます。階層データの詳細、および階層データをクエリする他の方法については、『階層データのクエリ』をご参照ください。

再帰的 CTE を使用すると、階層の数を事前に知らなくても、階層のすべてのレベルに参加できます。

再帰的 CTE 構文の概要

このセクションでは、構文の概要と、構文が再帰の動作とどのように関連するかについて説明します。

WITH [ RECURSIVE ] <cte_name> AS
(
  <anchor_clause> UNION ALL <recursive_clause>
)
SELECT ... FROM ...;
条件:
アンカー句

は、階層の最上部を表す初期行または行セットを選択します。例えば、会社のすべての従業員を表示する場合、アンカー句は会社の社長を選択します。

アンカー句は SELECT ステートメントであり、サポートされている SQL 構造を含めることができます。アンカー句は CTE名 を参照できません。

再帰句

は、前のレイヤーに基づいて階層の次のレイヤーを選択します。最初の反復では、前のレイヤーはアンカー句の結果セットです。後続の反復では、前の層が最後に完了した反復です。

再帰句 は、 SELECT ステートメントです。ただし、ステートメントは投影、結合、およびフィルターに制限されています。また、次はステートメントで 許可されていません

  • 集計またはウィンドウ関数。

  • GROUP BYORDER BYLIMIT、または DISTINCT

再帰句は、通常のテーブルまたはビューのように CTE名 を参照できます。

構文の詳細については、 WITH を参照ください。

論理的に、再帰的 CTE は次のように評価されます。

  1. アンカー句 が評価され、その結果が最終結果セットと作業テーブルの両方に書き込まれます。 CTE名 は事実上、その作業テーブルのエイリアスです。つまり、 CTE名 がその作業テーブルから読み取ることを参照するクエリです。

  2. 作業テーブルは空ではありませんが、

    1. cte_name が参照されている場合は常に、作業テーブルの現在の内容を使用して、 recursive_clause が評価されます。

    2. recursive_clause の結果は、最終結果セットと一時テーブルの両方に書き込まれます。

    3. 作業テーブルは、一時テーブルの内容によって上書きされます。

事実上、前の反復の出力は cte_name という名前の作業テーブルに格納され、そのテーブルは次の反復への入力の1つになります。作業テーブルには、最新の反復の結果のみが含まれます。これまでのすべての反復からの累積結果は、他の場所に格納されます。

最後の反復の後、 CTE名 を参照することにより、メインの SELECT ステートメントで累積結果を利用できます。

再帰的 CTE の考慮事項

無限ループの可能性

理論上、再帰的 CTE を誤って作成すると、無限ループが発生する可能性があります。実際には、Snowflakeでは、再帰句が1つのクエリで実行する反復回数を制限することにより、これを防ぎます。 MAX_RECURSIONS パラメーターは、反復回数を制限します。

アカウントの MAX_RECURSIONS を変更するには、 Snowflakeサポート にお問い合わせください。

連続していない階層

このトピックでは、階層、および再帰的 CTEs による親子関係の使用方法について説明しました。このトピックのすべての例で、階層は連続しています。

連続していない階層の詳細については、 階層データのクエリ をご参照ください。

このセクションには、2つのタイプを対比するために、非再帰的および再帰的 CTEs の例が含まれています。

非再帰的、2レベル、自己結合 CTE

この例では、従業員とマネージャーのテーブルを使用します。

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);

この従業員テーブルの2レベルの自己結合は次のようになります。

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

上記のクエリは、すべての従業員を示しています。レポートには、各マネージャーの従業員がそのマネージャーの近くに表示されます。ただし、レポートには階層が視覚的に表示されません。データを注意深く見ないと、組織内にいくつのレベルがあるのかわかりません。特定のマネージャーに関連付けられている従業員を確認するには、各行を読む必要があります。

次のセクションに示すように、再帰的 CTE はこの階層データを横向きツリーとして表示できます。

インデントされた出力を持つ再帰的な CTE

以下は、再帰的 CTE を使用した2つの例です。

  • 最初の例では、インデントを使用して階層のさまざまなレベルを示しています。この例を単純化するために、コードは特定の順序で行を生成しないようになっています。

  • 2番目の例では、インデントを使用し、各マネージャーの従業員をそのマネージャーのすぐ下に表示しています。

順不同の出力

最初の例を次に示します。

-- This is the WITH clause, of course.
WITH RECURSIVE managers 
      -- Column names for the "view"/CTE
      (indent, employee_ID, manager_ID, employee_title) 
    AS
      -- Common Table Expression
      (

        -- Anchor Clause
        SELECT '' AS indent, employee_ID, manager_ID, title AS employee_title
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT indent || '--- ',
            employees.employee_ID, employees.manager_ID, employees.title
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT indent || employee_title AS Title, employee_ID, manager_ID
    FROM managers
  ;
+----------------------------------+-------------+------------+
| TITLE                            | EMPLOYEE_ID | MANAGER_ID |
|----------------------------------+-------------+------------|
| President                        |           1 |       NULL |
| --- Vice President Engineering   |          10 |          1 |
| --- Vice President HR            |          20 |          1 |
| --- --- Programmer               |         100 |         10 |
| --- --- QA Engineer              |         101 |         10 |
| --- --- Health Insurance Analyst |         200 |         20 |
+----------------------------------+-------------+------------+

CTE には、2つの SELECT ステートメントが含まれています。

  • これらの SELECT ステートメントの最初はアンカー句です。これは1回実行され、階層の最初の(最上位)レベルからの行のセットを提供します。

  • CTE の2番目の SELECT は再帰句です。この句は CTEを参照できます。クエリは反復であり、各反復は前の反復のクエリ結果に基づいていると考えることができます。

マネージャー/従業員の例では、アンカー句が最初の行を発行します。これは、会社の社長を説明する行です。

再帰句の次の反復では、再帰句は、マネージャーが会社の社長であるすべての行を検索します(つまり、すべての副社長を検索します)。3番目の反復では、マネージャーが副社長の1人であるすべての従業員を検索します。反復は、取得されたすべての行が誰も管理していないリーフレベルの従業員の行である反復が行われるまで続きます。このステートメントは、マネージャーがリーフレベルの従業員である従業員を探します(ただし、見つかりません)。その反復は0行を生成し、反復は停止します。

これらの反復を通して、UNION ALL 句は結果を蓄積します。各反復の結果は、前の反復の結果に追加されます。最後の反復が完了すると、蓄積された行( WITH 句で生成された行など)がクエリのメイン SELECT 句で利用可能になります。メイン SELECT は、それらの行をクエリできます。

この特定のクエリ例では、インデントを使用してデータの階層的性質を示しています。出力を見ると、従業員のレベルが低いほど、その従業員のデータがさらにインデントされていることがわかります。

インデントは、 indent という名前の列によって制御されます。インデントは0文字(アンカー句の空の文字列)から始まり、各反復(つまり、階層の各レベル)で4文字(---)ずつ増加します。

当然のことながら、結合を正しく作成し、再帰句で正しい列を選択することが非常に重要です。再帰句の SELECT の列は、アンカー句の列に正しく対応する必要があります。クエリは社長から始まり、副社長を選択してから、副社長の部下にあたる人々などを選択することに注意してください。各反復では、 manager_id フィールドが前の反復で生成された managers.employee_id 値の1つに対応する従業員を探します。

別の言い方をすれば、マネージャーの「ビュー」の従業員 ID は、次のレベルの従業員のマネージャー ID です。従業員 IDs は、各反復中に階層(社長、副社長、上級管理者、後輩管理者など)を下方向に進む必要があります。従業員 IDs が進行しないと、クエリは無限ループをする(同じ manager_ID が異なる反復の managers.employee_ID 列に表示され続ける)、レベルをスキップする、または他の方法で失敗する可能性があります。

順序付けられた出力

前の例には ORDER BY 句がなかったため、各従業員のレコードが適切にインデントされていても、各従業員が必ずしもマネージャーの上に直接表示されるとは限りませんでした。次の例では、正しいインデントを使用して、各マネージャーの従業員がそのマネージャーのすぐ下にある出力を生成します。

クエリの ORDER BY 句は、 sort_key という名前の追加の列を使用します。再帰句が繰り返されると、ソートキーが蓄積されます。ソートキーは、上にあるコマンドチェーン全体(マネージャー、マネージャーの上司など)を含む文字列と考えることができます。その指揮系統の中で最上級の人物(社長)は、ソートキー文字列の先頭にいます。通常、ソートキーは表示されませんが、以下のクエリでは出力にソートキーが含まれているため、出力を理解しやすくなっています。

反復ごとにソートキーの長さを同じ量(同じ文字数)増やす必要があるため、クエリは skey という名前の UDF (ユーザー定義関数)を使用して、次の定義に従い、ソートキーの一貫した長さのセグメントを生成します。

CREATE OR REPLACE FUNCTION skey(ID VARCHAR)
  RETURNS VARCHAR
  AS
  $$
    SUBSTRING('0000' || ID::VARCHAR, -4) || ' '
  $$
  ;

SKEY 関数からの出力の例を次に示します。

SELECT skey(12);
+----------+
| SKEY(12) |
|----------|
| 0012     |
+----------+

これがクエリの最終バージョンです。これにより、各マネージャーの従業員がそのマネージャーのすぐ下に配置され、従業員の「レベル」に基づいてインデントが設定されます。

WITH RECURSIVE managers 
      -- Column list of the "view"
      (indent, employee_ID, manager_ID, employee_title, sort_key) 
    AS 
      -- Common Table Expression
      (
        -- Anchor Clause
        SELECT '' AS indent, 
            employee_ID, manager_ID, title AS employee_title, skey(employee_ID)
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT indent || '--- ',
            employees.employee_ID, employees.manager_ID, employees.title, 
            sort_key || skey(employees.employee_ID)
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT 
         indent || employee_title AS Title, employee_ID, 
         manager_ID, 
         sort_key
    FROM managers
    ORDER BY sort_key
  ;
+----------------------------------+-------------+------------+-----------------+
| TITLE                            | EMPLOYEE_ID | MANAGER_ID | SORT_KEY        |
|----------------------------------+-------------+------------+-----------------|
| President                        |           1 |       NULL | 0001            |
| --- Vice President Engineering   |          10 |          1 | 0001 0010       |
| --- --- Programmer               |         100 |         10 | 0001 0010 0100  |
| --- --- QA Engineer              |         101 |         10 | 0001 0010 0101  |
| --- Vice President HR            |          20 |          1 | 0001 0020       |
| --- --- Health Insurance Analyst |         200 |         20 | 0001 0020 0200  |
+----------------------------------+-------------+------------+-----------------+

次のクエリは、階層の前の(より高い)レベルのフィールドを参照する方法を示しています。 mgr_title 列に特に注意してください。

WITH RECURSIVE managers 
      -- Column names for the "view"/CTE
      (employee_ID, manager_ID, employee_title, mgr_title) 
    AS
      -- Common Table Expression
      (

        -- Anchor Clause
        SELECT employee_ID, manager_ID, title AS employee_title, NULL AS mgr_title
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT 
            employees.employee_ID, employees.manager_ID, employees.title, managers.employee_title AS mgr_title
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT employee_title AS Title, employee_ID, manager_ID, mgr_title
    FROM managers
    ORDER BY manager_id NULLS FIRST, employee_ID
  ;
+----------------------------+-------------+------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_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          |
+----------------------------+-------------+------------+----------------------------+

部品展開

マネージャー/従業員階層は、単一のテーブルに格納し、再帰的 CTE で処理できる唯一の種類の可変深階層ではありません。階層データのもう1つのよくある例が「部品展開」です。この場合、各コンポーネントはサブコンポーネントとともにリストされ、各コンポーネントはサブサブコンポーネントとともにリストされます。

例えば、テーブルに車のコンポーネントなどの階層データが含まれているとします。車には、エンジン、車輪などのコンポーネントも含まれていることもあります。これらのコンポーネントの多くにはサブコンポーネントが含まれています(例えば、エンジンに燃料ポンプが含まれている場合があります)。燃料ポンプには、モーター、チューブなどが含まれている場合があります。再帰的 CTE を使用して、すべてのコンポーネントとそのサブコンポーネントをリストできます。

部品展開を生成するクエリの例については、『WITH』をご参照ください。

再帰的 CTE のトラブルシューティング

再帰的 CTE クエリは、タイムアウトになるか、最大反復制限に達するまで実行される

この問題は、次の2つの異なるシナリオが原因で発生する可能性があります。

  • データ階層にはサイクルがある場合があります。

  • 無限ループを作成した可能性があります。

原因1:循環データ階層

データ階層にサイクルが含まれている場合(つまり、真のツリーではない場合)、次の複製の解決策があります。

解決策1.1

データにサイクルが含まれていない場合は、データを修正します。

解決策1.2

何らかの方法でクエリを制限します(例:出力の行数を制限します)。例:

WITH RECURSIVE t(n) AS
    (
    SELECT 1
    UNION ALL
    SELECT N + 1 FROM t
   )
 SELECT n FROM t LIMIT 10;
解決策1.2

階層データを想定する再帰的な CTE を含むクエリを使用しないでください。

原因2:無限ループ

再帰句 の射影句が「子」(現在の反復)ではなく「親」(前の反復)から値を出力し、次の反復が結合でその値を使用する場合、無限ループが発生する可能性があります。その場合、結合で現在の反復の値を使用する必要があります。

次の擬似コードは、このおおよその例を示しています。

CREATE TABLE employees (employee_ID INT, manager_ID INT, ...);
INSERT INTO employees (employee_ID, manager_ID) VALUES
        (1, NULL),
        (2, 1);

WITH cte_name (employee_ID, manager_ID, ...) AS
  (
     -- Anchor Clause
     SELECT employee_ID, manager_ID FROM table1
     UNION ALL
     SELECT manager_ID, employee_ID   -- <<< WRONG
         FROM table1 JOIN cte_name
           ON table1.manager_ID = cte_name.employee_ID
  )
SELECT ...

この例では、再帰句は、現在/子の値(employee_id)を持つ列に親値(manager_id)を渡します。親は次の反復で「現在の」値として表示され、「現在の」値として次の世代に再び渡されるため、クエリがレベルを下って進行することはありません。毎回同じレベルの処理を続けます。

ステップ1

アンカー句が値 employee_id = 1 および manager_id = NULL を選択するとします。

CTE:

employee_ID  manager_ID
-----------  ---------
      1         NULL
ステップ2

employee_id = 2manager_id = 1、および table1 の再帰句の最初の反復中。

CTE:

employee_ID  manager_ID
-----------  ----------
       1         NULL

table1:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

再帰句の結合の結果:

table1.employee_ID  table1.manager_ID  cte.employee_ID  cte.manager_ID
-----------------   -----------------  ---------------  --------------
 ...
       2                   1                 1                NULL
 ...

投影:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

ただし、投影では employee_id 列と manager_id 列が逆になるため、クエリの実際の出力(したがって、次の反復の開始時の CTE の内容)は次のようになります。

employee_ID  manager_ID
-----------  ----------
 ...
       1         2        -- Because manager and employee IDs reversed
 ...
ステップ3

再帰句の2回目の反復中:

CTE:

employee_ID  manager_ID
-----------  ----------
       1         2

table1:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

再帰句の結合の結果:

table1.employee_ID  table1.manager_ID  cte.employee_ID  cte.manager_ID
-----------------   -----------------  ---------------  --------------
 ...
       2                   1                 1                2
 ...

投影:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

クエリの結果(次の反復の開始時の CTE の内容):

employee_ID  manager_ID
-----------  ----------
 ...
       1         2        -- Because manager and employee IDs reversed
 ...

ここで見るように、2回目の反復の終了時、 CTE の行は反復の開始時と同じです。

  • employee_id1 です。

  • manager_id2 です。

したがって、次の反復中の結合の結果は、現在の反復中の結合の結果と同じになり、クエリは無限ループになります。

無限ループを作成した場合:

解決策2

再帰句が正しい変数を正しい順序で渡すようにしてください。

また、再帰句の JOIN 条件が正しいことを確認してください。通常の場合、「現在の」行の親は、親行の子/現在の値に結合される必要があります。