标签
PostgreSQL , Oracle , 树形查询 , 递归 , connect by , tablefunc , connectby
背景
Oracle connect by语法经常用于有树形关系的记录查询，PostgreSQL使用CTE递归语法，可以实现同样的功能。
《PostgreSQL Oracle 兼容性之 - connect by》
本文通过一个更加简单的方法，同样可以实现一样的功能，用到tablefunc插件中的connectby函数。
接口如下
https://www.postgresql.org/docs/devel/static/tablefunc.html
connectby(text relname, text keyid_fld, text parent_keyid_fld
[, text orderby_fld ], text start_with, int max_depth
[, text branch_delim ])
setof record
Produces a representation of a hierarchical tree structure
| Parameter | Description |
|---|---|
| relname | Name of the source relation |
| keyid_fld | Name of the key field |
| parent_keyid_fld | Name of the parent-key field |
| orderby_fld | Name of the field to order siblings by (optional) |
| start_with | Key value of the row to start at |
| max_depth | Maximum depth to descend to, or zero for unlimited depth |
| branch_delim | String to separate keys with in branch output (optional) |
例子
create extension tablefunc;
CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);
INSERT INTO connectby_tree VALUES('row1',NULL, 0);
INSERT INTO connectby_tree VALUES('row2','row1', 0);
INSERT INTO connectby_tree VALUES('row3','row1', 0);
INSERT INTO connectby_tree VALUES('row4','row2', 1);
INSERT INTO connectby_tree VALUES('row5','row2', 0);
INSERT INTO connectby_tree VALUES('row6','row4', 0);
INSERT INTO connectby_tree VALUES('row7','row3', 0);
INSERT INTO connectby_tree VALUES('row8','row6', 0);
INSERT INTO connectby_tree VALUES('row9','row5', 0);
-- with branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text);
keyid | parent_keyid | level | branch
-------+--------------+-------+---------------------
row2 | | 0 | row2
row4 | row2 | 1 | row2~row4
row6 | row4 | 2 | row2~row4~row6
row8 | row6 | 3 | row2~row4~row6~row8
row5 | row2 | 1 | row2~row5
row9 | row5 | 2 | row2~row5~row9
(6 rows)
-- without branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
AS t(keyid text, parent_keyid text, level int);
keyid | parent_keyid | level
-------+--------------+-------
row2 | | 0
row4 | row2 | 1
row6 | row4 | 2
row8 | row6 | 3
row5 | row2 | 1
row9 | row5 | 2
(6 rows)
-- with branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text, pos int);
keyid | parent_keyid | level | branch | pos
-------+--------------+-------+---------------------+-----
row2 | | 0 | row2 | 1
row5 | row2 | 1 | row2~row5 | 2
row9 | row5 | 2 | row2~row5~row9 | 3
row4 | row2 | 1 | row2~row4 | 4
row6 | row4 | 2 | row2~row4~row6 | 5
row8 | row6 | 3 | row2~row4~row6~row8 | 6
(6 rows)
-- without branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
AS t(keyid text, parent_keyid text, level int, pos int);
keyid | parent_keyid | level | pos
-------+--------------+-------+-----
row2 | | 0 | 1
row5 | row2 | 1 | 2
row9 | row5 | 2 | 3
row4 | row2 | 1 | 4
row6 | row4 | 2 | 5
row8 | row6 | 3 | 6
(6 rows)
参考
《PostgreSQL 家谱、族谱类应用实践 - 图式关系存储与搜索》
《PostgreSQL 递归妙用案例 - 分组数据去重与打散》
《PostgreSQL Oracle 兼容性之 - INDEX SKIP SCAN (递归查询变态优化) 非驱动列索引扫描优化》
《PostgreSQL 图式搜索(graph search)实践 - 百亿级图谱，毫秒响应》
《[未完待续] AgensGraph 图数据库介绍以及 on ECS部署》
《PostgreSQL 实践 - 内容社区(如论坛)图式搜索应用》
《小微贷款、天使投资(风控助手)业务数据库设计(图式搜索\图谱分析) - 阿里云RDS PostgreSQL, HybridDB for PostgreSQL最佳实践》
《PostgrSQL 递归SQL的几个应用 - 极客与正常人的思维》
《PostgreSQL 递归查询CASE - 树型路径分组输出》
《金融风控、公安刑侦、社会关系、人脉分析等需求分析与数据库实现 - PostgreSQL图数据库场景应用》
《用PostgreSQL找回618秒逝去的青春 - 递归收敛优化》
《distinct xx和count(distinct xx)的变态递归优化方法 - 索引收敛(skip scan)扫描》
《时序数据合并场景加速分析和实现 - 复合索引，窗口分组查询加速，变态递归加速》
《facebook linkbench 测试PostgreSQL社交关系图谱场景性能》
《PostgreSQL 使用递归SQL 找出数据库对象之间的依赖关系》
《PostgreSQL Oracle 兼容性之 - WITH 递归 ( connect by )》
《递归优化CASE - group by & distinct tuning case : use WITH RECURSIVE and min() function》
《PostgreSQL 树状数据存储与查询(非递归) - Use ltree extension deal tree-like data type》
