深度解析PostgreSQL数据库的树形结构递归查询

发表时间: 2020-01-02 00:04

概述

我们平时在处理不确定深度的层级结构,比如组织机构,一个常用的设计是在一张表里面保存 ID 和 Parent_ID ,并且通过自联结的办法构造一颗树。这种方式对写数据的过程很友好,但是查询过程就变得相对复杂。在不引入MPTT模型的前提下,必须通过递归算法来查询某个节点和下级子节点。

Oracle提供的connect by扩展语法,简单好用。那么,PG又是怎么实现呢?


一、CTE

1、with语句

WITH语句通常被称为通用表表达式(Common Table Expressions)或者CTEs。
WITH语句作为一个辅助语句依附于主语句,WITH语句和主语句都可以是SELECT,INSERT,UPDATE,DELETE中的任何一种语句。

WITH语句最基本的功能是把复杂查询语句拆分成多个简单的部分,如下例所示

WITH regional_sales AS (  SELECT region, SUM(amount) AS total_sales  FROM orders  GROUP BY region), top_regions AS (  SELECT region  FROM regional_sales  WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)SELECT  region,  product,  SUM(quantity) AS product_units,  SUM(amount) AS product_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;

该例中,定义了两个WITH辅助语句,regional_sales和top_regions。前者算出每个区域的总销售量,后者了查出所有销售量占所有地区总销售里10%以上的区域。主语句通过将这个CTEs及订单表关联,算出了顶级区域每件商品的销售量和销售额。

2、WITH RECURSIVE
WITH语句还可以通过增加RECURSIVE修饰符来引入它自己,从而实现递归

WITH RECURSIVE一般用于处理逻辑上层次化或树状结构的数据,典型的使用场景是寻找直接及间接子结点。

严格来讲,这个过程实现上是一个迭代的过程而非递归,不过RECURSIVE这个关键词是SQL标准委员会定立的,所以PostgreSQL也延用了RECURSIVE这一关键词。



二、实验模拟

1、准备环境

create table tree_data ( id integer, code text, pid integer, sort integer); insert into tree_data values(1, '中国', null, 1);insert into tree_data values(2, '四川', 1, 1);insert into tree_data values(3, '云南', 1, 2);insert into tree_data values(4, '成都', 2, 1);insert into tree_data values(5, '绵阳', 2, 2);   insert into tree_data values(6, '武侯区', 4, 1);insert into tree_data values(7, '昆明', 3, 1);   

2、connectby函数
如果安装了 tablefunc 扩展,就可以使用PG版本的connectby函数。这个没有Oracle那么强大,但是可以满足基本要求。

-- API 如下connectby(text relname,             -- 表名称  text keyid_fld,           -- id字段  text parent_keyid_fld     -- 父id字段      [, text orderby_fld ],    -- 排序字段  text start_with,          -- 起始行的id值  int max_depth             -- 树深度,0表示无限  [, text branch_delim ])   -- 路径分隔符-- 基本用法如下,必须通过AS子句定义返回的字段名称和类型select *     from connectby('demo.tree_data', 'id', 'pid', 'sort', '1', 0, '~')    as (id int, pid int, lvl int, branch text, sort int);

PS:虽然通过join可以查询出节点的code,但是branch部分不能直接转换成对应的code,使用上还是不太方便。


3、CTE语法

使用CTE语法,通过 with recursive 来实现树形数据的递归查询。这个方法虽然没有connectby那么直接,但是灵活性和显示效果更好。

with recursive cte as( -- 先查询root节点  select id, code, pid, '' as pcode, code as branch from tree_data where id = 1 union all -- 通过cte递归查询root节点的直接子节点  select origin.id, origin.code, cte.id as pid, cte.code as pcode, cte.branch || '~' || origin.code from cte join tree_data as origin on origin.pid = cte.id)select id,code, pid, pcode, branch,  -- 通过计算分隔符的个数,模拟计算出树形的深度 (length(branch)-length(replace(branch, '~', ''))) as lvlfrom cte; 

执行过程说明

从上面的例子可以看出,WITH RECURSIVE语句包含了两个部分

  • non-recursive term(非递归部分),即上例中的union all前面部分
  • recursive term(递归部分),即上例中union all后面部分

执行步骤如下:

  • 执行non-recursive term。(如果使用的是union而非union all,则需对结果去重)其结果作为recursive term中对result的引用,同时将这部分结果放入临时的working table中
  • 重复执行如下步骤,直到working table为空:用working table的内容替换递归的自引用,执行recursive term,(如果使用union而非union all,去除重复数据),并用该结果(如果使用union而非union all,则是去重后的结果)替换working table

觉得有用的朋友多帮忙转发哦!后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~