WITH
問い合わせ(共通テーブル式)
WITH
は、より大規模な問い合わせで使用される補助文を記述する方法を提供します。
これらの文は共通テーブル式(Common Table Expressions)またはCTEとよく呼ばれるものであり、1つの問い合わせのために存在する一時テーブルを定義すると考えることができます。
WITH
句内の補助文はそれぞれSELECT
、INSERT
、UPDATE
またはDELETE
を取ることができます。
そしてWITH
句自身は、これもSELECT
、INSERT
、UPDATE
またはDELETE
を取ることができる主文に付与されます。
WITH
内のSELECT
WITH
内のSELECT
の基本的な価値は、複雑な問い合わせをより単純な部品に分解することです。
以下に例を示します。
WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) ) SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product;
これは販売トップの地域(region)のみから製品ごとの売上高を表示します。
WITH
句は、regional_sales
、top_regions
という名前の2つの補助文を定義します。
ここで、regional_sales
の出力はtop_regions
内で使用され、top_regions
はSELECT
主問い合わせで使用されます。
この例は WITH
なしでも記述できますが、二階層の入れ子の副SELECT
を必要とします。この方法に従うほうが多少扱いやすいです。
オプションのRECURSIVE
修飾子は、WITH
を、単に構文上の利便性の高めるだけでなく標準的なSQLでは不可能な機能を実現させます。
RECURSIVE
を使用すれば、WITH
問い合わせが行った自己の結果を参照できるようになります。1から100までの数を合計する非常に単純な問い合わせは以下のようなものです。
WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t;
再帰的WITH
問い合わせの汎用形式は常に、非再帰的表現(non-recursive term)、そしてUNION
(またはUNION ALL
)、そして再帰的表現(recursive term)です。
再帰的表現だけが、その問い合わせ自身の出力への参照を含むことができます。
このような問い合わせは以下のように実行されます。
再帰的問い合わせの評価
非再帰的表現を評価します。
UNION
(ただしUNION ALL
は除きます)では、重複行を廃棄します。
その再帰的問い合わせの結果の残っているすべての行を盛り込み、同時にそれらを一時作業テーブルに置きます。
作業テーブルが空でないのであれば以下の手順を繰り返します。
再帰自己参照を作業テーブルの実行中の内容で置換し、再帰的表現を評価します。
UNION
(ただしUNION ALL
は除きます)に対し、重複行と前の結果行と重複する行を破棄します。
その再帰的問い合わせの結果の残っているすべての行を盛り込み、同時にそれらを一時中間テーブルに置きます。
中間テーブルの内容で作業テーブルの内容を差し替え、中間テーブルを空にします。
厳密には、この手順は反復(iteration)であって再帰(recursion)ではありませんが、RECURSIVE
はSQL標準化委員会で選ばれた用語です。
上記の例で、作業テーブルはそれぞれの手順での単なる単一行で、引き続く作業で1から100までの値を獲得します。
100番目の作業で、WHERE
句による出力が無くなり、問い合わせが終了します。
再帰的問い合わせは階層的、またはツリー構造データに対処するため一般的に使用されます。 実用的な例は、直接使用する部品を表すテーブル1つのみが与えられ、そこから製品すべての直接・間接部品を見つける次の問い合わせです。
WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) SELECT sub_part, SUM(quantity) as total_quantity FROM included_parts GROUP BY sub_part
再帰的問い合わせを使用してツリーの巡回順を計算する場合、深さ優先または幅優先のいずれかの順序で結果を順序付ける必要がある場合があります。 これは、他のデータ列と並んで順序付け列を計算し、それを使用して最後に結果を並べ替えることで実行できます。 これは、問合せ評価が行を訪問する順序を実際に制御するものではなく、常にSQL実装に依存することに注意してください。 このアプローチは、結果を後で順序付けるための便利な方法を提供するにすぎません。
深さ優先順序を作成するには、結果行ごとに、これまでに訪れた行の配列を計算します。
例えば、link
フィールドを使用してテーブルtree
を検索する次の問合せを考えてみます。
WITH RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data FROM tree t UNION ALL SELECT t.id, t.link, t.data FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree;
深さ優先の順序付け情報を追加するには、次のように記述します。
WITH RECURSIVE search_tree(id, link, data, path) AS ( SELECT t.id, t.link, t.data, ARRAY[t.id] FROM tree t UNION ALL SELECT t.id, t.link, t.data, path || t.id FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY path;
行を識別するために複数のフィールドを使用する必要がある一般的な場合は、行の配列を使用します。
たとえば、フィールドf1
とf2
を追跡する必要がある場合は、次のようにします。
WITH RECURSIVE search_tree(id, link, data, path) AS ( SELECT t.id, t.link, t.data, ARRAY[ROW(t.f1, t.f2)] FROM tree t UNION ALL SELECT t.id, t.link, t.data, path || ROW(t.f1, t.f2) FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY path;
追跡する必要があるフィールドが1つだけである一般的な場合では、ROW()
構文を削除します。
これで、複合型配列ではなく単純配列で済むので、効率も上がります。
幅優先順序を作成するには、検索の深さを追跡する列を追加します。次に例を示します。
WITH RECURSIVE search_tree(id, link, data, depth) AS ( SELECT t.id, t.link, t.data, 0 FROM tree t UNION ALL SELECT t.id, t.link, t.data, depth + 1 FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY depth;
安定した並べ替えを行うには、データ列を2次並べ替え列として追加します。
再帰的問い合わせ評価アルゴリズムは、幅優先の検索順で出力を生成します。 しかし、これは実装の詳細であり、これに頼るのはおそらく不健全です。 各レベル内の行の順序は確かに未定義であるため、いかなる場合でも明示的な順序付けが望まれるかもしれません。
深さ優先または幅優先の並べ替え列を計算するための組み込み構文があります。 例えば、
WITH RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data FROM tree t UNION ALL SELECT t.id, t.link, t.data FROM tree t, search_tree st WHERE t.id = st.link ) SEARCH DEPTH FIRST BY id SET ordercol SELECT * FROM search_tree ORDER BY ordercol; WITH RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data FROM tree t UNION ALL SELECT t.id, t.link, t.data FROM tree t, search_tree st WHERE t.id = st.link ) SEARCH BREADTH FIRST BY id SET ordercol SELECT * FROM search_tree ORDER BY ordercol;
この構文は、上記の手書きの形式に似たものに内部的に拡張されています。
SEARCH
句は、深さ優先または幅優先のどちらの検索が必要か、並べ替えのために追跡する列のリスト、並べ替えに使用できる結果データを含む列名を指定します。
この列は、CTEの出力行に暗黙的に追加されます。
再帰的問い合わせを扱う場合、問い合わせの再帰部分が最終的にはタプルを返さないようにすることが重要です。
そうしなければ、問い合わせが永久にループしてしまうからです。
UNION ALL
の替わりにUNION
を使用することで、重複する前回の出力行が廃棄され、これを実現できることもあるでしょう。
しかし、各周期が完全に重複している行を含まないこともよくあり、そのような場合は、1つまたは少数のフィールドを検査して、同じ場所に既に到達したかどうかを調べる必要があるかもしれません。
このような状態を取り扱う標準手法は、既に巡回された値の配列を計算することです。
例えば、link
フィールドを使ってテーブルgraph
を検索する以下の問い合わせを考えて見ます。
WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 0 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link ) SELECT * FROM search_graph;
この問い合わせはlink
関係が循環を含んでいればループします。
「depth」出力を要求しているので、UNION ALL
をUNION
に変えるだけでは、ループを取り除くことができません。
その代わり、linkの特定の経路をたどっている間に、同じ行に到達したかどうかを認識する必要があります。
このループしやすい問い合わせに、path
とcycle
の2列を加えます。
WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS ( SELECT g.id, g.link, g.data, 0, false, ARRAY[g.id] FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, g.id = ANY(path), path || g.id FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT is_cycle ) SELECT * FROM search_graph;
巡回防止の他に、特定行に到達する際に選ばれた「path」 それ自体を表示するため、配列値はしばしば利用価値があります。
循環を認識するために検査するために必要なフィールドが複数存在する一般的な状況では、行の配列を使用します。
例えば、フィールドf1
とf2
を比較する必要があるときは次のようにします。
WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS ( SELECT g.id, g.link, g.data, 0, false, ARRAY[ROW(g.f1, g.f2)] FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, ROW(g.f1, g.f2) = ANY(path), path || ROW(g.f1, g.f2) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT is_cycle ) SELECT * FROM search_graph;
循環を認識するために検査するために必要なフィールドが1つだけである一般的な場合では、ROW()
構文を削除します。
これで、複合型配列ではなく単純配列で済むので、効率も上がります。
サイクル検出を簡略化する組み込み構文があります。 上記のクエリは、次のように記述することもできます。
WITH RECURSIVE search_graph(id, link, data, depth) AS (
SELECT g.id, g.link, g.data, 1
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1
FROM graph g, search_graph sg
WHERE g.id = sg.link
) CYCLE id SET is_cycle USING path
SELECT * FROM search_graph;
また、内部的に上記の形式に書き換えられます。
CYCLE
句は、最初にサイクル検出のために追跡する列のリストを指定し、次にサイクルが検出されたかどうかを示す列名、最後にパスを追跡する別の列の名前を指定します。
サイクル列とパス列は、CTEの出力行に暗黙的に追加されます。
サイクル・パス列は、前のセクションで示した深さ優先順序列と同じ方法で計算されます。
問い合わせにはSEARCH
句とCYCLE
句の両方を含めることができますが、深さ優先検索指定とサイクル検出指定では冗長な計算が作成されるため、CYCLE
句を使用してパス列で順序付けるだけの方が効率的です。
幅優先順序が必要な場合は、SEARCH
とCYCLE
の両方を指定すると便利です。
ループするかどうか確信が持てない問い合わせをテストする有益な秘訣として、親問い合わせにLIMIT
を配置します。
例えば、以下の問い合わせはLIMIT
がないと永久にループします。
WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;
これが動作するのは、PostgreSQLの実装が、実際に親問い合わせで取り出されるのと同じ数のWITH
問い合わせの行のみを評価するからです。
この秘訣を実稼動環境で使用することは勧められません。
他のシステムでは異なった動作をする可能性があるからです。
同時に、もし外部問い合わせを再帰的問い合わせの結果を並べ替えたり、またはそれらを他のテーブルと結合するような書き方をした場合、動作しません。
このような場合、外部問い合わせは通常、WITH
問い合わせの出力をとにかくすべて取り込もうとするからです。
有用なWITH
問い合わせの特性は、親問い合わせ、もしくは兄弟WITH
問い合わせによりたとえ1回以上参照されるとしても、通常は親問い合わせ実行で1回だけ評価されることです。
したがって、複数の場所で必要な高価な計算は、冗長作業を防止するためWITH
問い合わせの中に配置することができます。
他にありうる適用としては、望まれない副作用のある関数の多重評価を避けることです。
しかし、反対の見方をすれば、オプティマイザが親クエリから複数参照されるWITH
問い合わせに制約を押し下げることができないということになります。
これは、WITH
問い合わせの出力が1つのみに影響する場合、その出力のすべての使用に影響する可能性があるためです。
複数参照されるWITH
問い合わせは、親問い合わせが後で破棄するであろう行を抑制せずに、書かれた通りに評価されます。
(しかし、上で述べたように、問い合わせの参照が限定された数の行のみを要求する場合、評価は早期に停止します。)
しかし、WITH
問い合わせが非再帰で副作用がない(つまり、揮発性(volatile)の関数を含まないSELECT
である)場合は、親問い合わせに組み込むことができ、2つの問い合わせレベルを同時に最適化できます。
デフォルトでは、親問い合わせがWITH
問い合わせを1回だけ参照する場合にこれが発生しますが、WITH
問い合わせを2回以上参照する場合には発生しません。
この決定を上書きするには、MATERIALIZED
を指定してWITH
問い合わせの個別の計算を強制するか、NOT MATERIALIZED
を指定して親問い合わせにマージするようにします。
後者を選択すると、WITH
問い合わせの計算が重複する危険性がありますが、WITH
問い合わせを使用するたびにWITH
問い合わせのごく一部しか必要としない場合は、全体の節約になります。
これらのルールの簡単な例を次に示します。
WITH w AS ( SELECT * FROM big_table ) SELECT * FROM w WHERE key = 123;
このWITH
問い合わせは組み込まれ、次のものと同じ実行計画を生成します。
SELECT * FROM big_table WHERE key = 123;
特に、key
インデックスがある場合、key = 123
を持つ行のみをフェッチするために使用される可能性があります。
一方で、
WITH w AS ( SELECT * FROM big_table ) SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref WHERE w2.key = 123;
このWITH
問い合わせでは実体化され、big_table
の一時的なコピーが生成されます。このコピーはインデックスのメリットなしに、それ自体に結合されます。
この問い合わせは次のように記述すると、より効率的に実行されます。
WITH w AS NOT MATERIALIZED ( SELECT * FROM big_table ) SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref WHERE w2.key = 123;
親の問い合わせの制限をbig_table
のスキャンに直接適用することが出来ます。
NOT MATERIALIZED
が望ましくない例を次に示します。
WITH w AS ( SELECT key, very_expensive_function(val) as f FROM some_table ) SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;
ここで、WITH
問い合わせを生成すると、very_expensive_function
がテーブルの行毎に1回のみ評価され、2回は評価されないことが保証されます。
上の例ではSELECT
を使用するWITH
のみを示しています。
しかし、同じ方法でINSERT
、UPDATE
、またはDELETE
に対して付与することができます。
それぞれの場合において、これは主コマンド内で参照可能な一時テーブルを実質的に提供します。
WITH
内のデータ変更文
WITH
内でデータ変更文(INSERT
、UPDATE
、DELETE
)を使用することができます。
これにより同じ問い合わせ内で複数の異なる操作を行うことができます。
WITH moved_rows AS ( DELETE FROM products WHERE "date" >= '2010-10-01' AND "date" < '2010-11-01' RETURNING * ) INSERT INTO products_log SELECT * FROM moved_rows;
この問い合わせは実質、products
からproducts_log
に行を移動します。
WITH
内のDELETE
はproducts
から指定した行を削除し、そのRETURNING
句により削除した内容を返します。
その後、主問い合わせはその出力を読み取り、それをproducts_log
に挿入します。
上の例の見事なところは、WITH
句がINSERT
内の副SELECT
ではなく、INSERT
に付与されていることです。
これは、データ更新文は最上位レベルの文に付与されるWITH
句内でのみ許されているため必要です。
しかし、通常のWITH
の可視性規則が適用されますので、副SELECT
からWITH
文の出力を参照することができます。
上の例で示したように、WITH
内のデータ変更文は通常RETURNING
句(6.4を参照)を持ちます。
問い合わせの残りの部分で参照することができる一時テーブルを形成するのは、RETURNING
句の出力の出力であって、データ変更文の対象テーブルではありません。
WITH
内のデータ変更文がRETURNING
句を持たない場合、一時テーブルを形成しませんので、問い合わせの残りの部分で参照することができません。
これにもかかわらずこうした文は実行されます。
特別有用でもない例を以下に示します。
WITH t AS ( DELETE FROM foo ) DELETE FROM bar;
この例はfoo
テーブルとbar
テーブルからすべての行を削除します。
クライアントに報告される影響を受けた行数にはbar
から削除された行のみが含まれます。
データ変更文内の再帰的な自己参照は許されません。
一部の場合において、再帰的なWITH
の出力を参照することで、この制限を回避することができます。
以下に例を示します。
WITH RECURSIVE included_parts(sub_part, part) AS ( SELECT sub_part, part FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) DELETE FROM parts WHERE part IN (SELECT part FROM included_parts);
この問い合わせはある製品の直接的な部品と間接的な部品をすべて削除します。
WITH
内のデータ変更文は正確に1回のみ実行され、主問い合わせがその出力をすべて(実際にはいずれか)を呼び出したかどうかに関係なく、常に完了します。
これが、前節で説明した主問い合わせがその出力を要求した時のみにSELECT
の実行が行われるというWITH
内のSELECT
についての規則と異なることに注意してください。
WITH
内の副文はそれぞれと主問い合わせで同時に実行されます。
したがってWITH
内のデータ変更文を使用する時、指定したデータ変更文が実際に実行される順序は予測できません。
すべての文は同じスナップショット(第13章参照)を用いて実行されます。
このため互いが対象テーブルに行った影響を「見る」ことはできません。これは、行の更新に関する実際の順序が予測できないという影響を軽減し、RETURNING
データが別のWITH
副文と主問い合わせとの間で変更を伝える唯一の手段であることを意味します。
この例を以下に示します。
WITH t AS ( UPDATE products SET price = price * 1.05 RETURNING * ) SELECT * FROM products;
外側のSELECT
はUPDATE
の動作前の元々の価格を返します。
WITH t AS ( UPDATE products SET price = price * 1.05 RETURNING * ) SELECT * FROM t;
一方こちらでは外側のSELECT
は更新されたデータを返します。
単一の文で同じ行を2回更新しようとすることはサポートされていません。
変更のうちの1つだけが行われますが、どれが実行されるかを確実に予測することは簡単ではありません(場合によっては不可能です)。
これはまた、同じ文内ですでに更新された行を削除する場合でも当てはまり、更新のみが実行されます。
したがって一般的には単一の文で1つの行を2回変更しようと試みることを避けなければなりません。
具体的には主文または同レベルの副文で変更される行と同じ行に影響を与えるWITH
副文を記述することは避けてください。
こうした文の影響は予測することはできません。
現状、WITH
内のデータ変更文の対象として使用されるテーブルはすべて、複数の文に展開される条件付きルール、ALSO
ルール、INSTEAD
ルールを持ってはなりません。