?
本文檔使用 PHP中文網(wǎng)手冊 發(fā)布
WITH提供了一種在更大的SELECT查詢中編寫子查詢的方式。 這個通常稱為公共表表達式或CTEs的子查詢可以認為是定義存在于查詢中的臨時表。 這個特性的一個應用是用于分解復雜查詢?yōu)楹唵蔚牟糠?。下面是一個例子:
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來編寫,但是我們必須需要兩個隔離的SELECT嵌套語句。該方法比其它方法更容易理解。
可選的RECURSIVE修飾符從僅有的語法便利性到一個完成事情的特性的改變。 使用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, 它們只可以包含參考查詢的輸出。這樣的一個查詢執(zhí)行如下:
遞歸查詢評估
評估無遞歸術語。使用UNION(并不是UNION ALL),去除重復的行。包括在遞歸查詢結果中所有剩余的行,并將它們放入臨時的工作表。
只要工作表不為空,那么將重復這些步驟:
評價遞歸術語,為遞歸自我參照替換當前工作表內(nèi)容。 用UNION(并不是UNION ALL),去除重復的行和與以前 結果行重復的行。 包括所有在遞歸查詢結果中剩余的行,并將它們放入一個臨時的中間表。
以中間表的內(nèi)容替換工作表的內(nèi)容,然后清空中間表。
Note: 嚴格的說,該過程是迭代而不是遞歸,但是RECURSIVE是通過SQL標準委員會選擇的術語。
在上面的例子中,在每一步中僅有一個工作表行,并且在后續(xù)的步驟中它的值將從1升至100。 在第100步,因為WHERE子句的原因沒有任何輸出, 因此查詢終止。
遞歸查詢通常用于處理分層或樹狀結構數(shù)據(jù)。一個有用的示例查詢是查找所有直接 或間接的產(chǎ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
當使用遞歸查詢的時候,確保查詢的遞歸部分最終不會返回元組是很重要的,否則查詢將會循環(huán)下去。 有時,通過使用UNION替代UNION ALL去除掉前面輸出重復的行可以實現(xiàn)這個。 然而,通常一個周期不涉及那些完全復制的輸出行:檢查一個或幾個字段來查看是否存在事先達成的 相同點可能是必要的。 處理這種情況的標準方式是計算一個訪問隊列。 例如,請考慮下面的查詢,使用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關系包含循環(huán)那么這個查詢將會循環(huán)。 因為我們需要一個"深度"輸出,僅改變UNION ALL為UNION將不會消除循環(huán)。 相反,我們需要認識到我們當按照特定路徑鏈接時是否再次得到了相同的行。 我們添加兩列path和cycle到傾向循環(huán)的查詢:
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ù)組值通常是有用的,在它的右邊作為代表采取的得到任何特定行的"路徑"。
在一般情況下,使用一個行數(shù)組多于一個字段需要檢查到一個循環(huán)。 例如,如果我們需要對比字段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: 在常見的情況下,當只有一個字段需要檢查到循環(huán)的時候忽略ROW()語法。 這允許一個簡單的數(shù)組而不是使用一個復雜類型的數(shù)組獲得效率。
Tip: 遞歸查詢評估算法產(chǎn)生以廣度優(yōu)先搜索順序的輸出。 你可以按照深度優(yōu)先查詢排序通過通過外部查詢ORDER BY一個"path"列來顯示結果。
當你不能確定它們在設置了一個LIMIT父查詢后是否會循環(huán)的時候,這是一個 對于測試查詢有用的技巧。 例如,這個循環(huán)將在沒有LIMIT的情況下循環(huán):
WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t ) SELECT n FROM t LIMIT 100;
它能工作是因為PostgreSQL的實現(xiàn)評估只有當許多實際上是通過父 查詢獲取的WITH查詢行。 在實際的生產(chǎn)環(huán)境下不推薦使用該技巧,因為其它的系統(tǒng)可以以不同的方式工作。 同樣,如果你使用外部查詢將遞歸查詢結果或將它們加入到別的表中分類,那么它通常是不工作的。
一個有用的WITH查詢屬性是每個父查詢執(zhí)行一次它們做一次評估,即使指定它們 不止一次地通過父查詢或WITH查詢。 所以,復雜的需要在多個地方放置的計算可以通過設置WITH查詢來避免冗余工作。 另一個可能的應用是防止不必要的多副作用函數(shù)的評估。 然而,另一方面,比起普通的子查詢,優(yōu)化器是不能夠避開父查詢拆分為一個WITH查詢的限制。 通常將WITH查詢評估如上,沒有行限制的父查詢可能丟失。(但是,正如上面所說, 如果查詢參考查詢數(shù)量有限的行,評估可能會很早終止。)