不使用ROLLUP关键字如何模拟实现二维数据透视表SQL聚合查询

双ROLLUP实现二维透视的本质，是行维度所有分组层级和列维度所有分组层级的笛卡尔积组合，和一维ROLLUP用UNION ALL拼接不同层级查询的逻辑完全一致，只需要把两个维度的所有分组层级两两配对，分别写分组查询再拼接即可。

核心逻辑 #

• 枚举行维度ROLLUP的全部分组粒度：n个行维度字段共对应n+1种粒度，从最细的全字段分组，逐次去掉最右侧字段做小计，直到所有行字段为NULL的总汇总
• 枚举列维度ROLLUP的全部分组粒度：m个列维度字段共对应m+1种粒度，规则同行维度
• 将两类粒度做笛卡尔积，为每一种组合写独立的分组查询：非当前分组粒度的字段填充NULL，聚合逻辑保持一致
• 所有子查询用UNION ALL拼接，最终返回结果和原生双ROLLUP写法完全等价

代码示例 #

以常见的销售表场景为例，假设需要实现的原生双ROLLUP语句如下（行维度为区域x、门店y，列维度为品类a、商品b，聚合指标为销售额val）：

-- 原生带ROLLUP的二维透视语句（仅作对照用，改写版本不使用该语法）
SELECT x, y, a, b, SUM(val) AS total
FROM sales
GROUP BY ROLLUP(x,y), ROLLUP(a,b);

对应的无ROLLUP改写版本如下，PostgreSQL、MySQL环境均可直接运行：

-- 行粒度：x+y明细，列粒度：a+b明细
SELECT x, y, a, b, SUM(val) AS total FROM sales GROUP BY x,y,a,b
UNION ALL
-- 行粒度：x+y明细，列粒度：a小计
SELECT x, y, a, NULL AS b, SUM(val) AS total FROM sales GROUP BY x,y,a
UNION ALL
-- 行粒度：x+y明细，列粒度：全总计
SELECT x, y, NULL AS a, NULL AS b, SUM(val) AS total FROM sales GROUP BY x,y
UNION ALL
-- 行粒度：x小计，列粒度：a+b明细
SELECT x, NULL AS y, a, b, SUM(val) AS total FROM sales GROUP BY x,a,b
UNION ALL
-- 行粒度：x小计，列粒度：a小计
SELECT x, NULL AS y, a, NULL AS b, SUM(val) AS total FROM sales GROUP BY x,a
UNION ALL
-- 行粒度：x小计，列粒度：全总计
SELECT x, NULL AS y, NULL AS a, NULL AS b, SUM(val) AS total FROM sales GROUP BY x
UNION ALL
-- 行粒度：全总计，列粒度：a+b明细
SELECT NULL AS x, NULL AS y, a, b, SUM(val) AS total FROM sales GROUP BY a,b
UNION ALL
-- 行粒度：全总计，列粒度：a小计
SELECT NULL AS x, NULL AS y, a, NULL AS b, SUM(val) AS total FROM sales GROUP BY a
UNION ALL
-- 行粒度：全总计，列粒度：全总计（全局总销售额）
SELECT NULL AS x, NULL AS y, NULL AS a, NULL AS b, SUM(val) AS total FROM sales;

注意事项 #

• 所有子查询的输出字段顺序、数据类型必须完全一致，非当前分组维度的字段要显式填充对应类型的NULL，不能省略字段
• 分组层级的枚举顺序要和原生ROLLUP逻辑对齐：默认从维度字段列表最右侧开始逐次汇总，如果需要调整小计的计算顺序，对应调整枚举粒度时移除字段的顺序即可
• 该写法需要多次扫描源表，性能比原生ROLLUP语法差，仅在兼容不支持ROLLUP的老旧数据库版本时使用，数据量大的场景优先用数据库原生分组扩展语法

内容的提问来源于stack exchange，提问作者David542