PostgreSQL WITH查詢（公共表表達(dá)式）

WITH中SELECT的基本價(jià)值是將復(fù)雜的查詢分解稱為簡(jiǎn)單的部分。一個(gè)例子：

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;

它只顯示在高銷售區(qū)域每種產(chǎn)品的銷售總額。WITH子句定義了兩個(gè)輔助語(yǔ)句regional_sales和top_regions，其中regional_sales的輸出用在top_regions中而top_regions的輸出用在主SELECT查詢。這個(gè)例子可以不用WITH來(lái)書寫，但是我們必須要用兩層嵌套的子SELECT。使用這種方法要更簡(jiǎn)單些。

可選的RECURSIVE修飾符將WITH從單純的句法便利變成了一種在標(biāo)準(zhǔn)SQL中不能完成的特性。通過(guò)使用RECURSIVE，一個(gè)WITH查詢可以引用它自己的輸出。一個(gè)非常簡(jiǎn)單的例子是計(jì)算從1到100的整數(shù)合的查詢：

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

一個(gè)遞歸WITH查詢的通常形式總是一個(gè)非遞歸項(xiàng)，然后是UNION（或者UNION ALL），再然后是一個(gè)遞歸項(xiàng)，其中只有遞歸項(xiàng)能夠包含對(duì)于查詢自身輸出的引用。這樣一個(gè)查詢可以被這樣執(zhí)行：

在上面的例子中，工作表在每一步只有一個(gè)行，并且它在連續(xù)的步驟中取值從1到100。在第100步，由于WHERE子句導(dǎo)致沒有輸出，因此查詢終止。

遞歸查詢通常用于處理層次或者樹狀結(jié)構(gòu)的數(shù)據(jù)。一個(gè)有用的例子是這個(gè)用于找到一個(gè)產(chǎn)品的直接或間接部件的查詢，只要給定一個(gè)顯示了直接包含關(guān)系的表：

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

在使用遞歸查詢時(shí)，確保查詢的遞歸部分最終將不返回元組非常重要，否則查詢將會(huì)無(wú)限循環(huán)。在某些時(shí)候，使用UNION替代UNION ALL可以通過(guò)拋棄與之前輸出行重復(fù)的行來(lái)達(dá)到這個(gè)目的。不過(guò)，經(jīng)常有循環(huán)不涉及到完全重復(fù)的輸出行：它可能只需要檢查一個(gè)或幾個(gè)域來(lái)看相同點(diǎn)之前是否達(dá)到過(guò)。處理這種情況的標(biāo)準(zhǔn)方法是計(jì)算一個(gè)已經(jīng)訪問(wèn)過(guò)值的數(shù)組。例如，考慮下面這個(gè)使用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關(guān)系包含環(huán)，這個(gè)查詢將會(huì)循環(huán)。因?yàn)槲覀円笠粋€(gè)“depth”輸出，僅僅將UNION ALL 改為UNION不會(huì)消除循環(huán)。反過(guò)來(lái)在我們順著一個(gè)特定鏈接路徑搜索時(shí)，我們需要識(shí)別我們是否再次到達(dá)了一個(gè)相同的行。我們可以項(xiàng)這個(gè)有循環(huán)傾向的查詢?cè)黾觾蓚€(gè)列path和cycle：

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;

除了阻止環(huán)，數(shù)組值對(duì)于它們自己的工作顯示到達(dá)任何特定行的“path”也有用。

在通常情況下如果需要檢查多于一個(gè)域來(lái)識(shí)別一個(gè)環(huán)，請(qǐng)用行數(shù)組。例如，如果我們需要比較域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;

當(dāng)你不確定查詢是否可能循環(huán)時(shí)，一個(gè)測(cè)試查詢的有用技巧是在父查詢中放一個(gè)LIMIT。例如，這個(gè)查詢沒有LIMIT時(shí)會(huì)永遠(yuǎn)循環(huán)：

WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

