Postgresql兼容Oracle改造系列——START WITH/CONNECT BY

Oracle分层查询

Oracle中START / WITH / CONNECT BY提供分层查询的能力，从START WITH开始遍历记录，递归查询结果集直到拿到所有满足条件的结果。

例如下面测试数据：

drop table sr_menu;
create table sr_menu(  
  id number(10) not null, 
  parent number(10),
  title varchar2(50)
);
insert into sr_menu values (1, null, 'level 0');
insert into sr_menu values (2, 1, 'level 1');
insert into sr_menu values (3, 1, 'level 1');
insert into sr_menu values (4, 1, 'level 1');
insert into sr_menu values (5, 3, 'level 2');
commit;

select * from sr_menu;

        ID     PARENT TITLE                                             
---------- ---------- --------------------------------------------------
         1            level 0                                           
         2          1 level 1                                           
         3          1 level 1                                           
         5          3 level 2                                           
         4          1 level 1  

有这样的逻辑关系：

level 0:          1
            /   /   \   \
level 1:   2   3     4   5
              /
level 2:     5

业务上如果需要查询跟节点1的所有子节点，在Oracle中可以使用以下语法：

select * from sr_menu 
start with id = 1 
connect by prior id = parent;
        ID     PARENT TITLE                                             
---------- ---------- --------------------------------------------------
         1            level 0                                           
         2          1 level 1                                           
         3          1 level 1                                           
         5          3 level 2                                           
         4          1 level 1  

查询时会用上一层的id=1（prior修饰）和当前的parent比较，查询出第二层符合条件的数据：

         2          1 level 1                                           
         3          1 level 1 
         4          1 level 1  

后面继续递归，使用上一层的id=2/3/4去匹配下面的数据，从id=3中得到：

         5          3 level 2                                           

Postgresql分层查询改造

Oracle 分层查询其实是一种递归查询的方式，用第一层查询的结果递归出后一层。在 Postgresql 中可以使用 WITH RECURSIVE 语法实现相同的功能。

普通的 WITH 子句可以实现 CTE 的功能，加上 RECURSIVE 关键字可以进一步在 WITH 内引用自己的输出实现递归，例如对于上面 SQL 的改写，可以实现完全相同的业务逻辑：

WITH RECURSIVE a AS (
SELECT id, parent, title
  FROM sr_menu
  WHERE id = 1
UNION ALL
  SELECT d.id, d.parent, d.title
  FROM sr_menu d
  JOIN a ON a.id = d.parent )
SELECT id, parent, title FROM a;

 id | parent |  title
----+--------+---------
  1 |        | level 0
  2 |      1 | level 1
  3 |      1 | level 1
  4 |      1 | level 1
  5 |      3 | level 2

WITH 内使用 UNION ALL 的第一张对应 START WITH语句，一般是一个固定结果集的查询条件。

UNION ALL的第二张表join ... a.id，表示连接当前 with 子句的查询结果，这样反复递归直到所有数据查询完毕。

从递归深度也可以看出执行过程：

WITH RECURSIVE a AS (
SELECT id, parent, title, 1::integer recursion_level
  FROM sr_menu
  WHERE id = 1
UNION ALL
  SELECT d.id, d.parent, d.title, a.recursion_level +1
  FROM sr_menu d
  JOIN a ON a.id = d.parent )
SELECT * FROM a;

 id | parent |  title  | recursion_level
----+--------+---------+-----------------
  1 |        | level 0 |               1
  2 |      1 | level 1 |               2
  3 |      1 | level 1 |               2
  4 |      1 | level 1 |               2
  5 |      3 | level 2 |               3

有关WITH RECURSIVE

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

递归WITH的执行流程：

1. 计算非递归项（UNION ALL内的固定查询部分，例如上面的VALUES(1)）把结果放在临时表A中

2. 临时表不为空，重复下列步骤：

   • 计算递归项（UNION ALL 内的递归部分），用临时表A当作递归自引用表。查询结果记录到临时表B
   • 用B的数据库覆盖A，清空B。

参考资料

https://www.postgresql.org/docs/11/queries-with.html#QUERIES-WITH-SELECT