实战 SQL：销售数据的小计/合计/总计以及数据透视表_sql怎么统计团队的交易-优快云博客

文章目录

        小计、合计与总计
        多维度交叉统计
        自定义统计维度
        数据透视表
        总结

学习过 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