PostgreSQL 务实应用(一/5)树形层级

时间:2022-12-13 00:27:58

项目中,经常会碰到多级的树形结构数据,如地区信息,省、市、区、街道等,或客户关系信息上三级,下三级等。

实际项目中,我们可能碰到以下两种需求:

  • 一条记录中呈现路径:省 - 市 - 区 - 街道
  • 一条记录中呈现上几级:上级,上上级,上上上级

此情此景,使用 WITH RECURSIVE 递归查询再合适不过。

递归查询

在介绍 WITH RECURSIVE 之前,我们先介绍一个 WITH

通过 WITH ,我们可以在写查询语句之前,为一个需要用到的子查询定义一个临时的别名,该别名可以查询语句中使用,从而简化查询语句。如下示例:

WITH 临时别名 AS(
    SELECT 1 a, 2 b, 3 c  
)
-- 下边是查询语句,像使用表或视图一样使用了“临时别名”
select a * a, b * b, c * c from 临时别名;

接下来我们再来看递归 RECURSIVE ,我们知道,递归形式上就是自己会调用自己,对于数据集而言,递归通常需要两个条件:

  1. 有入口数据,即递归开始的数据记录
  2. 有递归结束的条件,即在关联自己的同时有终止的条件

这第 2 点,原因很简单,关联自己产生的新数据记录又会成为递归关联中的数据。通常 WHERE 子句给出这个条件。

PostgreSQL 中,WITH RECURSIVE 即表示递归查询,“临时别名”在定义自身的子查询体中也可以使用。

我们来看一个示例:

-- 定义了一个临时别名 cet
WITH RECURSIVE cet AS (
  -- 递归初始的数据,id=1, name="name 1", pid=null (最顶层父id为空) 
  SELECT 1 AS id, 'name 1' AS name, cast(null AS int) AS pid
  UNION ALL
  -- 联合 cet 自己,id 递增,结束条件为 id < 10。如果不加这个 WHERE 会如何?
  SELECT id+1, 'name ' || (id+1), id FROM cet WHERE id < 10
)
SELECT * FROM cet;

输出以下结果:

PostgreSQL 务实应用(一/5)树形层级

搞定需求

上边的示例中的结果集就是一个典型的树形层级结构,我们以这个数据结果为例来完成文章开头提到的两个需求,首先我们把这个结果集变成一个视图以便当源数据用。

-- 创建一个名字为 view_tree_test 的视图
CREATE VIEW view_tree_test as
WITH RECURSIVE cet AS (
    SELECT 1 AS id, 'name 1' AS name, cast(null AS int) AS pid
  UNION ALL
    SELECT id+1, 'name ' || (id+1), id FROM cet WHERE id < 10
)
SELECT * FROM cet;

我们以 view_tree_test 作为数据源来完成两大需求。

需求一,显示路径

WITH RECURSIVE tpath AS(
    SELECT id, name, pid, name as path from view_tree_test where pid is null
    UNION ALL
    -- 联合子节点
    SELECT a.id, a.name, a.pid, 
           tpath.path || '-' || a.name  -- 父路径拼接当前节点名称形成路径
      FROM view_tree_test a, tpath      -- 关联已有结果,查询其子节点
     WHERE a.pid = tpath.id
) 
SELECT * FROM tpath;

查询结果可见其效果:

PostgreSQL 务实应用(一/5)树形层级

需求二,显示每第记录的上三级

WITH RECURSIVE uuup AS(
    -- 初始值,顶层的上级均置为空,需要几级置几个空
    SELECT id, name, '' 上级, '' 上上级, '' 上上上级 from view_tree_test where pid is null
    UNION ALL
    -- 联合子节点
    SELECT a.id, a.name, 
           -- 父级即为上级,父的上级为上上级
           uuup.name 上级, uuup.上级 上上级, uuup.上上级 上上上级
      FROM view_tree_test a, uuup      -- 关联已有结果,查询其子节点
     WHERE a.pid = uuup.id
) 
SELECT * FROM uuup;

顺利呈现相关的上三级,需要更多上级也是 SO EASY!

PostgreSQL 务实应用(一/5)树形层级

小结

PostgreSQL 提供了相当多实用的数据处理方式,让数据处理起来很是便捷。树形结构是应用中经常会使用到,使用递归查询能方便的处理跨层级的计算,还有很多想象空间哦。