实战 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;

saledateproductchannelamount
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;

productsum(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
桔子NULL909261.00
苹果店面306643.00
苹果京东318614.00
苹果淘宝311795.00
苹果NULL937052.00
香蕉店面311445.00
香蕉京东306033.00
香蕉淘宝307891.00
香蕉NULL925369.00
NULLNULL2771682.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表达式实现,而且更加灵活。