《MySQL 入门教程》第 12 篇分组统计

「这是我参与11月更文挑战的第5天，活动详情查看： 2021最后一次更文挑战」。
12.1 聚合函数

在 SQL 中，聚合函数（Aggregate Function）用于对一组数据进行汇总计算，并且返回单个分析结果 。例如，公司中的员工总数、所有员工的平均月薪等。MySQL 中常见的聚合函数包括：
COUNT ，返回查询结果的行数；
AVG ，计算一组数值的平均值；
SUM ，计算一组数值的总和；
MAX ，计算一组数据中的最大值；
MIN ，计算一组数据中的最小值；
GROUP_CONCAT ，连接一组字符串。
例如，以下查询返回了公司中的员工总数、平均月薪、最高月薪、最低月薪以及所有员工的月薪总和：
select count(*) "员工数量",
       avg(salary) "平均月薪",
       max(salary) "最高月薪",
       min(salary) "最低月薪",
       sum(salary) "月薪总和"
from employee;
员工数量|平均月薪   |最高月薪 |最低月薪 |月薪总和 |
-------|-----------|--------|-------|---------|
     25|9912.000000|30000.00|4000.00|247800.00|
以下查询返回了行政管理部所有员工的姓名组成的字符串：
select group_concat(emp_name) "所有员工",
       group_concat(emp_name order by salary separator ':') "所有员工"
from employee
where dept_id = 1;
所有员工     |所有员工      |
------------|------------|
刘备,关羽,张飞|张飞:关羽:刘备|
第一个 group_concat 函数使用默认的参数和分隔符，第二个 group_concat 函数指定了字符串的连接顺序和分隔符。
使用聚合函数时需要注意两点：
在聚合函数的参数中加上 DISTINCT 关键字，可以在计算之前排除重复值。例如，当 AVG 函数中包含 DISTINCT 参数时，在计算平均值之前会排除掉重复值。因此，（1、1、2）的平均值为 (1 + 2) / 2 = 1.5，而不是 (1 + 1 + 2) / 3 = 1.33。
聚合函数在计算时，忽略输入值为 NULL 的数据行；COUNT(*) 除外。例如，当 AVG 函数中存在空值时，计算之前会忽略这些空值。因此，（1，2，NULL）的平均值为 (1 + 2) / 2 = 1.5，而不是 (1 + 2) / 3 = 1。
select count(*), 
       count(distinct sex), 
       count(bonus)
from employee;
count(*)|count(distinct sex)|count(bonus)|
--------|-------------------|------------|
      25|                  2|           9|
其中，COUNT(*) 返回了员工的总数；count(distinct sex) 返回了不同性别的种类（男、女）；count(bonus) 返回了拥有奖金的员工数量，只有 9 名员工有奖金。
聚合函数的完整语法如下：
aggregate_function( [ALL | DISTINCT] expression)
其中，ALL 表示计算时不排除重复值。这是默认行为，通常省略。
📝MySQL 还支持更多的聚合函数，例如计算方差和标准差的 VAR_SAMP 和 STDDEV_SAMP 函数；详细列表可以参考官方文档。
聚合函数单独使用时，只能返回所有数据的整体汇总结果。如果我们想要按照不同的分组进行统计，例如按照部门统计员工的平均薪水、员工数量等，就要将聚合函数和GROUP BY分组子句一起使用。
12.2 分组汇总
GROUP BY 子句可以将数据按照某种规则进行分组，并且为每一个组返回一条记录。在查询语句中使用分组子句的语法如下：
SELECT col1,
       col2,
       aggregate_function(expression)
  FROM table_name
[WHERE conditions]
 GROUP BY col1, col2;
例如，以下查询返回了不同部门中的员工数量和月薪总和：
select dept_id, count(*), sum(salary)
from employee
group by dept_id;
dept_id|count(*)|sum(salary)|
-------|--------|-----------|
      1|       3|   80000.00|
      2|       3|   41500.00|
      3|       2|   18000.00|
      4|       9|   68200.00|
      5|       8|   40100.00|
以下语句同时按照部门和性别统计员工的数量：
select dept_id, sex, count(*)
from employee
group by dept_id, sex;
dept_id|sex |count(*)|
-------|----|--------|
      1|男  |       3|
      2|男  |       3|
      3|女  |       2|
      4|男  |       8|
      4|女  |       1|
      5|男  |       8|
