递归 CTE 的适用边界
自引用层级（组织、类目、账户）可以用递归 CTE 从根节点逐层展开到叶子，PostgreSQL、Hive 与 Spark SQL 都支持 WITH RECURSIVE 语法。它适合中小规模层级，结果常用来生成路径列、导出层级视图或校验孤儿节点与环路。
基本信息
| 语法 | WITH RECURSIVE，PostgreSQL、Hive、Spark SQL 均支持 |
|---|---|
| 语句结构 | 锚定查询取根节点，递归部分关联子节点 |
| 常见用途 | 展开层级、找根节点、检测环路、生成路径列 |
| 深度保护 | 限制层级深度，防止脏数据导致无限递归 |
| 规模上限 | 节点多时性能明显下降，需要物化 |
典型使用场景
- 组织树展开：从集团根节点展开到部门与小组
- 类目层级：生成三级类目路径供报表下钻
- 账户结构：展开主账户下的全部子账户
- 数据校验：找出没有父节点的孤儿记录
- 路径物化：把递归结果写入路径列，后续查询不再递归
递归展开并生成路径
递归展开并生成路径
-- 递归展开组织树，同时生成路径与层级
WITH RECURSIVE tree AS (
SELECT org_id, parent_id, 1 AS lvl,
cast(org_id AS string) AS path
FROM dim_org
WHERE parent_id IS NULL
UNION ALL
SELECT o.org_id, o.parent_id, t.lvl + 1,
concat(t.path, '/', o.org_id)
FROM dim_org o
JOIN tree t ON o.parent_id = t.org_id
WHERE t.lvl < 10
)
SELECT org_id, lvl, path FROM tree;各环节作用
| 环节 | 作用 | 注意事项 |
|---|---|---|
| 锚定查询 | 取根节点作为起点 | 根节点判定条件要唯一 |
| 递归部分 | 按父子关系向下展开 | 关联字段要建索引 |
| 深度限制 | 防止无限递归 | 脏数据成环时尤其重要 |
| 输出结果 | 层级、路径与节点清单 | 只读结果，不直接更新维表 |
环路要提前检测：层级表出现 A 是 B 的父、B 又是 A 的父时，递归会一直展开直到资源耗尽，因此递归语句必须带深度上限。
层级规模大时物化路径列或闭包表：每次查询都现场递归会让响应时间随深度增长，物化后按前缀过滤即可完成子树查询。
递归结果建议物化成按天分区的层级快照：既能做历史比对，也避免每次查询都重新递归，代价是每天多一份存储。