pg数据库 递归查询 树形 with recursive

场景

• 保险公司有全国保司，下属的省份机构、地市机构，甚至是区县机构。在生产库中使用 ID 和上级 ID 来实现这种层级关系。此时需要制作保司维表方便分析时，需要变更为某全国保司下的各省份、地市、区县机构的垂直扁平化结构
• 做票据OCR识别时，会对图片A分步骤处理。每个步骤产生一张图片，分别为 A1,A2,A3，再对产生的A1再做处理生成A11,A12等图片。这样不断细化处理，以提高识别的准确度。此时会形成一种类似树形的结构，此时需要统计深度超过3的图片有哪些（N 度搜索）
• pg数据库中有一张A表且在表上创建了视图B，当想要更新A 表中字段类型时，就会报错存在依赖性视图，无法变更。更有可能在视图B上面又创建了嵌套视图C

方法

在pg语法中使用with recursive语法满足上述场景。官网有详细语法解释。

with 语法本身可以用来构建一个公共表达式，这个表达式可以当做临时表一样被查询。加上recursive关键字就可以引用它自己的输出，直到条件结束，这就实现了SQL语法中的递归函数。

具体了解下 with recursive ... 语法使用，官方文档有个例子

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

/*
详细解读下上面SQL。这是将1到100的数值相加的一段递归SQL

1. WITH RECURSIVE 子句：
    这表明要定义一个递归的公共表表达式（CTE Common Table Expression）。

2. t(n) AS (...):
    定义了一个名为't'的CTE，它有一个列名为'n'。

3. 基础部分（非递归部分）：
    VALUES (1)
    这是递归的起点，它插入值1作为初始行。

4. UNION ALL:
    这个关键字将基础部分和递归部分的结果合并。也可以使用UNION，那么结果会合并去重。

5. 递归部分：
    SELECT n+1 FROM t WHERE n < 100
    从't'表中选择'n'值
    将'n'加1
    只有当'n'小于100时才继续递归

6. 递归过程：
    第1次迭代：n = 1
    第2次迭代：n = 2
    第3次迭代：n = 3
    ...
    第99次迭代：n = 99
    第100次迭代：n = 100
    当n = 100时，WHERE条件不再满足，递归停止

7. SELECT sum(n) FROM t:
    从生成的't'表中选择所有'n'值，这里的't'表就是刚刚定义的 CTE
    对这些值求和

*/

回归到场景实现

想要实现扁平化结构、深度查询、自身关联都可以使用这种结构，举例如下

-- 查询深度超过 2 的票据
with recursive cte(id, url, parent_img_id, level) as ( --定义一个表名为 cte 的递归公共表达式，后面可以被当做表名一样查询，其有4个字段id, url, parent_img_id, level
	select id, url, cut_f_id as parent_img_id, 1 as level from ods.img where cut_f_id is null --定义非递归查询，起初深度 level 写死 1
	union all
	select t.id, t.url, t.cut_f_id as parent_img_id, cte.level + 1 as level from ods.img t join cte on t.cut_f_id = cte.id --定义递归查询部分，每次 票据id 和 父票据id 关联上则深度level+1
)
select * from cte where cte.level > 2 --从公共表达式中查询深度大于 2 的记录

\

--查询扁平化结构
/* 创建测试表temp_recur，表内容
id    fid    name    level
1             A        1
2      1      A1       2
3      1      A2       2
4      1      A3       2
5      2      A4       3
6      3      A5       3
7      3      A6       3
8      3      A7       3
*/
WITH RECURSIVE tree(id, fid, lvl1_name, lvl2_name, lvl3_name, level) AS (
    -- 基础查询：选择顶级项目（fid 为 null 的项目）
    SELECT id, fid, name, name, name, level FROM dqs.temp_recur WHERE fid IS NULL
    UNION ALL
    -- 递归部分：选择子项目
    SELECT t.id, t.fid,
           CASE WHEN t.level = '2' THEN tree.lvl1_name ELSE tree.lvl1_name END,
           CASE WHEN t.level = '2' THEN t.name ELSE tree.lvl2_name END,
           CASE WHEN t.level = '2' then t.name when t.level = '3' THEN t.name ELSE tree.lvl3_name END,
           t.level
    FROM dqs.temp_recur t
    JOIN tree ON t.fid = tree.id
    WHERE t.level IN ('2', '3')
)
SELECT * FROM tree order by 1

\

结论

用SQL递归公共表达式来实现普通递归场景是很方面的，但是有利也有弊

• SQL 的性能优势在于数据库引擎的优化，特别是处理大量数据时。
• 语言的优势在于可以实现更复杂的算法和控制流程。当数据中有很复杂的处理逻辑时，用语言更有优势。

对于简单的递归问题,使用 SQL 的递归公共表表达式(CTE)通常会更快、更简洁。SQL 的递归 CTE 可以高效地生成数据序列。

类似的还有最短路径计算，可以参考德哥博客