以下语句统计了每年入职的员工数量：
select extract(year from hire_date) as "入职年份",
       count(*) as "员工数量"
from employee
group by extract(year from hire_date);
入职年份|员工数量|
----|----|
2000|   3|
2006|   1|
2008|   1|
2007|   1|
2002|   2|
2005|   1|
2009|   1|
2011|   3|
2012|   2|
2010|   1|
2014|   1|
2017|   2|
2018|   5|
2019|   1|
GROUP BY 支持使用表达式进行分组。EXTRACT 函数用于提取日期中的年份信息，我们在后续文章中会介绍这个函数。
另外，GROUP BY 也可以使用字段在 SELECT 列表中出现的次序指定分组方式。上面的示例可以改写如下：
select extract(year from hire_date) as "入职年份",
       count(*) as "员工数量"
from employee
group by 1;
extract(year from hire_date) 是查询返回的第 4 个字段；因此该语句也是按照年度统计入职的员工数量。
如果GROUP BY后的分组字段存在 NULL 值，多个 NULL 值将被看作一个分组。以下语句按照不同奖金值统计员工的数量：
SELECT bonus, COUNT(*)
  FROM employee
 GROUP BY bonus;
bonus   |COUNT(*)|
--------|--------|
10000.00|       3|
 8000.00|       1|
  [NULL]|      16|
 5000.00|       2|
 6000.00|       1|
 2000.00|       1|
 1500.00|       1|
从查询结果可以看出，16 个员工没有奖金；但是他们都被分组同一个组中，而不是多个不同的组。
在使用分组汇总时，初学者常见的一个错误就是在 SELECT 列表中使用了既不是聚合函数，也不属于分组字段的字段。例如：
-- GROUP BY 错误示例
select dept_id, emp_name, avg(salary)
from employee
group by dept_id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hrdb.employee.emp_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
以上语句返回了一个错误：字段 emp_name 没有出现在 GROUP BY 子句或者聚合函数中。原因在于该查询按照部门进行分组，但是每个部门包含多个员工；因此无法确定需要显示哪个员工的姓名。
MySQL 通过 SQL 模式参数 ONLY_FULL_GROUP_BY 控制该行为，默认值表示遵循 SQL 标准；如果禁用该参数，以上示例不会出错。
另外，MySQL 也可以通过 ANY_VALUE 函数返回一个随机的数据，可以避免以上错误：
select dept_id, any_value(emp_name), avg(salary)
from employee
group by dept_id;
dept_id|any_value(emp_name)|avg(salary) |
-------|-------------------|------------|
      1|刘备                |26666.666667|
      2|诸葛亮              |13833.333333|
      3|孙尚香              | 9000.000000|
      4|赵云                | 7577.777778|
      5|法正                | 5012.500000|
需要小心的是，any_value 函数返回的数据是不确定的。
12.3 分组过滤
当我们需要对分组后的数据再次进行过滤，例如找出人数多于 5 个的部门时，如果在 WHERE 子句中增加一个过滤条件：
select




    
 dept_id, count(*)
from employee
where count(*) > 5
group by dept_id;
ERROR 1111 (HY000): Invalid use of group function
该语句执行出错。错误的原因在于 WHERE 子句在 GROUP BY 子句之前执行，此时还没有计算聚合函数，因此它只能基于分组之前的数据进行过滤。如果需要对分组后的结果进行过滤，需要使用HAVING子句。以上查询的正确写法如下：
select dept_id, count(*)
from employee
group by dept_id
having count(*) > 5;
dept_id|count(*)|
-------|--------|
      4|       9|
      5|       8|
HAVING 子句位于 GROUP BY 之后，并且必须与 GROUP BY 一起使用。
我们可以使用 WHERE 子句对表进行过滤，同时使用 HAVING 对分组结果进行过滤。例如，以下语句返回了存在 2 名以上女性员工的部门：
select dept_id, count(*) cnt
from employee
where sex = '女'
group by dept_id
having cnt >= 2;
dept_id|cnt|
-------|---|
      3|  2|
首先通过 WHERE子句找出女性员工；然后，按照部门编号进行分组，计算每个组内的员工数量；最后，使用 HAVING 子句过滤员工数量等于或多于 2 个人的部门。MySQL 允许在 HAVING 子句中使用列的别名（cnt）进行过滤。
到目前为止，我们学习过的完整查询语句如下：
SELECT col1,
       col2,
       aggregate_function(expression)
  FROM table_name
 WHERE conditions
 GROUP BY col1, col2
HAVING conditions
 ORDER BY col1 [ASC | DESC], col2 [ASC | DESC], ...
 LIMIT [off_set,] row_count;
对于以上各个子句，MySQL 的逻辑执行顺序为 FROM、WHERE、SELECT、GROUP BY、HAVING、ORDER BY 以及 LIMIT。
12.4 高级分组
MySQL 中的 GROUP BY 子句还支持一个WITH ROLLUP选项，除了分组统计之外还会生成更高层级的汇总，类似于报表中的小计和总计。
首先创建一个销售数据表：
CREATE TABLE sales (
    item VARCHAR(10),
    year VARCHAR(4),
    quantity INT
INSERT INTO sales VALUES('apple', '2018', 800);
INSERT INTO sales VALUES('apple', '2018', 1000);
INSERT INTO sales VALUES('banana', '2018', 500);
INSERT INTO sales VALUES('banana', '2018', 600);
INSERT INTO sales VALUES('apple', '2019', 1200);
INSERT INTO sales VALUES('banana', '2019', 1800);
使用以下查询可以返回按照产品和年度统计的销量小计，按照产品统计的销量合计，以及所有产品的销量总计：
select item, year, sum(quantity)
from sales
group by item, year with rollup;
item  |year|sum(quantity)|
------|----|-------------|
apple |2018|         1800|
apple |2019|         1200|
apple |    |         3000|
banana|2018|         1100|
banana|2019|         1800|
banana|    |         2900|
      |    |         5900|
其中，第三行数据表示 apple 在所有年度的销量合计；最后一行表示所有产品在所有年度的销量总计。
对于以下形式的 WITH ROLLUP 而言：
GROUP BY col1, col2 WITH ROLLUP
实际上等价于以下三种分组统计的结果相加：
GROUP BY col1, col2
GROUP BY col1
GROUP BY null
使用了 WITH ROLLUP 选项之后，会产生一些数据为 NULL 的结果，表示相应字段上的汇总结果。但是这种显示方式意义不明确，而且如果原数据也有 NULL 数据，则无法进行区分。因此 MySQL 提供了GROUPING()函数。
如果某个数据是汇总的小计或者总计，GROUPING() 函数返回 1；否则，返回 0。例如：
select item, year, sum(quantity), grouping(item), grouping(year), grouping(item, year)
from sales
group by item, year with rollup;
item  |year|sum(quantity)|grouping(item)|grouping(year)|grouping(item, year)|
------|----|-------------|--------------|--------------|--------------------|
apple |2018|         1800|             0|             0|                   0|
apple |2019|         1200|             0|             0|                   0|
apple |    |         3000|             0|             1|                   1|
banana|2018|         1100|             0|             0|                   0|
banana|2019|         1800|             0|             0|                   0|
banana|    |         2900|             0|             1|                   1|
      |    |         5900|             1|             1|                   3|
其中，第三行数据是按照年度计算的合计，grouping(item) 返回 0，grouping(year) 返回 1；最后一行是所有产品在所有年度的销量总计，grouping(item) 返回 1，grouping(year) 返回 1。grouping(item, year) 的计算方式是 grouping(item) * 2 + grouping(year)。
对于 grouping(col1, col2, col3)，计算的方式如下：
grouping(col1) * 4 + grouping(col2) * 2 + grouping(col3)
我们可以将上面的示例修改如下：
select if(grouping(item) = 1, '所有产品', item) as "产品", 
       if(grouping(year) = 1, '所有年度', item) as "年度", 
       sum(quantity) as "销量"
from sales
group by item, year with rollup;
产品     |年度      |销量 |
---------|---------|----|
apple    |apple    |1800|
apple    |apple    |1200|
apple    |所有年度  |3000|
banana   |banana   |1100|
banana   |banana   |1800|
banana   |所有年度  |2900|
所有产品   |所有年度  |5900|
其中，IF(expr1,expr2,expr3) 函数当 expr1 为 TRUE 时（expr1 <> 0 and expr1 <> NULL）返回 expr2 的值；否则，返回 expr3 的值。
GROUPING() 函数可以用于 SELECT 列表、HAVING 子句以及 ORDER BY 子句中。