MySQL之数据分组与数据透视表

逃离我推掉我的手 2023-01-15 15:30 181阅读 0赞

### 目录 ###

*   *  （1）对分组后的数据进行聚合运算
     *  （2）对聚合后的数据进行条件筛选
     *  （3）group\_concat()函数
     *  （4）rollup
     *  （5）数据透视表

开发工具：

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

运行结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70]  
有时候，我们还需要按照province列和city列同时进行分组，并对分组后的sales列的数据进行求和聚合运算，具体实现代码如下：

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 1]  
我们还可以同时对组内数据进行多个聚合运算，比如，对sales列进行求和聚合运算，对shop列进行计数聚合运算，具体实现代码如下：

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 2]  
**在使用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;

运行结果：  
![在这里插入图片描述][2021042311332521.png]  
在上面代码中，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;

运行结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 3]  
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;

运行结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 4]  
上面的需求还有一种更便捷的实现方式，就是使用rollup，在group by的具体列名后面加上with rollup即可，具体实现代码如下：

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

运行结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 5]  
上表中province列和city列同时为null的表示全国汇总的店铺销量数据，province列不为null而city列为null的表示各个省份汇总的店铺销量数据，province列和city列都不为null的表示各个城市汇总的店铺销量数据。

## （5）数据透视表 ##

如果领导想看一下每天每个区域的订单量，是很简单的，直接按照日期和区域同时进行分组即可，但是这样得出的结果是每天每个区域放于一行（下表Before样式），不利于直接查看。所以最好可以制作成下表所示的After样式，也就是数据透视表的样式，在Excel中很好实现，直接把deal\_date字段拖到行区域，把area字段拖到列区域，把order\_id字段拖到值区域，然后对order\_id字段进行计数聚合运算。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 6]  
在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;

--------------------

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70]: /images/20221022/9e7db781159d4c488078c9ea3d149b3d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 1]: /images/20221022/5e56ff4b92b0422e8b0422e983f882f7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 2]: /images/20221022/54a5997b9c9a4fb7b33d42a7e0a6271b.png
[2021042311332521.png]: /images/20221022/3aa5563a54c94f9a992feac60628d866.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 3]: /images/20221022/0f2ffb1d0ec54f50a0de81df010d3d5b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 4]: /images/20221022/1224c8cc6061440c85aa5ba8c82c3629.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 5]: /images/20221022/5645f46dd2d04ce5bc1c1516bb730334.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70 6]: /images/20221022/50152198c91f4f27944244f15249479b.png