实战 SQL:销售数据的小计/合计/总计以及数据透视表
作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net
文章目录
小计、合计与总计
多维度交叉统计
自定义统计维度
数据透视表
总结
- 1
- 2
- 3
- 4
- 5
学习过 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 是分组字段的个数。
- 1
多维度交叉统计
如果我们的销量报表需要统计以下信息:
不同产品、不同渠道的销量小计;
不同产品、所有渠道的销量合计;
所有产品、不同渠道的销量合计;
所有产品、所有渠道的销量总计。
- 1
- 2
- 3
- 4
由于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 个以上就比较麻烦。
📝GROUP BY子句的CUBE选项是一种按照各种层次组合汇总的过程,需要汇总 2 的 N 次方个维度,N 是分组字段的个数。
- 1
自定义统计维度
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、PostgreSQL
select coalesce(to_char(saledate,‘Q’),’【半年】’) “季度”, coalesce(product, ‘【全部产品】’) “产品”,
coalesce(channel, ‘【所有渠道】’) “渠道”, sum(amount) “销量”
from sales_data
group 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、PostgreSQL
with d(saledate, product, channel, amount) as (
select extract(month from saledate), product, channel, sum(amount)
from sales_data
group 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 d
group 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表达式实现,而且更加灵活。