CTEs（共通テーブル式）の使用¶
- こちらもご参照ください。
このトピックの内容:
CTEとは何ですか？¶
CTE（共通テーブル式）は、 WITH 句で定義された名前付きサブクエリです。CTE は、 CTE 定義するステートメントで使用する仮の ビュー と考えることができます。CTE は、仮のビューの名前、オプションの列名のリスト、およびクエリ式（つまり、 SELECT ステートメント）を定義します。クエリ式の結果は事実上テーブルになります。そのテーブルの各列は、列名の（オプションの）リスト内の列に対応しています。
次のコードは、 CTE を使用するクエリの例です。
WITH
my_cte (cte_col_1, cte_col_2) AS (
SELECT col_1, col_2
FROM ...
)
SELECT ... FROM my_cte;
上の例では、 CTE は my_cte (cte_col_1, cte_col_2) AS ( を含む行で始まり、 ) を含む行で終わります。
以下に一致する 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 ...;
- 条件:
anchor_clause階層の最上部を表す初期行または行セットを選択します。例えば、会社のすべての従業員を表示する場合、アンカー句は会社の社長を選択します。
アンカー句は SELECT ステートメントであり、サポートされている SQL 構造を含めることができます。アンカー句は
cte_nameを参照できません。recursive_clause前のレイヤーに基づいて階層の次のレイヤーを選択します。最初の反復では、前のレイヤーはアンカー句の結果セットです。後続の反復では、前の層が最後に完了した反復です。
recursive_clauseは、 SELECT ステートメントです。ただし、ステートメントは投影、結合、およびフィルターに制限されています。また、次はステートメントで 許可されていません 。集計またはウィンドウ関数。
GROUP BY、ORDER BY、LIMIT、またはDISTINCT。
再帰句は、通常のテーブルまたはビューのように
cte_nameを参照できます。
構文の詳細については、 WITH を参照ください。
論理的に、再帰的 CTE は次のように評価されます。
anchor_clauseが評価され、その結果が最終結果セットと作業テーブルの両方に書き込まれます。cte_nameは事実上、その作業テーブルのエイリアスです。つまり、cte_nameがその作業テーブルから読み取ることを参照するクエリです。作業テーブルは空ではありませんが、
cte_nameが参照されている場合は常に、作業テーブルの現在の内容を使用して、recursive_clauseが評価されます。recursive_clauseの結果は、最終結果セットと仮テーブルの両方に書き込まれます。作業テーブルは、一時テーブルの内容によって上書きされます。
事実上、前の反復の出力は cte_name という名前の作業テーブルに格納され、そのテーブルは次にある反復への入力の1つになります。作業テーブルには、最新の反復の結果のみが含まれます。これまでのすべての反復からの累積結果は、他の場所に格納されます。
最後の反復の後、 cte_name を参照することにより、メインの SELECT ステートメントで累積結果を利用できます。
再帰的 CTE の考慮事項¶
無限ループの可能性¶
再帰的 CTE を誤って作成すると、無限ループが発生する可能性があります。このような場合、クエリは、クエリが成功するか、クエリがタイムアウトするか（例: STATEMENT_TIMEOUT_IN_SECONDS パラメーターで指定された秒数を超過）、または クエリをキャンセルする まで実行を続けます。
無限ループが発生する可能性のある仕組みと、この問題を回避する方法のガイドラインについては、 再帰的 CTE のトラブルシューティング をご参照ください。
連続していない階層¶
このトピックでは、階層、および再帰的 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番目の例では、インデントを使用し、各マネージャーの従業員をそのマネージャーのすぐ下に表示しています。
順不同の出力¶
最初の例を次に示します。
WITH RECURSIVE managers -- Line 1
(indent, employee_ID, manager_ID, employee_title) -- Line 2
AS -- Line 3
( -- Line 4
-- Line 5
SELECT '' AS indent, -- Line 6
employee_ID, -- Line 7
manager_ID, -- Line 8
title AS employee_title -- Line 9
FROM employees -- Line 10
WHERE title = 'President' -- Line 11
-- Line 12
UNION ALL -- Line 13
-- Line 14
SELECT indent || '--- ', -- Line 15
employees.employee_ID, -- Line 16
employees.manager_ID, -- Line 17
employees.title -- Line 18
FROM employees JOIN managers -- Line 19
ON employees.manager_ID = managers.employee_ID -- Line 20
) -- Line 21
-- Line 22
SELECT indent || employee_title AS Title, -- Line 23
employee_ID, -- Line 24
manager_ID -- Line 25
FROM managers; -- Line 26
クエリには次のセクションが含まれます。
行2には、「ビュー」（CTE）の列名が含まれています。
行4-21には CTE が含まれています。
行6-11には、 CTE のアンカー句が含まれています。
行15-21には、 CTE の再帰句が含まれています。
行23-26には、 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) || ' ' $$ ;
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.3:
階層データを想定する再帰的な 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 ...
この例では、再帰句は、現在/子の値（employee_id）を持つ列に親値（manager_id）を渡します。親は次の反復で「現在の」値として表示され、「現在の」値として次の世代に再び渡されるため、クエリがレベルを下って進行することはありません。毎回同じレベルの処理を続けます。
- ステップ1:
アンカー句が値
employee_id = 1およびmanager_id = NULLを選択するとします。CTE:
employee_ID manager_ID ----------- --------- 1 NULL
- ステップ2:
employee_id = 2、manager_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_idは1です。manager_idは2です。
したがって、次の反復中の結合の結果は、現在の反復中の結合の結果と同じになり、クエリは無限ループになります。
無限ループを作成した場合：
- 解決策2:
再帰句が正しい変数を正しい順序で渡すようにしてください。
また、再帰句の JOIN 条件が正しいことを確認してください。通常の場合、「現在の」行の親は、親行の子/現在の値に結合される必要があります。