学过 SQL 的人都知道，使用聚合函数（AVG、SUM、COUNT、MIN/MAX 等）和分组操作（GROUP BY）可以对数据进行基本的统计分析，例如统计公司员工的人数、每个部门的平均月薪等。
不过，SQL 不仅仅能够进行这些基本的分组汇总，它还提供了许多高级的统计分析功能。本文就来介绍一下如何使用 SQL 实现销售数据的小计、合计、总计以及多维度交叉统计和数据透视表。我们首先列出这些功能在主流数据库中的支持情况：
* 参考下文中的具体讨论。如果没有特殊说明，以下示例适用于上面的 4 种数据库。
小计、合计与总计
我们先查看一下示例表中的数据：
select * from sales_data;saledate |product |channel |amount |----------|---------|--------|-------|2019-01-01|桔子 |淘宝 |1864.00|2019-01-01|桔子 |京东 |1329.00|2019-01-01|桔子 |店面 |1736.00|2019-01-01|香蕉 |淘宝 |1573.00|2019-01-01|香蕉 |京东 |1364.00|2019-01-01|香蕉 |店面 |1178.00|2019-01-01|苹果 |淘宝 | 511.00|2019-01-01|苹果 |京东 | 568.00|2019-01-01|苹果 |店面 | 847.00|...
这是一个模拟的销售数据，记录了不同日期（2019-01-01 到 2019-06-30）、三种不同产品、三种不同渠道的销量情况。
以下语句使用 GROUP BY 统计了三种不同产品各自的总销量：
select product, sum(amount)from sales_datagroup by product;product |sum(amount)|---------|-----------|桔子 | 909261.00|香蕉 | 925369.00|苹果 | 937052.00|
显然，还可以编写 SQL 语句统计三种不同产品在不同渠道各自的销量合计、所以产品的销量总计等。但是如何一次获取这些按照不同维度进行统计的结果呢？我们可以使用 GROUP BY 的第一个扩展选项：ROLLUP。例如：
-- Oracle、SQL Server、PostgreSQLselect product "产品", channel "渠道", sum(amount) "销量"from sales_datagroup by rollup (product, channel);产品|渠道 |销量 |----|----|----------|桔子|店面 | 294680.00|桔子|京东 | 311799.00|桔子|淘宝 | 302782.00|桔子|NULL| 909261.00|苹果|店面 | 306643.00|苹果|京东 | 318614.00|苹果|淘宝 | 311795.00|苹果|NULL| 937052.00|香蕉|店面 | 311445.00|香蕉|京东 | 306033.00|香蕉|淘宝 | 307891.00|香蕉|NULL| 925369.00|NULL|NULL|2771682.00|
其中，ROLLUP 表示先按照 (product, channel) 的组合计算不同产品、不同渠道的销量小计，然后按照计算不同产品（product）、所有渠道的销量合计（结果中的 channel 字段显示为 NULL），最后计算所有产品、所有渠道的销量总计（结果中的 product 和 channel 字段都为 NULL）。
如果使用 MySQL 数据库，ROLLUP 的使用略有不同：
-- MySQLselect product, channel, sum(amount)from sales_datagroup by product, channel with rollup;
MySQL 在分组字段之后使用 with rollup 选项，查询的结果与其他数据库相同。
ROLLUP 选项可以使用 UNION 合并多个查询结果进行模拟：
with d as (select product, channel , sum(amount) amountfrom sales_datagroup by product, channel)select product "产品", channel "渠道", amount "销量" from dunion allselect product, NULL, sum(amount) from d group by productunion allselect NULL, NULL, sum(amount) from d;
其中，WITH 表示定义通用表表达式，类似于临时表；关于通用表表达式的概念可以参考这篇文章。以上语句正好解释了 ROLLUP 选项的作用。
📝GROUP BY 子句的 ROLLUP 选项是一种按照层次从下往上依次汇总的过程，需要汇总 N + 1 个维度，N 是分组字段的个数。
多维度交叉统计
如果我们的销量报表需要统计以下信息：
不同产品、不同渠道的销量小计；
不同产品、所有渠道的销量合计；
所有产品、不同渠道的销量合计；
所有产品、所有渠道的销量总计。
由于ROLLUP 选项是按照分组字段的顺序依次往上汇聚，(product, channel) 无法获取所有产品、不同渠道的销量合计，(channel, product) 又无法获取不同产品、所有渠道的销量合计。虽然可以查询两次然后去除重复结果，但是不方便；况且当我们的分组字段增加到 3 个或者 4 个时，组合情况更多。
为此，我们可以使用 GROUP BY 的第二个扩展选项：CUBE。以下语句可以实现上面的统计需求：
-- Oracle、SQL Server、PostgreSQLselect coalesce(product, '【全部产品】') "产品", coalesce(channel, '【所有渠道】') "渠道", sum(amount) "销量"from sales_datagroup by cube (product,channel)order by product, channel;产品 |渠道 |销量 |-----------|----------|-------|桔子 |京东 | 311799|桔子 |店面 | 294680|桔子 |淘宝 | 302782|桔子 |【所有渠道】| 909261|苹果 |京东 | 318614|苹果 |店面 | 306643|苹果 |淘宝 | 311795|苹果 |【所有渠道】| 937052|香蕉 |京东 | 306033|香蕉 |店面 | 311445|香蕉 |淘宝 | 307891|香蕉 |【所有渠道】| 925369|【全部产品】|京东 | 936446|【全部产品】|店面 | 912768|【全部产品】|淘宝 | 922468|【全部产品】|【所有渠道】|2771682|
为了更好地查看结果，我们使用 coalesce 函数对 NULL 进行了转换显示；CUBE 基于分组字段的任意组合进行汇总，比 ROLLUP 获得的更多维度的统计信息。
MySQL 目前没有提供 CUBE 选项。以上示例中的分组字段只有 2 个，可以使用 UNION 合并多个查询结果进行模拟：
with d as (select product, channel , sum(amount) amountfrom sales_datagroup by product, channel)select product "产品", channel "渠道", amount "销量" from dunion allselect product, NULL, sum(amount) from d group by productunion allselect NULL, channel, sum(amount) from d group by channelunion allselect NULL, NULL, sum(amount) from d;
这种方法正好解释了 CUBE 选项的作用，也适用于其他数据库；但是如果分组字段达到 3 个以上就比较麻烦。
📝GROUP BY 子句的 CUBE 选项是一种按照各种层次组合汇总的过程，需要汇总 2 的 N 次方个维度，N 是分组字段的个数。
自定义统计维度
ROLLUP 和 CUBE 都是按照预定义好的维度进行数据统计，SQL 还提供了第三个 GROUP BY 选项：GROUPING SETS。它允许我们指定自定义的分组集，例如：
GROUP BY ROLLUP (product, channel)实际上等价于：
GROUP BY GROUPING SETS ((product, channel), (product), ())其中，每一个维度都位于一对括号之内。(product, channel) 表示按照产品和渠道的组合进行统计、(product) 表示按照产品进行统计、() 表示所有数据进行统计。
同样，对于 CUBE 选项：
GROUP BY CUBE (product, channel)等价于：
GROUP BY GROUPING SETS ((product, channel), (product), (channel), ())而没有任何选项的 GROUP BY：
GROUP BY product, channel等价于：
GROUP BY GROUPING SETS ((product, channel))注意，(product, channel) 需要使用括号包含；GROUP BY GROUPING SETS (product, channel) 统计的是产品维度的数据和渠道维度的数据。
以下示例用于计算按照季度、产品、渠道以及不同季度不同渠道的销量统计：
-- Oracle、PostgreSQLselect coalesce(to_char(saledate,'Q'),'【半年】') "季度", coalesce(product, '【全部产品】') "产品",coalesce(channel, '【所有渠道】') "渠道", sum(amount) "销量"from sales_datagroup by grouping sets ((to_char(saledate,'Q')), (product), (channel), (to_char(saledate,'Q'), channel));季度 |产品 |渠道 |销量 |--------|-----------|-----------|-------|1 |【全部产品】|京东 | 396027|2 |【全部产品】|京东 | 540419|1 |【全部产品】|店面 | 388885|2 |【全部产品】|店面 | 523883|1 |【全部产品】|淘宝 | 401937|2 |【全部产品】|淘宝 | 520531|【半年】 |【全部产品】|京东 | 936446|【半年】 |【全部产品】|店面 | 912768|【半年】 |【全部产品】|淘宝 | 922468|1 |【全部产品】|【所有渠道】|1186849|2 |【全部产品】|【所有渠道】|1584833|【半年】 |香蕉 |【所有渠道】| 925369|【半年】 |桔子 |【所有渠道】| 909261|【半年】 |苹果 |【所有渠道】| 937052|
我们使用了 to_char(saledate,'Q') 函数获取销售日期对应的季度；如果使用 SQL Server 数据库，可以换成 datename(q, saledate) 函数；MySQL 目前没有提供 CUBE 选项。
从上面的示例可以看出，GROUPING SETS 可以实现任意的维度统计；ROLLUP 和 CUBE 都属于预定义的特定统计维度。
数据透视表
在 Excel 中有一个分析功能叫做数据透视表（Pivot Table），如下图所示：
其中，产品和渠道出现在行中，可以进行展开和折叠；日期出现在列中，最后一列是所有月份的汇总；透视表可以通过将行移动到列或将列移动到行，从而查看不同级别的数据汇总。
使用 SQL 同样可以实现上面的数据透视表：
-- Oracle、PostgreSQLwith d(saledate, product, channel, amount) as (select extract(month from saledate), product, channel, sum(amount)from sales_datagroup by extract(month from saledate), product, channel)select coalesce(product, '【全部产品】') "产品", coalesce(channel, '【所有渠道】') "渠道",sum(case saledate when 1 then amount else 0 end) "1月",sum(case saledate when 2 then amount else 0 end) "2月",sum(case saledate when 3 then amount else 0 end) "3月",sum(case saledate when 4 then amount else 0 end) "4月",sum(case saledate when 5 then amount else 0 end) "5月",sum(case saledate when 6 then amount else 0 end) "6月",sum(amount) "合计"from dgroup by rollup (product, channel)order by product, channel desc;
首先，我们使用 WITH 子句获得了按照月份、产品、渠道统计的销量；extract(month from saledate) 函数用于提取销售日期中的月份；然后使用 SUM 函数和 CASE 表达式将月份从行转换为列，最终得到了一个数据透视表。
MySQL 中需要使用 with rollup 选项计算合计；SQL Server 中需要使用 datepart(month, saledate) 函数。
总结
Excel 中的数据透视表通过展开、折叠、筛选、行列转换等操作得到不同层次和视角的数据小计/合计/总计。这些功能在数据库的 OLAP（在线分析处理系统）中被称为下钻（Drill down）、上钻(Roll up)、切块（Dicing）、切片（Slicing）、旋转（Pivot）等，利用 SQL 中的聚合函数、GROUP BY 子句的 ROLLUP、CUBE 和 GROUPING SETS 选项以及 CASE 表达式实现，而且更加灵活。
原文作者：SQL编程思想；如有侵权，请联系管理员删除。
