無料スクリプト配布のPHP.TO   PHPの実用的なtips PHPマニュアル MySQLマニュアル Apacheマニュアル PostgreSQLマニュアル マニュアル検索    

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

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

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

再帰的問い合わせの評価

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

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

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

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

注意: 厳密には、この手順は反復であって再帰ではありませんが、 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 を付与することができます。 それぞれの場合において、これは主コマンド内で参照可能な一時テーブルを実質的に提供します。

7.8.2. 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 に挿入します。

上の例の見事なところは、 INSERT 内の副 SELECT ではなく WITH 句が INSERT に付与されていることです。 これは、データ更新文は最上位レベルの文に付与される WITH 句内でのみ許されているため必要です。 しかし、通常の WITH の可視性規則が適用されますので、副 SELECT から WITH 文の出力を参照することができます。

上の例で示したように、 WITH 内のデータ変更文は通常 RETURNING 句を持ちます。 データ変更文の対象テーブルでは なく 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 ルールを持ってはなりません。


powered by SEO.CUG.NET