7.8. WITH 查詢 (通用表表達式) · PostgreSQL 中文文檔 9.3

# 7.8\. `WITH` 查詢 (通用表表達式) `WITH`提供了一種在更大的查詢中編寫輔助語句的方式。這個通常稱為通用表表達式或CTEs的輔助語句可以認為是定義只存在于一個查詢中的臨時表。每個`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; ``` 它顯示了每個產品僅在銷售區域的銷售總額。`WITH`子句定義了兩個名為 `regional_sales` 和 `top_regions`的輔助語句， `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-recursive term_，然后`UNION`（或者`UNION ALL`），然后一個_recursive term_，其中只有遞歸的術語可以包含一個對查詢自己輸出的引用。這樣一個查詢像下面那樣執行： **遞歸查詢評估** 1. 評估非遞歸的術語。使用`UNION`（而不是`UNION ALL`）去除重復的行。包括在遞歸查詢結果中所有剩余的行，并將它們放入臨時的_工作表_。 2. 只要工作表不為空，那么將重復這些步驟： 1. 評估遞歸術語，為遞歸自我參照替換當前工作表內容。用`UNION`(并不是`UNION ALL`)，去除重復的行和與以前結果行重復的行。包括所有在遞歸查詢結果中剩余的行，并將它們放入一個臨時的_中間表_。 2. 用中間表的內容替換工作表的內容，然后清空中間表。 > **Note:** 嚴格的說，該過程是迭代而不是遞歸，但是`RECURSIVE`是通過 SQL 標準委員會選擇的術語。在上面的例子中，在每一步中僅有一個工作表行，并且在后續的步驟中它的值將從 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_quantity FROM included_parts GROUP BY sub_part ``` 當使用遞歸查詢的時候，確保查詢的遞歸部分最終不會返回元組是很重要的，否則查詢將會無限的循環下去。有時，通過使用`UNION`替代`UNION ALL` 去除掉與前面輸出重復的行可以實現這個。然而，通常一個周期不涉及那些完全復制的輸出行：檢查一個或幾個字段來查看是否存在事先達成的相同點可能是必要的。處理這種情況的標準方式是計算一個已經訪問過的數值的數組。例如，請考慮下面的查詢，使用`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`到傾向循環的查詢： ``` 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; ``` 除了防止循環，該數組值通常是有用的，在它的右邊作為代表用來得到任何特定行的"路徑"。在一般情況下，需要檢測多個字段來識別一個循環時使用一個行數組。例如，如果我們需要對比字段`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; ``` > **Tip:** 在常見的情況下，當只需要檢查一個字段來識別循環的時候忽略`ROW()`語法。這允許使用一個簡單的數組而不是一個復雜類型的數組，增加查詢的效率。 > **Tip:** 遞歸查詢評估算法產生以廣度優先搜索順序的輸出。您可以按照深度優先查詢排序通過外部查詢`ORDER BY`一個"path"列來顯示結果。當您不能確定它們是否會循環的時候，在一個父查詢中放置`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`在`SELECT`中的使用，但是它也可以用同樣的方式附加到`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`中。上面例子的一個優點是`WITH`子句是附加到`INSERT`，而不是`INSERT`中的子`SELECT`查詢。這是必須的，因為數據修改語句只允許在附加到頂級語句的`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); ``` 這個查詢將刪除一個產品所有直接或非直接的subparts。 `WITH`中的數據修改語句被直接執行一次，并且總是完成，獨立的主查詢讀取所有（或者實際上是任意）它們的輸出。注意，這和在`WITH`中`SELECT`的規則不同：就像前一節規定的那樣，`SELECT`的執行直到首級查詢需要它的輸出時才實施。 `WITH`中的子語句之間和與主查詢之間兼容的執行。因此，當在`WITH`中使用數據修改語句時，其他的指定的更新實際上是不可預知發生的。所有的語句都在相同的_快照_中執行（見[Chapter 13](#calibre_link-444)），所以他們不能"看見"彼此對目標表的影響。這樣減輕了實際行更新的不可預知的影響，并且意味著`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`將返回更新了的數據。不支持嘗試在一個語句中更新相同的行兩次。如果嘗試了，那么只有一個修改會發生，但是不容易（或者有時不可能）準確預測是哪一個。這個同樣適用于刪除一個已經在相同語句中更新了的行：只有更新被執行。因此你通常應該避免嘗試在一個語句中修改一個行兩次。特別的，避免寫可能影響被主語句或同級子語句改變了的行的`WITH`子語句。這樣一個語句的影響將是不可預測的。目前，任何作為在`WITH`中的數據修改語句目標的表，不必有擴展到多個語句的條件規則、 `ALSO`規則和`INSTEAD`規則。