数据库系列之数据分组和数据透视表

1.数据分组

1.1 group by分组

使用过程中有以下2点要注意:

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

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

1.2 having条件筛选

与group by后面的列名必须是原始表中的列名不同，having后面的列名可以是别名。因为group by的执行顺序是先于组内聚合运算及其对应的列名生成的，所以不能使用别名。having的执行顺序是落后于组内聚合运算及其对应的列名生成的，所以可以使用别名。

1.3 group_concat()函数

该函数的作用是对组内的字符串进行拼接，相当于group by和concat的组合。

比如有一张表记录了每个学生的三门成绩，现在需要将每位同学的三门成绩汇总合并成一行数据，这个时候可以用到这个函数

原始数据如下:

SELECT
    sid,
    GROUP_CONCAT( score ) AS score_group 
FROM
    `chapter10` 
GROUP BY
    sid
复制代码

result:

合并后的效果如图所示。一般group_concat()函数要与group by结合使用

1.4 rollup

有时候，我们会有根据不同维度进行分组聚合并将结果汇总到同一张表格中的需求。比如，按照province列进行分组得到每个省的销售额，然后按照city列进行分组得到每个城市的销售额，最后将二者合并

所用到的数据如下:

1.4.1 一般性的做法

一般的，先获取每个省的销售额

SELECT
    province,
    NULL AS city,
    SUM( sale_num ) AS sum_sales 
FROM
    `sales` 
GROUP BY
    province
复制代码

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

result:

再获取每个市的销售额

SELECT
    province,
    city,
    SUM( sale_num ) AS sum_sales 
FROM
    `sales` 
GROUP BY
    province,
    city
复制代码

result:

然后使用union all进行纵向合并

SELECT
    province,
    NULL AS city,
    SUM( sale_num ) AS sum_sales 
FROM
    `sales` 
GROUP BY
    province
UNION ALL
SELECT
    province,
    city,
    SUM( sale_num ) AS sum_sales 
FROM
    `sales` 
GROUP BY
    province,
    city
复制代码

result:

对于这个结果，如果我们要查看每个省份的数据，只要让city列为null然后查看province列对应的数据即可。如果我们要查看每个省份的数据，只要查看city列不为null的部分对应的数据即可。

1.4.2 使用rollup进行实现

SELECT
    province,
    city,
    SUM( sale_num ) AS sum_sales 
FROM
    `sales` 
GROUP BY
    province,
    city WITH ROLLUP
复制代码

result:

这里说明一下，province列和city列均为null时表示全国汇总的销售额，province列不为null且city列为null表示各个省份的汇总销售额，province列和city列均不为null时表示各个城市的汇总销售额 可以看到，使用rollup进行实现代码更为简洁

2.数据透视表

数据透视表就是按照行列同时分组，然后对同时满足行列条件的值进行某种聚合运算

数据透视表实现如下:

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
    `order` 
GROUP BY
    deal_date
复制代码

result: