SQL優(yōu)化（五） PostgreSQL （遞歸）CTE 通用表表達式

原創(chuàng)文章，首發(fā)自作者個人博客，轉載請務必將下面這段話置于文章開頭處。
　　本文轉發(fā)自Jason's Blog，原文鏈接　http://www.jasongj.com/sql/cte/

CTE or WITH

WITH語句通常被稱為通用表表達式（Common Table Expressions）或者CTEs。

WITH語句作為一個輔助語句依附于主語句，WITH語句和主語句都可以是SELECT，INSERT，UPDATE，DELETE中的任何一種語句。

例講CTE

WITH語句最基本的功能是把復雜查詢語句拆分成多個簡單的部分，如下例所示

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。前者算出每個區(qū)域的總銷售量，后者了查出所有銷售量占所有地區(qū)總銷售里10%以上的區(qū)域。主語句通過將這個CTEs及訂單表關聯(lián)，算出了頂級區(qū)域每件商品的銷售量和銷售額。

當然，本例也可以不使用CTEs而使用兩層嵌套子查詢來實現(xiàn)，但使用CTEs更簡單，更清晰，可讀性更強。

在WITH中使用數(shù)據(jù)修改語句

文章開頭處提到，WITH中可以不僅可以使用SELECT語句，同時還能使用DELETE，UPDATE，INSERT語句。因此，可以使用WITH，在一條SQL語句中進行不同的操作，如下例所示。

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;

本例通過WITH中的DELETE語句從products表中刪除了一個月的數(shù)據(jù)，并通過RETURNING子句將刪除的數(shù)據(jù)集賦給moved_rows這一CTE，最后在主語句中通過INSERT將刪除的商品插入products_log中。

如果WITH里面使用的不是SELECT語句，并且沒有通過RETURNING子句返回結果集，則主查詢中不可以引用該CTE，但主查詢和WITH語句仍然可以繼續(xù)執(zhí)行。這種情況可以實現(xiàn)將多個不相關的語句放在一個SQL語句里，實現(xiàn)了在不顯式使用事務的情況下保證WITH語句和主語句的事務性，如下例所示。

WITH d AS (
  DELETE FROM foo
),
u as (
  UPDATE foo SET a = 1
  WHERE b = 2
)
DELETE FROM bar;

WITH使用注意事項

  1. WITH中的數(shù)據(jù)修改語句會被執(zhí)行一次，并且肯定會完全執(zhí)行，無論主語句是否讀取或者是否讀取所有其輸出。而WITH中的SELECT語句則只輸出主語句中所需要記錄數(shù)。
  2. WITH中使用多個子句時，這些子句和主語句會并行執(zhí)行，所以當存在多個修改子語句修改相同的記錄時，它們的結果不可預測。
  3. 所有的子句所能“看”到的數(shù)據(jù)集是一樣的，所以它們看不到其它語句對目標數(shù)據(jù)集的影響。這也緩解了多子句執(zhí)行順序的不可預測性造成的影響。
  4. 如果在一條SQL語句中，更新同一記錄多次，只有其中一條會生效，并且很難預測哪一個會生效。
  5. 如果在一條SQL語句中，同時更新和刪除某條記錄，則只有更新會生效。
  6. 目前，任何一個被數(shù)據(jù)修改CTE的表，不允許使用條件規(guī)則，和ALSO規(guī)則以及INSTEAD規(guī)則。

WITH RECURSIVE

WITH語句還可以通過增加RECURSIVE修飾符來引入它自己，從而實現(xiàn)遞歸

WITH RECURSIVE實例

WITH RECURSIVE一般用于處理邏輯上層次化或樹狀結構的數(shù)據(jù)，典型的使用場景是尋找直接及間接子結點。

定義下面這樣的表，存儲每個區(qū)域（省、市、區(qū)）的id，名字及上級區(qū)域的id

CREATE TABLE chinamap
(
  id INTEGER,
  pid INTEGER,
  name TEXT
);

需要查出某個省，比如湖北省，管轄的所有市及市轄地區(qū)，可以通過WITH RECURSIVE來實現(xiàn)，如下

