PostgreSQL Oracle 相容性 - connect by 2

标簽

PostgreSQL , Oracle , 樹形查詢 , 遞歸 , connect by , tablefunc , connectby

https://github.com/digoal/blog/blob/master/201807/20180714_03.md#%E8%83%8C%E6%99%AF 背景

Oracle connect by文法經常用于有樹形關系的記錄查詢，PostgreSQL使用CTE遞歸文法，可以實作同樣的功能。

《PostgreSQL Oracle 相容性之 - connect by 進階選項 CONNECT_BY_ISLEAF、SYS_CONNECT_BY_PATH、CONNECT_BY_ISCYCLE、LEVEL》 《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)

https://github.com/digoal/blog/blob/master/201807/20180714_03.md#%E4%BE%8B%E5%AD%90 例子

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)  
           

https://github.com/digoal/blog/blob/master/201807/20180714_03.md#%E5%8F%82%E8%80%83 參考

《PostgreSQL 家譜、族譜類應用實踐 - 圖式關系存儲與搜尋》 《PostgreSQL 遞歸妙用案例 - 分組資料去重與打散》 《PostgreSQL Oracle 相容性之 - INDEX SKIP SCAN (遞歸查詢變态優化) 非驅動列索引掃描優化》 《PostgreSQL 圖式搜尋(graph search)實踐 - 百億級圖譜，毫秒響應》 《[未完待續] AgensGraph 圖資料庫介紹以及 on ECS部署》 《PostgreSQL 實踐 - 内容社群(如論壇)圖式搜尋應用》 《[未完待續] 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 遞歸死循環案例及解法》 《PostgreSQL 遞歸查詢一例 - 資金累加鍊》 《PostgreSQL Oracle 相容性之 - WITH 遞歸 ( connect by )》 《遞歸優化CASE - group by & distinct tuning case : use WITH RECURSIVE and min() function》 《遞歸優化CASE - performance tuning case :use cursor\trigger\recursive replace (group by and order by) REDUCE needed blockes scan》 《PostgreSQL 樹狀資料存儲與查詢(非遞歸) - Use ltree extension deal tree-like data type》