树形结构建模
组织架构、商品类目、行政区划这类树形结构在维度里有两种表达方式。一是自关联递归：维表存 parent_key，查询时通过递归 CTE 或迭代逐层展开，改动成本低但查询代价高。二是物化层次路径：把 l1/l2/l3 或路径串直接写进维表，分析时可任意按层聚合，代价是深度变化要改表结构。选择依据是树深是否稳定、查询是否高频、成员量级多大。
基本信息
| 递归方式 | parent_key 自关联，逐层展开 |
|---|---|
| 物化方式 | 层次路径列 l1/l2/l3 或 path 串 |
| 权衡要点 | 深度变化频繁程度对查询便利性的取舍 |
| 适用引擎 | 支持递归 CTE 或迭代语法的分析引擎均可 |
典型使用场景
- 组织架构分析：人员归属沿部门树逐级汇总，需要按任意层级出报表
- 类目销售统计：一级类目看大盘、三级类目看细节，靠路径列快速切片
- 区域层级口径：省市区三级固定，物化路径后无需递归即可下钻
- 深度不定的树：无限级类目必须用递归或闭包表表达，硬编码层级列会丢节点
- 血缘与依赖分析：任务依赖关系本质是树，常用递归方式逐层追溯
两种表达对照
两种表达对照
-- 方式一：物化路径列，深度稳定的树首选
-- dim_category(cat_id, parent_id, l1_id, l2_id, l3_id, path)
SELECT l1_id, sum(amt) AS amt
FROM dim_category c
JOIN fact_sale f ON f.cat_id = c.cat_id
GROUP BY 1; -- 无需递归，直接按第一层聚合
-- 方式二：查询某三级类目下的全部叶子（路径前缀匹配）
SELECT cat_id FROM dim_category WHERE path LIKE '1/12/103/%';两种方式对照
| 判断项 | 物化路径列 | parent 递归 |
|---|---|---|
| 树深稳定性 | 深度固定或变动少 | 深度不定、可无限级 |
| 查询性能 | 一次扫描即可聚合 | 逐层展开，代价随深度上升 |
| 维护成本 | 层级调整需回刷路径列 | 只改 parent_id |
| 使用者友好度 | 业务方直接写 SQL | 需要理解递归写法 |
深度稳定的树优先物化路径，深度不定的树用递归或闭包表。判断是否稳定不需要争论，先跑一次实际深度分布统计，用数据说话。
路径串格式必须定规范并写进文档，比如用斜杠分隔且首尾带分隔符，否则 LIKE 前缀匹配会把 1/12 与 1/120 混为一谈。