WITH RECURSIVE result AS
(
  SELECCT
    id,
    name
  FROM  chinamap
  WHERE id = 11
  UNION ALL
  SELECT
    origin.id,
    result.name || ' > ' || origin.name
  FROM result
  JOIN chinamap origin
  ON origin.pid = result.id
)
SELECT
  id,
  name
FROM result;

結果如下

 id  |           name           
-----+--------------------------
  11 | 湖北省
 110 | 湖北省 > 武漢市
 120 | 湖北省 > 孝感市
 130 | 湖北省 > 宜昌市
 140 | 湖北省 > 隨州市
 150 | 湖北省 > 仙桃市
 160 | 湖北省 > 荊門市
 170 | 湖北省 > 枝江市
 180 | 湖北省 > 神農架市
 111 | 湖北省 > 武漢市 > 武昌區(qū)
 112 | 湖北省 > 武漢市 > 下城區(qū)
 113 | 湖北省 > 武漢市 > 江岸區(qū)
 114 | 湖北省 > 武漢市 > 江漢區(qū)
 115 | 湖北省 > 武漢市 > 漢陽區(qū)
 116 | 湖北省 > 武漢市 > 洪山區(qū)
 117 | 湖北省 > 武漢市 > 青山區(qū)
(16 rows)

WITH RECURSIVE 執(zhí)行過程

從上面的例子可以看出，WITH RECURSIVE語句包含了兩個部分

  • non-recursive term（非遞歸部分），即上例中的union all前面部分
  • recursive term（遞歸部分），即上例中union all后面部分

執(zhí)行步驟如下

  1. 執(zhí)行non-recursive term。（如果使用的是union而非union all，則需對結果去重）其結果作為recursive term中對result的引用，同時將這部分結果放入臨時的working table中
  2. 重復執(zhí)行如下步驟，直到working table為空：用working table的內容替換遞歸的自引用，執(zhí)行recursive term，（如果使用union而非union all，去除重復數(shù)據(jù)），并用該結果（如果使用union而非union all，則是去重后的結果）替換working table

以上面的query為例，來看看具體過程
1.執(zhí)行

SELECT
  id,
  name
FROM chinamap
WHERE id = 11

結果集和working table為

11 | 湖北

2.執(zhí)行

SELECT
  origin.id,
  result.name || ' > ' || origin.name
FROM result
JOIN chinamap origin
ON origin.pid = result.id

結果集和working table為

 110 | 湖北省 > 武漢市
 120 | 湖北省 > 孝感市
 130 | 湖北省 > 宜昌市
 140 | 湖北省 > 隨州市
 150 | 湖北省 > 仙桃市
 160 | 湖北省 > 荊門市
 170 | 湖北省 > 枝江市
 180 | 湖北省 > 神農架市

3.再次執(zhí)行recursive query，結果集和working table為

 111 | 湖北省 > 武漢市 > 武昌區(qū)
 112 | 湖北省 > 武漢市 > 下城區(qū)
 113 | 湖北省 > 武漢市 > 江岸區(qū)
 114 | 湖北省 > 武漢市 > 江漢區(qū)
 115 | 湖北省 > 武漢市 > 漢陽區(qū)
 116 | 湖北省 > 武漢市 > 洪山區(qū)
 117 | 湖北省 > 武漢市 > 青山區(qū)

4.繼續(xù)執(zhí)行recursive query，結果集和working table為空
5.結束遞歸，將前三個步驟的結果集合并，即得到最終的WITH RECURSIVE的結果集

嚴格來講，這個過程實現(xiàn)上是一個迭代的過程而非遞歸，不過RECURSIVE這個關鍵詞是SQL標準委員會定立的，所以PostgreSQL也延用了RECURSIVE這一關鍵詞。

WITH RECURSIVE 防止死循環(huán)

從上一節(jié)中可以看到，決定是否繼續(xù)迭代的working table是否為空，如果它永不為空，則該CTE將陷入無限循環(huán)中。
對于本身并不會形成循環(huán)引用的數(shù)據(jù)集，無段作特別處理。而對于本身可能形成循環(huán)引用的數(shù)據(jù)集，則須通過SQL處理。

一種方式是使用UNION而非UNION ALL，從而每次recursive term的計算結果都會將已經存在的數(shù)據(jù)清除后再存入working table，使得working table最終會為空，從而結束迭代。

