文章目录
小计、合计与总计
多维度交叉统计
自定义统计维度
数据透视表
总结学习过 SQL 的人都知道，使用聚合函数（AVG、SUM、COUNT、MIN/MAX 等）和分组操作（GROUP BY）可以对数据进行基本的统计分析，例如统计公司员工的人数、每个部门的平均月薪等。如果想要回顾这些基础概念，可以参考这篇文章。
不过 SQL 不仅仅能够进行这些基本的分组汇总，它还提供了许多高级的统计分析功能。本文就来介绍一下如何使用 SQL 实现销售数据的小计、合计、总计以及多维度交叉统计和数据透视表。我们首先列出这些功能在主流数据库中的支持情况：
- 参考下文中的具体讨论。本文使用的示例数据可以点此下载，如果没有特殊说明，以下示例适用于上面的 5 种数据库。
小计、合计与总计
我们先查看一下示例表中的数据：
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_data
group by product;
product | sum(amount) |
桔子 | 909261.00 |
香蕉 | 925369.00 |
苹果 | 937052.00 |
显然，还可以编写 SQL 语句统计三种不同产品在不同渠道各自的销量合计、所以产品的销量总计等。但是如何一次获取这些按照不同维度进行统计的结果呢？我们可以使用GROUP BY的第一个扩展选项：ROLLUP。例如：
– Oracle、SQL Server、PostgreSQL
select product “产品”, channel “渠道”, sum(amount) “销量”
from sales_data
group 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的使用略有不同：
– MySQL
select product, channel, sum(amount)
from sales_data
group by product, channel with rollup;MySQL 在分组字段之后使用with rollup选项，查询的结果与其他数据库相同。
ROLLUP选项可以使用UNION合并多个查询结果进行模拟：
with d as (
select product, channel , sum(amount) amount
from sales_data
group by product, channel
)
select product “产品”, channel “渠道”, amount “销量” from d
union all
select product, NULL, sum(amount) from d group by product
union all
select 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、PostgreSQL
select coalesce(product, ‘【全部产品】’) “产品”, coalesce(channel, ‘【所有渠道】’) “渠道”, sum(amount) “销量”
from sales_data
group 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) amount
from sales_data
group by product, channel
)
select product “产品”, channel “渠道”, amount “销量” from d
union all
select product, NULL, sum(amount) from d group by product
union all
select NULL, channel, sum(amount) from d group by channel
union all
select NULL, NULL, sum(amount) from d;这种方法正好解释了CUBE选项的作用，也适用于其他数据库；但是如果分组字段达到 3 个以上就比较麻烦。
