7.8. WITH問い合わせ（共通テーブル式）

7.8.1. SELECT in WITH

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_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;

は上位販売地域のみの商品ごとの売上高合計を表示します。 WITH句はregional_salesとtop_regionsという2つの補助文を定義し、regional_salesの出力はtop_regionsで、top_regionsの出力は主SELECT問い合わせで使用されるようにします。 この例はWITHなしで書くこともできますが、2つのレベルの入れ子になった副SELECTが必要でした。

オプションのRECURSIVE修飾子は、WITHを単なる構文の便利さから、標準SQLでは不可能なことを実現する機能へと変化させます。 RECURSIVEを使用すると、WITH問い合わせはそれ自身の出力を参照することができます。 非常に簡単な例として、1から100までの整数を合計する問い合わせがあります。 このようなクエリは次のように実行されます。

上記の例では、作業テーブルは各ステップで1行だけあり、連続して1～100までの値を取るようになっています。 100番目のステップでは、WHERE句のために出力がないので、クエリは終了します。

再帰的クエリは通常、階層的または木構造データを処理するために使用されます。

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_quantityFROM included_partsGROUP BY sub_part

再帰的な問い合わせを行う場合、問い合わせの再帰的な部分が最終的にタプルを返さないことを確認することが重要です。 UNION ALLの代わりにUNIONを使用することで、以前の出力行と重複する行を破棄し、これを達成できることがあります。 しかし、多くの場合、循環は完全に重複した出力行を伴わないことが多く、以前に同じ地点に到達したかどうかを確認するために、1つまたはいくつかのフィールドだけをチェックすることが必要な場合があります。 このような状況を処理する標準的な方法は、既に訪れた値の配列を計算することです。 例えば、リンクフィールドを使用してテーブルグラフを検索する以下のクエリを考えてみましょう：

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;

このクエリは、リンク関係にサイクルが含まれている場合にループします。 深さ」の出力が必要なので、UNION ALLをUNIONに変更するだけでは、ループは解消されません。 代わりに、リンクの特定の経路をたどっている間に再び同じ行に到達したかどうかを認識する必要があります。

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, 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;

サイクルを防止することは別として、配列の値はそれ自体、特定の行に到達するために取られた「パス」を表すものとして有用であることが多いです。 例えば、フィールド f1 と f2 を比較する必要がある場合:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, 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;

ループするかどうかわからないクエリをテストするのに役立つトリックは、親クエリにLIMITを配置することです。 例えば、この問い合わせはLIMITがなければ永遠にループしてしまいます。 他のシステムでは異なる動作をするかもしれませんので、このトリックを実運用で使用することはお勧めしません。 また、外部問い合わせが再帰問い合わせの結果をソートしたり、他のテーブルと結合したりする場合、通常この方法は機能しません。

WITH問い合わせの有用な特性は、たとえ親問い合わせや兄弟WITH問い合わせによって複数回参照されても、親問い合わせの実行ごとに1回のみ評価されることです。 したがって、複数の場所で必要とされる高価な計算をWITHクエリ内に配置することで、冗長な作業を避けることができます。 また、副作用のある関数の不要な多重評価を防ぐという用途も考えられます。 しかし、このコインの裏側には、オプティマイザが通常のサブクエリよりも親クエリからの制約をWITHクエリに押し下げる力が弱いということがあります。 WITH問い合わせは一般的に、親問い合わせが後で破棄する可能性のある行を抑制することなく、記述された通りに評価されます。 (しかし、上述のように、問い合わせへの参照が限られた数の行しか必要としない場合、評価は早期に停止するかもしれません。)

上記の例では、SELECTで使用されるWITHのみを示していますが、INSERT、UPDATE、DELETEに同じように付けることが可能です。 いずれの場合も、主コマンドで参照できる一時テーブルを効果的に提供します。