13:7.8. クエリ(共通テーブル式)

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なしで書かれている可能性がありますが、ネストされたサブ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クエリの一般的な形式は、常に非再帰的な用語、次にUNION（またはUNION ALL）、次に再帰的な用語であり、再帰的な用語のみがクエリ自身の出力への参照を含 このようなクエリは、次のように実行されます:

上記の例では、作業テーブルは各ステップで単一の行だけを持ち、連続するステップで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つまたはいくつかのフィールドだけをチェックする必要がある場合があります。 このような状況を処理するための標準的な方法は、既に訪問された値の配列を計算することです。 たとえば、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リレーションシップにサイクルが含まれている場合にループします。 「深さ」出力が必要なため、UNION ALLをUNIONに変更するだけではループが解消されません。 代わりに、特定のリンクパスをたどっている間に同じ行に再び到達したかどうかを認識する必要があります。 ループが発生しやすいクエリにpathとcycleの2つの列を追加します:

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とフィールド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 RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t)SELECT n FROM t LIMIT 100;

これは、PostgreSQLの実装がWITHクエリの行数を、親クエリによって実際にフェッチされる行数と同じだけ評価するために機能します。 他のシステムでは動作が異なる可能性があるため、実稼働環境でこのトリックを使用することはお勧めしません。 また、外部クエリを再帰クエリの結果をソートしたり、他のテーブルに結合したりすると、外部クエリは通常、WITHクエリの出力をすべて取得しようとするため、通常は機能しません。

WITHクエリの有用なプロパティは、親クエリまたは兄弟のWITHクエリによって複数回参照されている場合でも、通常は親クエリの実行ごとに一度だけ評価 したがって、複数の場所で必要とされる高価な計算は、冗長な作業を避けるためにWITHクエリ内に配置することができます。 もう一つの可能な適用は副作用の機能の不必要な多数の評価を防ぐことである。 ただし、このコインのもう一つの側面は、オプティマイザが親クエリからの制限を乗算参照WITHクエリにプッシュすることができないことです。WITHクエリの出力のすべての使用に影響を与える可能性があるため、WITHクエリの出力は1つだけに影響します。 乗算参照されたWITHクエリは、親クエリが後で破棄する可能性のある行を抑制することなく、書き込まれたとおりに評価されます。 (ただし、前述したように、クエリへの参照が限られた数の行しか要求しない場合、評価は早期に停止する可能性があります。)

しかし、WITHクエリが非再帰的で副作用のない(つまり、volatile関数を含まないSELECTである)場合、それを親クエリに折り畳むことができ、二つのクエリレベルの共同最適化が可能になります。 デフォルトでは、親クエリがWITHクエリを一度だけ参照している場合に発生しますが、WITHクエリを複数回参照している場合には発生しません。 この決定を上書きするには、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;

と同じ実行計画が生成されます。 一方、

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

では、WITHクエリが実体化され、big_tableの一時コピーが生成され、インデックスの利点なしにそれ自身と結合されます。 このクエリは、親クエリの制限をbig_tableのスキャンに直接適用できるように、

WITH w AS NOT MATERIALIZED ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

と記述すると、はるかに効率的に実行されます。

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がテーブル行ごとに一度だけ評価され、二度では評価されません。

上記の例では、WITHがSELECTで使用されていることのみを示していますが、INSERT、UPDATE、またはDELETEにも同じ方法でアタッチできます。 いずれの場合も、メインコマンドで参照できる一時テーブルを効果的に提供します。