★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

7.8. WITH問い合わせ(共通テーブル式) #

WITHは、より大規模な問い合わせで使用される補助文を記述する方法を提供します。 これらの文は共通テーブル式(Common Table Expressions)またはCTEとよく呼ばれるものであり、1つの問い合わせのために存在する一時テーブルを定義すると考えることができます。 WITH句内の補助文はそれぞれSELECTINSERTUPDATEまたはDELETEを取ることができます。 そしてWITH句自身は、これもSELECTINSERTUPDATEDELETE、またはMERGEを取ることができる主文に付与されます。

7.8.1. 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_salestop_regionsという名前の2つの補助文を定義します。 ここで、regional_salesの出力はtop_regions内で使用され、top_regionsSELECT主問い合わせで使用されます。 この例は WITHなしでも記述できますが、二階層の入れ子のsub-SELECTを必要とします。この方法に従うほうが多少扱いやすいです。

7.8.2. 再帰的問い合わせ #

オプションの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)です。 再帰的表現だけが、その問い合わせ自身の出力への参照を含むことができます。 このような問い合わせは以下のように実行されます。

再帰的問い合わせの評価

  1. 非再帰的表現を評価します。 UNION(ただしUNION ALLは除きます)では、重複行を廃棄します。 その再帰的問い合わせの結果の残っているすべての行を盛り込み、同時にそれらを一時作業テーブルに置きます。

  2. 作業テーブルが空でないのであれば以下の手順を繰り返します。

    1. 再帰自己参照を作業テーブルの実行中の内容で置換し、再帰的表現を評価します。 UNION(ただしUNION ALLは除きます)に対し、重複行と前の結果行と重複する行を破棄します。 その再帰的問い合わせの結果の残っているすべての行を盛り込み、同時にそれらを一時中間テーブルに置きます。

    2. 中間テーブルの内容で作業テーブルの内容を差し替え、中間テーブルを空にします。

注記

RECURSIVEでは問い合わせを再帰的(recursively)に指定できますが、内部的にはそのような問い合わせは反復的(iteratively)に評価されます。

上記の例で、作業テーブルはそれぞれの手順での単なる単一行で、引き続く作業で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 * pr.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

7.8.2.2. サイクル検出 #

再帰的問い合わせを扱う場合、問い合わせの再帰部分が最終的にはタプルを返さないようにすることが重要です。 そうしなければ、問い合わせが永久にループしてしまうからです。 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 ALLUNIONに変えるだけでは、ループを取り除くことができません。 その代わり、linkの特定の経路をたどっている間に、同じ行に到達したかどうかを認識する必要があります。 このループしやすい問い合わせに、pathcycleの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 それ自体を表示するため、配列値はしばしば利用価値があります。

循環を認識するために検査するために必要なフィールドが複数存在する一般的な状況では、行の配列を使用します。 例えば、フィールドf1f2を比較する必要があるときは次のようにします。

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句を使用してパス列で順序付けるだけの方が効率的です。 幅優先順序が必要な場合は、SEARCHCYCLEの両方を指定すると便利です。

ループするかどうか確信が持てない問い合わせをテストする有益な秘訣として、親問い合わせに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問い合わせの出力をとにかくすべて取り込もうとするからです。

7.8.3. 共通テーブル式内マテリアライゼーション #

有用な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のみを示しています。 しかし、同じ方法でINSERTUPDATEDELETEまたはMERGEに対して付与することができます。 それぞれの場合において、これは主コマンド内で参照可能な一時テーブルを実質的に提供します。

7.8.4. WITH内のデータ変更文 #

ほとんどのデータ変更文(INSERTUPDATEDELETEは使用できますが、MERGEは使用できません)は、WITH内で使用できます。 これにより同じ問い合わせ内で複数の異なる操作を行うことができます。

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内のDELETEproductsから指定した行を削除し、そのRETURNING句により削除した内容を返します。 その後、主問い合わせはその出力を読み取り、それをproducts_logに挿入します。

上の例の見事なところは、WITH句がINSERT内のsub-SELECTではなく、INSERTに付与されていることです。 これは、データ更新文は最上位レベルの文に付与されるWITH句内でのみ許されているため必要です。 しかし、通常のWITHの可視性規則が適用されますので、sub-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;

外側のSELECTUPDATEの動作前の元々の価格を返します。

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

一方こちらでは外側のSELECTは更新されたデータを返します。

単一の文で同じ行を2回更新しようとすることはサポートされていません。 変更のうちの1つだけが行われますが、どれが実行されるかを確実に予測することは簡単ではありません(場合によっては不可能です)。 これはまた、同じ文内ですでに更新された行を削除する場合でも当てはまり、更新のみが実行されます。 したがって一般的には単一の文で1つの行を2回変更しようと試みることを避けなければなりません。 具体的には主文または同レベルの副文で変更される行と同じ行に影響を与えるWITH副文を記述することは避けてください。 こうした文の影響は予測することはできません。

現状、WITH内のデータ変更文の対象として使用されるテーブルはすべて、複数の文に展開される条件付きルール、ALSOルール、INSTEADルールを持ってはなりません。