這會(huì)起作用，因?yàn)?span id="7r4pkbo" class="productname">PostgreSQL的實(shí)現(xiàn)只計(jì)算WITH查詢中被父查詢實(shí)際取到的行。不推薦在生產(chǎn)中使用這個(gè)技巧，因?yàn)槠渌到y(tǒng)可能以不同方式工作。同樣，如果你讓外層查詢排序遞歸查詢的結(jié)果或者把它們連接成某種其他表，這個(gè)技巧將不會(huì)起作用，因?yàn)樵谶@些情況下外層查詢通常將嘗試取得WITH查詢的所有輸出。

WITH查詢的一個(gè)有用的特性是在每一次父查詢的執(zhí)行中它們通常只被計(jì)算一次，即使它們被父查詢或兄弟WITH查詢引用了超過(guò)一次。 因此，在多個(gè)地方需要的昂貴計(jì)算可以被放在一個(gè)WITH查詢中來(lái)避免冗余工作。另一種可能的應(yīng)用是阻止不希望的多個(gè)函數(shù)計(jì)算產(chǎn)生副作用。 但是，從另一方面來(lái)看，優(yōu)化器不能將來(lái)自父查詢的約束下推到乘法引用WITH查詢，因?yàn)楫?dāng)他應(yīng)該只影響一個(gè)時(shí)它可能會(huì)影響所有使用WITH查詢的輸出的使用。 乘法引用WITH查詢通常將會(huì)被按照所寫的方式計(jì)算，而不抑制父查詢以后可能會(huì)拋棄的行（但是，如上所述，如果對(duì)查詢的引用只請(qǐng)求有限數(shù)目的行，計(jì)算可能會(huì)提前停止）。

但是，如果 WITH 查詢是非遞歸和邊際效應(yīng)無(wú)關(guān)的（就是說(shuō)，它是一個(gè)SELECT包含沒有可變函數(shù)），則它可以合并到父查詢中，允許兩個(gè)查詢級(jí)別的聯(lián)合優(yōu)化。 默認(rèn)情況下，這發(fā)生在如果父查詢僅引用 WITH查詢一次的時(shí)候，而不是它引用WITH查詢多于一次時(shí)。 你可以超越控制這個(gè)決策，通過(guò)指定 MATERIALIZED 來(lái)強(qiáng)制分開計(jì)算 WITH 查詢，或者通過(guò)指定 NOT MATERIALIZED來(lái)強(qiáng)制它被合并到父查詢中。 后一種選擇存在重復(fù)計(jì)算WITH查詢的風(fēng)險(xiǎn)，但它仍然能提供凈節(jié)省，如果WITH查詢的每個(gè)使用只需要WITH查詢的完整輸出的一小部分。

這些規(guī)則的一個(gè)簡(jiǎn)單示例是

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;

這個(gè) WITH 查詢將被合并，生成相同的執(zhí)行計(jì)劃為

SELECT * FROM big_table WHERE key = 123;

特別是，如果在key上有一個(gè)索引，它可能只用于獲取具有 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查詢將被物化，生成一個(gè)big_table的臨時(shí)拷貝，然后與其自身 — 聯(lián)合，這樣將不能從索引上獲得任何好處。 如果寫成下面的形式，這個(gè)查詢將被執(zhí)行得更有效率。

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;

所以父查詢的限制可以直接應(yīng)用于big_table的掃描。

一個(gè)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每個(gè)表行只計(jì)算一次，而不是兩次。

以上的例子只展示了和SELECT一起使用的WITH，但是它可以被以相同的方式附加在INSERT、UPDATE或DELETE上。在每一種情況中，它實(shí)際上提供了可在主命令中引用的臨時(shí)表。

你可以在WITH中使用數(shù)據(jù)修改語(yǔ)句（INSERT、UPDATE或DELETE）。這允許你在同一個(gè)查詢中執(zhí)行多個(gè)而不同操作。一個(gè)例子：

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;

這個(gè)查詢實(shí)際上從products把行移動(dòng)到products_log。WITH中的DELETE刪除來(lái)自products的指定行，以它的RETURNING子句返回它們的內(nèi)容，并且接著主查詢讀該輸出并將它插入到products_log。

