CTEs（共通テーブル式）の使用¶

無限ループの可能性¶

再帰的 CTE を誤って作成すると、無限ループが発生する可能性があります。このような場合、クエリは、クエリが成功するか、クエリがタイムアウトするか（例: STATEMENT_TIMEOUT_IN_SECONDS パラメーターで指定された秒数を超過）、または クエリをキャンセルする まで実行を続けます。

無限ループが発生する可能性のある仕組みと、この問題を回避する方法のガイドラインについては、 再帰的 CTE のトラブルシューティング をご参照ください。

連続していない階層¶

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

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

順不同の出力¶

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

 1)    WITH RECURSIVE managers
 2)          (indent, employee_ID, manager_ID, employee_title)
 3)        AS
 4)          (
 5)
 6)            SELECT '' AS indent, employee_ID, manager_ID, title AS employee_title
 7)              FROM employees
 8)              WHERE title = 'President'
 9)
10)            UNION ALL
11)
12)           SELECT indent || '--- ',
13)               employees.employee_ID, employees.manager_ID, employees.title
14)             FROM employees JOIN managers
15)               ON employees.manager_ID = managers.employee_ID
16)         )
17)
18)     SELECT indent || employee_title AS Title, employee_ID, manager_ID
19)       FROM managers
20)     ;

クエリには次のセクションが含まれます。

• 行2には、「ビュー」（CTE）の列名が含まれています。

• 行4-16にはCTEが含まれています。

• 行6-8には、CTEのアンカー句が含まれています。

• 行12-15には、CTEの再帰句が含まれています。

• 行18～19には、 CTE をビューとして使用するメインの SELECT が含まれています。このSELECTは次を参照します。

  • 1行で定義されているCTE名（managers）。

  • 2行で定義されたCTEの列（indent、 employee_id など）。

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

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

• 再帰句の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) || ' '
  $$
  ;

Copy

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

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

Copy

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

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

Copy

次のクエリは、階層の前の（より高い）レベルのフィールドを参照する方法を示しています。 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          |
+----------------------------+-------------+------------+----------------------------+

Copy

原因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;

Copy

解決策1.2

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

原因2：無限ループ¶

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

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

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

Copy

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

ステップ1

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

CTE:

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

Copy

ステップ2

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

CTE:

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

Copy

table1:

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

Copy

再帰句の結合の結果：

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

Copy

投影：

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

Copy

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

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

Copy

ステップ3

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

CTE:

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

Copy

table1:

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

Copy

再帰句の結合の結果：

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

Copy

投影：

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

Copy

クエリの結果（次の反復の開始時の CTE の内容）：

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

Copy

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

• employee_id は 1 です。

• manager_id は 2 です。

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

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

解決策2

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

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