AnalyticDB for PostgreSQL 6.0 新特性解析：Recursive CTE (Common Table Expressions)

作者簡介：陳毅能（葦度）畢業于中國科學院，曾任職于百度、微軟亞洲研究院、華為、阿裡巴巴等公司，專注于分布式資料庫核心開發。

Recursive CTE (Common Table Expressions) 能夠實作SQL的遞歸查詢功能，一般用于處理邏輯上為階層化或樹狀結構的資料（如查詢組織結構、物料清單等），友善對該類資料進行多級遞歸查詢。與Oracle的CONNECT BY文法的功能類似。

在AnalyticDB for PostgreSQL 6.0版本中，Recursive CTE不再作為待驗證特性，而是預設打開。可以通過參數gp_recursive_cte打開或關閉Recursive CTE，預設情況下，gp_recursive_cte是打開的。

show gp_recursive_cte;

 gp_recursive_cte
------------------
 on
(1 row)           

例子：1到100求和

使用Recursive CTE可以完成一些普通SQL語句無法完成的功能。使用Recursive CTE後，SQL語句可以引用它自己的輸出。首先來看一個例子，計算1、2、……、100的和。

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

 sum
------
 5050
(1 row)           

上述例子中，CTE遞歸調用了自己，進而生成了1到100的序列，進而在主查詢中進行求和。

Recursive CTE文法及執行邏輯

使用Recursive CTE的文法如下：

WITH RECURSIVE cte_name AS (
    non_recursive_term
  UNION [ ALL ]
    recursive_term
)
sql_statement;           

在recursive_term的部分，Recursive CTE對自身進行自引用。其整體執行邏輯如下：

• 執行non_recursive_term部分。如果是UNION，還需要執行一次去重。然後将資料作為本輪執行的結果，并将其結果放入一個臨時工作表

• 如果這個臨時工作表非空，則循環執行如下步驟。如果這個臨時工作表為空，則傳回所有輪的執行結果

  （1）執行recursive_term部分。如果是UNION，需要去除重複資料，且要去除和之前結果重複的資料。然後将資料作為本輪執行的結果，并将其結果放入一個中間結果表

（2）将臨時工作表的内容替換為中間結果表，并且清空中間結果表

在使用Recursive CTE的時候，需要確定執行結果是可收斂的，即總有一輪的執行結果為空，以結束循環，否則查詢将出現無限循環。在前面1到100求和的例子中，有一個WHERE條件限制循環執行100步，數字達到100後因不滿足WHERE條件，傳回0行資料，循環終止，查詢結束。

從上述執行邏輯看，Recursive CTE将對資料進行廣度優先周遊。

例子：通過省市上下級關系表查詢

建立省市上下級關系表：

CREATE TABLE city_relation
(
    id int           -- 目前省市id
  , parent_id int    -- 上級省市id
  , name varchar(10) -- 目前省市名稱
);           

插入省市關系資料：

INSERT INTO city_relation values( 2,  NULL, '浙江省');
INSERT INTO city_relation values( 1,  NULL, '廣東省');
INSERT INTO city_relation values( 3,  2,    '衢州市');
INSERT INTO city_relation values( 4,  2,    '杭州市');
INSERT INTO city_relation values( 5,  2,    '湖州市');
INSERT INTO city_relation values( 6,  2,    '嘉興市');
INSERT INTO city_relation values( 7,  2,    '甯波市');
INSERT INTO city_relation values( 8,  2,    '紹興市');
INSERT INTO city_relation values( 9,  2,    '台州市');
INSERT INTO city_relation values(10,  2,    '溫州市');
INSERT INTO city_relation values(11,  2,    '麗水市');
INSERT INTO city_relation values(12,  2,    '金華市');
INSERT INTO city_relation values(13,  2,    '舟山市');
INSERT INTO city_relation values(14,  4,    '上城區');
INSERT INTO city_relation values(15,  4,    '下城區');
INSERT INTO city_relation values(16,  4,    '拱墅區');
INSERT INTO city_relation values(17,  4,    '餘杭區');
INSERT INTO city_relation values(18, 11,    '金東區');
INSERT INTO city_relation values(19,  1,    '廣州市');
INSERT INTO city_relation values(20,  1,    '深圳市');           

查詢浙江省及其下屬城市清單：

WITH RECURSIVE cities AS 
( 
    SELECT id, name, parent_id, name::text as path FROM city_relation WHERE id=2
  UNION ALL
    SELECT t.id, t.name, t.parent_id, c.path || '>' || t.name as path
    FROM city_relation t JOIN cities c ON t.parent_id = c.id
)
SELECT id, name, path FROM cities;           

查詢結果：

id |  name  |         path
----+-------+----------------------
  2 | 浙江省 | 浙江省
 13 | 舟山市 | 浙江省>舟山市
 11 | 麗水市 | 浙江省>麗水市
 10 | 溫州市 | 浙江省>溫州市
  9 | 台州市 | 浙江省>台州市
  6 | 嘉興市 | 浙江省>嘉興市
  5 | 湖州市 | 浙江省>湖州市
 12 | 金華市 | 浙江省>金華市
  8 | 紹興市 | 浙江省>紹興市
  7 | 甯波市 | 浙江省>甯波市
  4 | 杭州市 | 浙江省>杭州市
  3 | 衢州市 | 浙江省>衢州市
 18 | 金東區 | 浙江省>麗水市>金東區
 17 | 餘杭區 | 浙江省>杭州市>餘杭區
 14 | 上城區 | 浙江省>杭州市>上城區
 15 | 下城區 | 浙江省>杭州市>下城區
 16 | 拱墅區 | 浙江省>杭州市>拱墅區
(17 rows)           

引用

[1]

https://gpdb.docs.pivotal.io/6-0/relnotes/gpdb-60-release-notes.html

[2]

https://gpdb.docs.pivotal.io/6-0/admin_guide/query/topics/CTE-query.html

[3]

https://blog.csdn.net/zengshaotao/article/details/84753796

(版權不明，網際網路上可找到的發表最早的一篇)