上述例子中好的一點(diǎn)是WITH子句被附加給INSERT，而沒有附加給INSERT的子SELECT。這是必需的，因?yàn)閿?shù)據(jù)修改語(yǔ)句只允許出現(xiàn)在附加給頂層語(yǔ)句的WITH子句中。不過(guò)，普通WITH可見性規(guī)則應(yīng)用，這樣才可能從子SELECT中引用到WITH語(yǔ)句的輸出。

正如上述例子所示，WITH中的數(shù)據(jù)修改語(yǔ)句通常具有RETURNING子句（見第 6.4 節(jié)）。它是RETURNING子句的輸出，不是數(shù)據(jù)修改語(yǔ)句的目標(biāo)表，它形成了剩余查詢可以引用的臨時(shí)表。如果一個(gè)WITH中的數(shù)據(jù)修改語(yǔ)句缺少一個(gè)RETURNING子句，則它形不成臨時(shí)表并且不能在剩余的查詢中被引用。但是這樣一個(gè)語(yǔ)句將被執(zhí)行。一個(gè)非特殊使用的例子：

WITH t AS (
    DELETE FROM foo
)
DELETE FROM bar;

這個(gè)例子將從表foo和bar中移除所有行。被報(bào)告給客戶端的受影響行的數(shù)目可能只包括從bar中移除的行。

數(shù)據(jù)修改語(yǔ)句中不允許遞歸自引用。在某些情況中可以采取引用一個(gè)遞歸WITH的輸出來(lái)操作這個(gè)限制，例如：

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);

這個(gè)查詢將會(huì)移除一個(gè)產(chǎn)品的所有直接或間接子部件。

WITH中的數(shù)據(jù)修改語(yǔ)句只被執(zhí)行一次，并且總是能結(jié)束，而不管主查詢是否讀取它們所有（或者任何）的輸出。注意這和WITH中SELECT的規(guī)則不同：正如前一小節(jié)所述，直到主查詢要求SELECT的輸出時(shí)，SELECT才會(huì)被執(zhí)行。

The sub-statements in WITH中的子語(yǔ)句被和每一個(gè)其他子語(yǔ)句以及主查詢并發(fā)執(zhí)行。因此在使用WITH中的數(shù)據(jù)修改語(yǔ)句時(shí)，指定更新的順序?qū)嶋H是以不可預(yù)測(cè)的方式發(fā)生的。所有的語(yǔ)句都使用同一個(gè)snapshot執(zhí)行（參見第 13 章），因此它們不能“看見”在目標(biāo)表上另一個(gè)執(zhí)行的效果。這減輕了行更新的實(shí)際順序的不可預(yù)見性的影響，并且意味著RETURNING數(shù)據(jù)是在不同WITH子語(yǔ)句和主查詢之間傳達(dá)改變的唯一方法。其例子

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

外層SELECT可以返回在UPDATE動(dòng)作之前的原始價(jià)格，而在

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

外部SELECT將返回更新過(guò)的數(shù)據(jù)。

在一個(gè)語(yǔ)句中試圖兩次更新同一行是不被支持的。只會(huì)發(fā)生一次修改，但是該辦法不能很容易地（有時(shí)是不可能）可靠地預(yù)測(cè)哪一個(gè)會(huì)被執(zhí)行。這也應(yīng)用于刪除一個(gè)已經(jīng)在同一個(gè)語(yǔ)句中被更新過(guò)的行：只有更新被執(zhí)行。因此你通常應(yīng)該避免嘗試在一個(gè)語(yǔ)句中嘗試兩次修改同一個(gè)行。尤其是防止書寫可能影響被主語(yǔ)句或兄弟子語(yǔ)句修改的相同行。這樣一個(gè)語(yǔ)句的效果將是不可預(yù)測(cè)的。

當(dāng)前，在WITH中一個(gè)數(shù)據(jù)修改語(yǔ)句中被用作目標(biāo)的任何表不能有條件規(guī)則、ALSO規(guī)則或INSTEAD規(guī)則，這些規(guī)則會(huì)擴(kuò)展成為多個(gè)語(yǔ)句。