SQL如何处理多级分类统计报表_窗口函数层级汇总技巧

ROLLUP生成的NULL是层级聚合占位符而非空值，需用GROUPING()函数区分；窗口函数不能替代GROUP BY，须先聚合再计算；递归CTE适用于动态树形结构；导出时应统一处理NULL以避免前端解析异常。GROUP BY + ROLLUP 能直接出多级汇总，但要注意 NULL 含义SQL 多级分类统计最常踩的坑，是以为 ROLLUP 生成的 NULL 就是“空值”，其实它是层级聚合的占位符。比如按 region → city → store 三级分组，ROLLUP(region, city, store) 会产出 4 层结果：明细行、store 小计、city 小计、region 小计，每层缺失维度用 NULL 标识。实操建议：用 GROUPING() 函数区分真 NULL 和聚合占位符，例如 GROUPING(city) = 1 表示该行是 city 级汇总（city 列值为 NULL，但不是数据缺失）别直接 WHERE city IS NOT NULL 过滤——会把 city 小计也干掉ROLLUP 在 MySQL 8.0+、PostgreSQL 9.5+、SQL Server 全支持，但 SQLite 不支持，Oracle 需用 GROUPING SETS窗口函数不能替代 GROUP BY，但能补全层级占比和排名有人想用 SUM(sales) OVER (PARTITION BY region) 直接算大区占比，却发现结果行数没变——对，窗口函数不压缩行，它是在原表每行上追加计算列。真正做多级报表，得先 GROUP BY 得到聚合结果集，再套窗口函数加工。典型场景：在三级分组结果（region/city/store）上，算每个 store 占所属 city 的比例：用 SUM(sales) OVER (PARTITION BY region, city) 做分母给每个 city 内的 store 按销售额排名：RANK() OVER (PARTITION BY region, city ORDER BY sales DESC)注意 ORDER BY 在窗口定义里影响排序逻辑，但不会改变输出行顺序——最终排序还得靠外层 ORDER BY递归 CTE 解决动态层级（如无限级类目树），但性能要盯死如果分类是树形结构（比如电商类目：电子 > 手机 > 苹果 > iPhone 15），且层级深度不确定，ROLLUP 就失效了——它只认固定列数。这时得用递归 CTE 把树摊平成“路径+层级”二维表，再聚合。关键点：递归部分必须有终止条件，常见写法是 WHERE parent_id != id 或限制最大深度（LEVEL <= 5）防止死循环PostgreSQL 用 WITH RECURSIVE，MySQL 8.0+ 支持，SQL Server 用 WITH + UNION ALL，旧版 MySQL 只能靠应用层拼树越深、节点越多，递归中间结果集越大；建议在 category 表的 parent_id 和 id 上建索引报表导出时 NULL 和空字符串混用，前端解析容易崩SQL 层聚合后，经常要导出 CSV 或喂给 BI 工具。这时候 ROLLUP 产生的 NULL、字段默认值 ''、以及真实业务空数据，在 Excel 或 Tableau 里可能全显示成空白，但底层类型不同，排序/过滤行为不一致。稳妥做法：用 COALESCE(region, '[全部]') 统一替换聚合行标识，比留 NULL 更直观避免在聚合字段上用 IFNULL 或 ISNULL 直接转空字符串——这会让 GROUPING() 失效，无法区分层级导出前加个 SELECT ... FROM (...) AS t ORDER BY GROUPING(region) DESC, region, ...，确保汇总行排在最上面或最下面层级深、维度多、数据量大时，ROLLUP 和递归 CTE 的执行计划很容易飘，上线前一定用真实数据集 explain 一下；临时表或物化视图不是银弹，但比每次跑全量递归靠谱。