如何通过SQL窗口函数实现包含父级分类汇总的层级结构报表？-SQL-PHP中文网

SUM() OVER() 不能直接跨层级求父级汇总，因其窗口函数仅按 PARTITION BY 和 ORDER BY 定义的逻辑帧计算，不理解树形层级；需先用递归CTE或自连接补全祖先路径（如 root_id），再基于该字段 PARTITION BY 实现正确汇总。

如何通过sql窗口函数实现包含父级分类汇总的层级结构报表？

为什么 `SUM() OVER()` 不能直接跨层级求父级汇总

窗口函数默认按 ORDER BY 和 PARTITION BY 定义的逻辑帧计算，它天然不理解树形层级关系。比如你有三级分类（一级→二级→三级），想在每行显示“所属一级分类的总销售额”，SUM(sales) OVER(PARTITION BY level1_id) 看似可行，但前提是数据里必须显式存有 level1_id 字段——而真实业务表往往只存直接父ID（parent_id），没有预先展开的祖先路径。

常见错误现象：NULL 值大量出现、汇总值重复累加、某级分类总和对不上手工核对结果。

必须先通过递归 CTE 或自连接把每条记录的顶层分类 ID 补全，再用 SUM() OVER(PARTITION BY top_level_id)
MySQL 8.0+、PostgreSQL、SQL Server 2017+ 支持递归 CTE；旧版 MySQL 需用自连接模拟（最多支持固定深度）
避免在窗口函数中嵌套子查询来查父级汇总——这会退化为相关子查询，性能极差，N 行 × N 次 IO

用递归 CTE 构建完整祖先路径（以 PostgreSQL 为例）

核心是把原始的 category_id → parent_id 关系，逐层向上追溯，直到 parent_id IS NULL，并把根 ID 记为 root_id。

WITH RECURSIVE category_tree AS (
  -- 基础：所有叶子/中间节点
  SELECT id, name, parent_id, id AS root_id, 0 AS depth
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  -- 递归：找每个节点的子节点，并继承其 root_id
  SELECT c.id, c.name, c.parent_id, ct.root_id, ct.depth + 1
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT 
  c.id,
  c.name,
  c.root_id,
  s.sales_amount,
  SUM(s.sales_amount) OVER(PARTITION BY c.root_id) AS root_total_sales
FROM category_tree c
JOIN sales_summary s ON c.id = s.category_id;

注意：UNION ALL 不去重，确保父子链完整；若存在环（如 A→B→A），需加 depth 限制防死循环。

`GROUPING SETS` 和窗口函数混用时的层级对齐陷阱

当报表既要明细行，又要小计行（如每级分类合计），有人会倾向用 GROUPING SETS + 窗口函数。但要注意：窗口函数在 GROUP BY 后执行，若分组粒度比目标父级更细（例如按三级分类分组，却想算一级汇总），SUM() OVER(PARTITION BY level1_id) 仍有效；但若用了 ROLLUP，生成的 NULL 分组线会让 PARTITION BY 行为异常——它会把所有 NULL 归为同一组。

PostgreSQL 18.4 ubuntu

PostgreSQL 18.4 官方 Ubuntu 安装包现已发布，这是目前最新的稳定版本。推荐通过官方 APT 仓库安装：先执行 sudo apt update 更新索引，再运行 sudo apt install postgresql-18 即可完成部署。新版本引入了异步 I/O 子系统，在顺序扫描与 VACUUM 场景下性能提升显著，同时支持 UUID v7 原生生成函数与虚拟生成列。

下载

安全做法：先用 CTE 展平层级（补 root_id、level1_id 等字段），再在最终 SELECT 中用窗口函数
避免在 GROUPING SETS 的主查询里直接 PARTITION BY GROUPING_ID()——语义不清，各数据库行为不一致
Oracle 用户注意：CONNECT BY 的 LEVEL 伪列不能直接用于 OVER() 的 PARTITION BY，需先转成普通列

性能敏感点：为什么索引要覆盖 `parent_id` 和 `id`

递归 CTE 的性能瓶颈几乎全在 JOIN 效率上。每次递归都需根据子节点的 parent_id 查父节点的 id，若没索引，就是全表扫描 × 递归深度。

实操建议：

在 categories(parent_id, id) 上建联合索引（顺序不能反，因为查询条件是 WHERE parent_id = ?）
PostgreSQL 还可考虑 INDEX CONCURRENTLY 避免锁表；SQL Server 注意统计信息是否及时更新
如果层级深（>10 层）且查询频繁，不如物化祖先路径到宽表（如加字段 ancestors_path TEXT CHECK (ancestors_path ~ '^(\d+,)*\d+$')），用字符串函数提取根 ID

最易被忽略的是：递归结果集默认无序，若后续依赖顺序（比如只取每层第一个节点），必须显式加 ORDER BY —— 而这个 ORDER BY 无法下推到递归内部，可能触发额外排序。