PolarDB：WITH查詢（公用表運算式）

簡介

WITH提供了一種方式來書寫在一個大型查詢中使用的輔助語句。這些語句通常被稱為公用表運算式或 CTE，它們可以被看成是定義只在一個查詢中存在的暫存資料表。在WITH子句中的每一個輔助語句可以是一個SELECT、INSERT、UPDATE或DELETE，並且WITH子句本身也可以被附加到一個主語句，主語句也可以是SELECT、INSERT、UPDATE或DELETE。

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查詢的通常形式總是一個非遞迴項，然後是UNION（或者UNION ALL），再然後是一個遞迴項，其中只有遞迴項能夠包含對於查詢自身輸出的引用。這樣一個查詢可以被這樣執行：

遞迴查詢求值

1. 計算非遞迴項。對UNION（但不對UNION ALL），拋棄重複行。把所有剩餘的行包括在遞迴查詢的結果中，並且也把它們放在一個臨時的工作表中。

2. 只要工作表不為空白，重複下列步驟：

1. 計算遞迴項，用當前工作表的內容替換遞迴自引用。對UNION（不是UNION ALL），拋棄重複行以及那些與之前結果行重複的行。將剩下的所有行包括在遞迴查詢的結果中，並且也把它們放在一個臨時的中間表中。

2. 用中間表的內容替換工作表的內容，然後清空中間表。

在上面的例子中，工作表在每一步只有一個行，並且它在連續的步驟中取值從 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關係包含環，這個查詢將會迴圈。因為我們要求一個“depth”輸出，僅僅將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;

除了阻止環，數組值對於它們自己的工作顯示到達任何特定行的“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;

當你不確定查詢是否可能迴圈時，一個測試查詢的有用技巧是在父查詢中放一個LIMIT。例如，這個查詢沒有LIMIT時會永遠迴圈：

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

這會起作用，因為本資料庫中的實現只計算WITH查詢中被父查詢實際取到的行。不推薦在生產中使用這個技巧，因為其他系統可能以不同方式工作。同樣，如果你讓外層查詢排序遞迴查詢的結果或者把它們串連成某種其他表，這個技巧將不會起作用，因為在這些情況下外層查詢通常將嘗試取得WITH查詢的所有輸出。

WITH查詢的一個有用的特性是在每一次父查詢的執行中它們通常只被計算一次，即使它們被父查詢或兄弟WITH查詢引用了超過一次。 因此，在多個地方需要的昂貴計算可以被放在一個WITH查詢中來避免冗餘工作。另一種可能的應用是阻止不希望的多個Function Compute產生副作用。 但是，從另一方面來看，最佳化器不能將來自父查詢的約束下推到乘法引用WITH查詢，因為當他應該隻影響一個時它可能會影響所有使用WITH查詢的輸出的使用。 乘法引用WITH查詢通常將會被按照所寫的方式計算，而不抑制父查詢以後可能會拋棄的行（但是，如上所述，如果對查詢的引用只請求有限數目的行，計算可能會提前停止）。

但是，如果 WITH 查詢是非遞迴和邊際效應無關的（就是說，它是一個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;

特別是，如果在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每個錶行只計算一次，而不是兩次。

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

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

這個查詢將會移除一個產品的所有直接或間接子組件。

WITH中的資料修改語句只被執行一次，並且總是能結束，而不管主查詢是否讀取它們所有（或者任何）的輸出。注意這和WITH中SELECT的規則不同：正如前一小節所述，直到主查詢要求SELECT的輸出時，SELECT才會被執行。

The sub-statements in WITH中的子語句被和每一個其他子語句以及主查詢並發執行。因此在使用WITH中的資料修改語句時，指定更新的順序實際是以不可預測的方式發生的。所有的語句都使用同一個snapshot執行，因此它們不能“看見”在目標表上另一個執行的效果。這減輕了行更新的實際順序的不可預見性的影響，並且意味著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中一個資料修改語句中被用作目標的任何錶不能有條件規則、ALSO規則或INSTEAD規則，這些規則會擴充成為多個語句。