然而，這種方法并不總是有效的，因為有時可能需要這些重復數(shù)據(jù)。同時UNION只能去除那些所有字段都完全一樣的記錄，而很有可能特定字段集相同的記錄即應該被刪除。此時可以通過數(shù)組（單字段）或者ROW（多字段）記錄已經訪問過的記錄，從而實現(xiàn)去重的目的。

WITH RECURSIVE 求最短路徑

定義無向有環(huán)圖如下圖所示


Non-directional cycle graph

定義如下表并存入每條邊的權重

CREATE TABLE graph
(
  id char,
  neighbor char,
  value integer
);
INSERT INTO graph
VALUES('A', 'B', 3),
('A', 'C', 5),
('A', 'D', 4),
('B', 'E', 8),
('B', 'C', 4),
('E', 'C', 7),
('E','F', 10),
('C', 'D', 3),
('C', 'F', 6),
('F','D', 5);

計算思路如下：

  • 因為是無向圖，所以首先要將各條邊的id和neighbor交換一次以方便后續(xù)計算。
  • 利用WITH RECURSIVE算出所有可能的路徑并計算其總權重。
  • 因為該圖有環(huán)，為避免無限循環(huán)，同時為了計算路徑，將經過的結點存于數(shù)據(jù)中，當下一個結點已經在數(shù)據(jù)中時，說明該結點已被計算。
  • 最終可算出所有可能的路徑及其總權重

實現(xiàn)如下

 WITH RECURSIVE edges AS (
  SELECT id, neighbor, value FROM graph
  UNION ALL
  SELECT neighbor, id, value 
  FROM graph
), 
all_path (id, neighbor, value, path, depth, cycle) AS (
  SELECT
    id, neighbor, value, ARRAY[id], 1, 'f'::BOOLEAN
  FROM edges
  WHERE id = 'A'
  UNION ALL
  SELECT
    all_path.id,
    edges.neighbor,
    edges.value + all_path.value,
    all_path.path || ARRAY[edges.id],
    depth + 1,
    edges.id = ANY(all_path.path)
  FROM edges
  JOIN all_path
  ON all_path.neighbor = edges.id
  AND NOT cycle
), a_f AS (
  SELECT
    rank() over(order by value) AS rank,
    path || neighbor AS path,
    value,
    depth
  FROM all_path
  WHERE neighbor = 'F'
)
SELECT path, value, depth
FROM a_f
WHERE rank = 1;

WITH RECURSIVE 使用限制

  • 如果在recursive term中使用LEFT JOIN，自引用必須在“左”邊
  • 如果在recursive term中使用RIGHT JOIN，自引用必須在“右”邊
  • recursive term中不允許使用FULL JOIN
  • recursive term中不允許使用GROUP BY和HAVING
  • 不允許在recursive term的WHERE語句的子查詢中使用CTE的名字
  • 不支持在recursive term中對CTE作aggregation
  • recursive term中不允許使用ORDER BY
  • LIMIT / OFFSET不允許在recursive term中使用
  • FOR UPDATE不可在recursive term中使用
  • recursive term中SELECT后面不允許出現(xiàn)引用CTE名字的子查詢
  • 同時使用多個CTE表達式時，不允許多表達式之間互相訪問（支持單向訪問）
  • 在recursive term中不允許使用FOR UPDATE

CTE 優(yōu)缺點

  • 可以使用遞歸 WITH RECURSIVE，從而實現(xiàn)其它方式無法實現(xiàn)或者不容易實現(xiàn)的查詢
  • 當不需要將查詢結果被其它獨立查詢共享時，它比視圖更靈活也更輕量
  • CTE只會被計算一次，且可在主查詢中多次使用
  • CTE可極大提高代碼可讀性及可維護性
  • CTE不支持將主查詢中where后的限制條件push down到CTE中，而普通的子查詢支持

SQL優(yōu)化系列

最后編輯于 ：
?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
【社區(qū)內容提示】社區(qū)部分內容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內容（如有圖片或視頻亦包括在內）由作者上傳并發(fā)布，文章內容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內容

友情鏈接更多精彩內容