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 (版权不明，互联网上可找到的发表最早的一篇)