SQL高级技巧透视表

• mysql-8.0
• DataGrip

数据源：chapter10_1.csv

shop,city,province,sales
F1,杭州,浙江省,1
A1,北京,北京,2
A2,北京,北京,3
A3,北京,北京,4
B,泉州,福建省,5
D,成都,四川省,6
F2,杭州,浙江省,7
C,厦门,福建省,8
E,绵阳,四川省,9

数据源：chapter10_4.csv

id,score
1,79
2,85
3,53
1,61
2,71
3,88
1,71
2,66
3,97

数据源：chapter10_7.csv

order_id,price,deal_date,area
S001,10,2019/1/1,A区
S002,20,2019/1/1,B区
S003,30,2019/1/1,C区
S004,40,2019/1/2,A区
S005,10,2019/1/2,B区
S006,20,2019/1/2,C区
S007,30,2019/1/3,A区
S008,40,2019/1/3,C区

（1）对分组后的数据进行聚合运算

我们要获取每个省份的店铺总销量，需要先对province列进行分组，然后对各个组内的sales列进行求和聚合运算，具体实现代码如下：

-- 比如，我们要获取每个省份的店铺总销量
select province,sum(sales) as sum_sales from demo.chapter10_1 group by province;

运行结果：

有时候，我们还需要按照province列和city列同时进行分组，并对分组后的sales列的数据进行求和聚合运算，具体实现代码如下：

select province,city,sum(sales) as sum_sales from demo.chapter10_1 group by province,city;

我们还可以同时对组内数据进行多个聚合运算，比如，对sales列进行求和聚合运算，对shop列进行计数聚合运算，具体实现代码如下：

select province ,
       city,
       sum(sales) as sum_sales,
       count(shop) as count_shop
from
     demo.chapter10_1
group by
         province,
         city;

在使用group by的过程中，要注意一下两点：
（1）除参加聚合运算的列外，要在select中查询的列必须先通过group by进行分组，因为没有进行分组的列是不会直接展示出来的，这些列只是在背后等着参与聚合运算，直接select这些列是查找不到的。

（2）group by后面的列名必须是原始表中的列名，而不能是select过程中起的别名。

（2）对聚合后的数据进行条件筛选

有时候，聚合出来的数据并不都是我们想要的，我们在前面讲过如何利用where来筛选满足条件的行，where是针对原始表进行条件筛选的，对聚合后的数据是无效的，但我们又有对聚合后的数据进行条件筛选的需求，这个时候就可以使用having。比如，我们要筛选出店铺销量大于10的省份，可以通过如下代码实现：

select province,
       sum(sales) as sum_sales
from
     demo.chapter10_1
group by province
having sum_sales > 10;

运行结果：

在上面代码中，having后面的sum(sales)也可以换成别名sum_sales，得到的结果是一样的。读者可能会有疑惑，为什么having后面可以使用别名，而group by后面不可以使用别名呢？

这就涉及了SQL语句的执行顺序，group by的执行顺序是先于组内聚合运算及其对应的别名生成的，所以不能使用别名，因为别名还没有生成。而having的执行顺序是落后于组内聚合运算及其对应的别名生成的，所以可以使用别名。

（3）group_concat()函数

现在我们有这样一个需求，将每位同学的模拟考试成绩从多行合并成一行，且放在一个单元格内，值与值之间用半角逗号分隔。

这个时候就可以使用group_concat()函数来实现，group_concat()函数可以理解成group by和concat的组合，作用是对组内的字符串进行连接，具体实现代码如下：

select id,group_concat(score) as score_group from demo.chapter10_4 group by id;

运行结果：

group_concat()函数一般需要与group by结合使用。

（4）rollup

有时候，我们还会有这样的需求，就是根据不同维度进行分组聚合，然后将分组聚合后的数据汇总到同一张表中，比如，按照province列进行分组得到每个省份的店铺总销量，然后按照city列进行分组得到每个城市的店铺总销量，最后将二者合并到一张表中，这个过程我们可以通过如下代码实现。
我们先获取每个省份的店铺总销量，具体实现代码如下：

-- 我们先获取每个省份的店铺总销量，具体实现代码如下：
select province ,null as city ,sum(sales) as sum_sales from demo.chapter10_1 group by province;

上面的代码中增加了一列null值是为了便于与后面的province列和city列在纵向合并时实现列与列对齐。

然后获取每个城市的店铺总销量，具体实现代码如下：

-- 然后获取每个城市的店铺总销量，具体实现代码如下：
select province,city,sum(sales) as sum_sales from demo.chapter10_1 group by province, city;

接着将上面得到的数据进行纵向合并，使用的是union all

select
       province ,
       null as city ,
       sum(sales) as sum_sales
from demo.chapter10_1
group by province
union all select
                 province,
                 city,
                 sum(sales) as sum_sales
from demo.chapter10_1
group by province, city;

运行结果：

上面的需求还有一种更便捷的实现方式，就是使用rollup，在group by的具体列名后面加上with rollup即可，具体实现代码如下：

select province,city,sum(sales) as sum_sales from demo.chapter10_1 group by province, city with rollup ;

运行结果：

上表中province列和city列同时为null的表示全国汇总的店铺销量数据，province列不为null而city列为null的表示各个省份汇总的店铺销量数据，province列和city列都不为null的表示各个城市汇总的店铺销量数据。

（5）数据透视表

如果领导想看一下每天每个区域的订单量，是很简单的，直接按照日期和区域同时进行分组即可，但是这样得出的结果是每天每个区域放于一行（下表Before样式），不利于直接查看。所以最好可以制作成下表所示的After样式，也就是数据透视表的样式，在Excel中很好实现，直接把deal_date字段拖到行区域，把area字段拖到列区域，把order_id字段拖到值区域，然后对order_id字段进行计数聚合运算。

在SQL中，我们要实现数据透视表需要使用group by与case when两者组合的形式，具体实现代码如下：

select deal_date,
       count(case when area = 'A区' then order_id end ) as 'A区',
       count(case when area = 'B区' then order_id end ) as 'B区',
       count(case when area = 'C区' then order_id end ) as 'C区'

from demo.chapter10_7
group by deal_date;

以上内容仅供参考学习，如有侵权请联系我删除！
如果这篇文章对您有帮助，左下角的大拇指就是对博主最大的鼓励。
您的鼓励就是博主最大的动力！