WITHはより大きなSELECT問い合わせで使用される副問い合わせを記述する方法を提供します。副問い合わせは、その問い合わせのみに存在する一時テーブルを定義するものと考えられます。この特性の1つの使用法は複雑な問い合わせを単純な部品に分解することです。例を示します。
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なしでも記述できますが、二階層の入れ子の副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ではない)に対し、重複行と前の結果行と重複する行を破棄します。その再帰的問い合わせの結果の残っている全ての行を盛り込み、同時にそれらを暫定的中間テーブルに置きます。
中間テーブルの内容で作業テーブルの内容を差し替え、中間テーブルを空にします。
注意: 厳密には、この手順は反復であって再帰ではありませんが、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関係がcycleを含んでいればループします。"depth"出力が必要なので、UNION ALL を UNION に変更しループ化を除去しないようにします。その代わり、特定のlinkのpathをたどっている間、同じ列に到達したかどうかを認識する必要があります。ループしやすい問い合わせに、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"がどんな特定行に到達したかの表示としての公正さからしばしば利用価値があります。
あるcycleを認識するために検査しなくてはならない1つ以上のフィールドが必要とされる一般的な場合、行の配列を使用します。例えば、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;
ティップ: cycleを認識するために検査しなければならない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問い合わせによりたとえ1回以上参照されるとしても、親問い合わせ実行でたった1回だけ評価されることです。従って、複数の場所で必要な高価な計算は、冗長作業を防止するためWITH問い合わせの中に配置することができます。他に有りうるアプリケーションとしては、望まれない副作用のある関数の多重評価を避けることです。しかし、硬貨の一方の側で、オプティマイザの能力は、親問い合わせからの制約を通常の副問い合わせではなく、WITH問い合わせに押し下げるには、劣っています。WITH問い合わせは一般的に、親問い合わせが後で破棄するであろう行を抑制せずに、書かれた通りに評価されます。(しかし、上で述べたように、問い合わせの参照が限定された数の行のみを要求する場合、評価は早期に停止します。)