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-recursiveterm)、そして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
再帰的問い合わせを扱う場合、問い合わせの再帰部分が最終的にはタプルを返さないようにすることが重要です。
そうしなければ、問い合わせが永久にループしてしまうからです。
UNION ALL
の替わりにUNION
を使用することで、重複する前回の出力行が廃棄され、これを実現できることもあるでしょう。
しかし、各周期が完全に重複している行を含まないこともよくあり、そのような場合は、1つまたは少数のフィールドを検査して、同じ場所に既に到達したかどうかを調べる必要があるかもしれません。
このような状態を取り扱う標準手法は、既に巡回された値の配列を計算することです。
例えば、link
フィールドを使ってテーブルgraph
を検索する以下の問い合わせを考えて見ます。
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 ) SELECT * FROM search_graph;
この問い合わせはlink
関係が循環を含んでいればループします。
「depth」出力を要求しているので、UNION ALL
をUNION
に変えるだけでは、ループを取り除くことができません。
その代わり、linkの特定の経路をたどっている間に、同じ行に到達したかどうかを認識する必要があります。
このループしやすい問い合わせに、path
とcycle
の2列を加えます。
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY[g.id], false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle ) SELECT * FROM search_graph;
巡回防止の他に、特定行に到達する際に選ばれた「path」 それ自体を表示するため、配列値はしばしば利用価値があります。
循環を認識するために検査するために必要なフィールドが複数存在する一般的な状況では、行の配列を使用します。
例えば、フィールドf1
とf2
を比較する必要があるときは次のようにします。
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY[ROW(g.f1, g.f2)], false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || ROW(g.f1, g.f2), ROW(g.f1, g.f2) = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle ) SELECT * FROM search_graph;
循環を認識するために検査するために必要なフィールドが1つだけである一般的な場合では、ROW()
構文を削除します。
これで、複合型配列ではなく単純配列で済むので、効率も上がります。
再帰的問い合わせ評価アルゴリズムは、幅優先探索順でその出力を作成します。
このようにして作られた「path」列を外側問い合わせでORDER BY
すれば、深さ優先探索順の結果の表示が可能です。
ループするかどうか確信が持てない問い合わせをテストする有益な秘訣として、親問い合わせに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
問い合わせは一般的に、親問い合わせが後で破棄するであろう行を抑制せずに、書かれた通りに評価されます。
(しかし、上で述べたように、問い合わせの参照が限定された数の行のみを要求する場合、評価は早期に停止します。)
上の例では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
ルールを持ってはなりません。