数据与智能 本公众号关注大数据与人工智能技术。由一批具备多年实战经验的技术极客参与运营管理，持续输出大数据、数据分析、推荐系统、机器学习、人工智能等方向的原创文章，每周至少输出7篇精品原创。同时，我们会关注和分享大数据与人工智能行业动态。欢迎关注。

来源 | Learning SQL Generate, Manipulate, and Retrieve Data， Third Edition

作者 | Alan Beaulieu

译者 | Liangchu

校对 | gongyouliu

编辑 | auroral-L

全文共7921字，预计阅读时间30分钟。

第八章 分组和聚合

1. 分组概念

2. 聚合函数

2.1 隐式分组和显式分组

2.2 计数独立值

2.3 使用表达式

2.4 处理Null值

3. 产生分组

3.1 单列分组

3.2 多列分组

3.3 使用表达式进行分组

3.4 产生合计数/汇总

4. 分组过滤条件

对于数据库用户来说，数据通常是以其所需的最低粒度存储的。如果在银行查账时需要查看单个客户交易，那么在数据库中需要有一个单独存储交易的表。然而，这并不意味着所有用户都必须处理存储在数据库中的数据。本章的重点是如何对数据进行分组和聚合，以使得用户在更高粒度级别与数据进行交互。

1. 分组概念

有时需要在数据中找到其变化的趋势，这就需要数据库服务器在生成所需的结果前对数据进行一点修改。例如，假设你负责向最常光顾的客户发送免费租赁影碟优惠券，你可以发出一个简单的查询来查看原始数据：

 mysql> SELECT customer_id FROM rental;
+-------------+
| customer_id |
+-------------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
...
| 599 |
| 599 |
| 599 |
| 599 |
| 599 |
| 599 |
+-------------+
16044 rows in set (0.01 sec)  

可以看出599位客户一共有16000多行租赁记录，所以通过查看原始数据来确定哪些客户租赁了最多的电影是不可行的。此时你可以使用group by子句指示数据库服务器对数据进行分组。以下是使用group by子句按照客户ID对租赁数据进行分组的相同查询：

 mysql> SELECT customer_id
 -> FROM rental
 -> GROUP BY customer_id;
+-------------+
| customer_id |
+-------------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
...
| 594 |
| 595 |
| 596 |
| 597 |
| 598 |
| 599 |
+-------------+
599 rows in set (0.00 sec)  

结果集中每一行对应customer_id列中的不同值，结果是599行，而不是完整的16044行。结果集变小的原因是一些客户租了不止一部电影。要查看每个客户租了多少电影，可以使用select子句中的聚合函数来计算每个组中的行数：

 mysql> SELECT customer_id, count(*)
 -> FROM rental
 -> GROUP BY customer_id;
+-------------+----------+
| customer_id | count(*) |
+-------------+----------+
| 1 | 32 |
| 2 | 27 |
| 3 | 26 |
| 4 | 22 |
| 5 | 38 |
| 6 | 28 |
...
| 594 | 27 |
| 595 | 30 |
| 596 | 28 |
| 597 | 25 |
| 598 | 22 |
| 599 | 19 |
+-------------+----------+
599 rows in set (0.01 sec)  

聚合函数count()计算每个分组中的行数，星号*告诉服务器计算组中的所有行。联合使用group by子句和count()聚合函数，你可以在不查看原始数据的情况下准确地生成满足业务问题的数据。

查看结果集，你可以看到ID为1的客户租赁了32部影片，ID为597的客户租赁了25部电影。想要知道哪位客户租赁的电影数最多，只需要添加一个order by：

 mysql> SELECT customer_id, count(*)
 -> FROM rental
 -> GROUP BY customer_id
 -> ORDER BY 2 DESC;
+-------------+----------+
| customer_id | count(*) |
+-------------+----------+
| 148 | 46 |
| 526 | 45 |
| 236 | 42 |
| 144 | 42 |
| 75 | 41 |
...
| 248 | 15 |
| 110 | 14 |
| 281 | 14 |
| 61 | 14 |
| 318 | 12 |
+-------------+----------+
599 rows in set (0.01 sec)  

现在对结果进行了排序，显而易见，ID为148的客户租赁电影的数量最多，共有46部，而ID为318的客户租赁电影的数量最少，为12部。

在对数据进行分组时，可能还需要根据分组数据（而非原始数据）从结果集中过滤不需要的数据。由于group by子句在where子句被执行之后才运行，因此不能向where子句中添加过滤条件。例如，下面的查询尝试过滤租赁电影数目小于40部的客户：

 mysql> SELECT customer_id, count(*)
 -> FROM rental
 -> WHERE count(*) >= 40
 -> GROUP BY customer_id;
ERROR 1111 (HY000): Invalid use of group function  

上面的查询报错，这是因为不能在where子句中使用聚合函数count(*)，因为在计算where子句时尚未生成分组，所以你必须将分组过滤条件放在having子句中。下面是使用having子句之后查询的结果：

 mysql> SELECT customer_id, count(*)
 -> FROM rental
 -> GROUP BY customer_id
 -> HAVING count(*) >= 40;
+-------------+----------+
| customer_id | count(*) |
+-------------+----------+
| 75 | 41 |
| 144 | 42 |
| 148 | 46 |
| 197 | 40 |
| 236 | 42 |
| 469 | 40 |
| 526 | 45 |
+-------------+----------+
7 rows in set (0.01 sec)  

因为包含的成员小于40的分组已经通过having子句过滤掉了，所以结果集现在只包含那些租了40部及以上电影的客户。

2. 聚合函数

聚合函数对某个组中的所有行执行特定操作。尽管每个数据库服务器都有其专用的聚合函数，但是也有些聚合函数是被所有数据库服务器支持的，包括：

• max()

返回一个集合中的最大值。

• min()

返回一个集合中的最小值。

• avg()

返回一个集合的平均值。

• sum()

返回一个集合所有值的和。

• count()

返回一个集合中值的总个数。

下面是一个使用各种常用聚合函数分析电影租金数据的查询：

 mysql> SELECT MAX(amount) max_amt, 
 -> MIN(amount) min_amt, 
 -> AVG(amount) avg_amt, 
 -> SUM(amount) tot_amt, 
 -> COUNT(*) num_payments 
 -> FROM payment; 
+---------+---------+----------+----------+--------------+ 
| max_amt | min_amt | avg_amt | tot_amt | num_payments | 
+---------+---------+----------+----------+--------------+ 
| 11.99 | 0.00 | 4.200667 | 67416.51 | 16049 | 
+---------+---------+----------+----------+--------------+ 
1 row in set (0.09 sec)  

从查询结果我们可以知道，在payment表的16049行数据中，租用一部电影的最高付款额为11.99美元，最低付款额为0美元，平均付款额为4.20美元，所有租金总额为67416.51美元。希望上面的查询能让你了解这些聚合函数的作用，下一节会进一步阐明如何使用这些函数。

2.1 隐式分组和显式分组

在前面的示例中，查询返回的每个值都是由聚合函数生成的。因为没有使用group by子句，所以只有一个隐式的分组（payment表中的所有行）。

然而在大多数情况下，你需要检索其他列以及聚合函数生成的列。例如，如果你希望扩展上一个查询，以便为每个客户执行相同的五个聚合函数，而不是基于所有客户执行查询。对于此查询，你需要在检索customer_id列的同时使用聚合函数，如下所示：

 SELECT customer_id, 
 MAX(amount) max_amt, 
 MIN(amount) min_amt, 
 AVG(amount) avg_amt, 
 SUM(amount) tot_amt, 
 COUNT(*) num_payments 
FROM payment;  

但是，在执行该查询的时候，会产生如下的错误：

 ERROR 1140 (42000): In aggregated query without GROUP BY,  
 expression #1 of SELECT list contains nonaggregated column  

显然，你想要将聚合函数应用于payment表中的每位客户，但是查询失败了，这是因为你没有显式指定数据的分组方式。因此，你需要给其增加一个group by子句来指定聚合函数应该应用于哪一组：

 mysql> SELECT customer_id,
 -> MAX(amount) max_amt,
 -> MIN(amount) min_amt,
 -> AVG(amount) avg_amt,
 -> SUM(amount) tot_amt,
 -> COUNT(*) num_payments
 -> FROM payment
 -> GROUP BY customer_id;
+-------------+---------+---------+----------+---------+--------------+
| customer_id | max_amt | min_amt | avg_amt | tot_amt | num_payments |
+-------------+---------+---------+----------+---------+--------------+
| 1 | 9.99 | 0.99 | 3.708750 | 118.68 | 32 |
| 2 | 10.99 | 0.99 | 4.767778 | 128.73 | 27 |
| 3 | 10.99 | 0.99 | 5.220769 | 135.74 | 26 |
| 4 | 8.99 | 0.99 | 3.717273 | 81.78 | 22 |
| 5 | 9.99 | 0.99 | 3.805789 | 144.62 | 38 |
| 6 | 7.99 | 0.99 | 3.347143 | 93.72 | 28 |
...
| 594 | 8.99 | 0.99 | 4.841852 | 130.73 | 27 |
| 595 | 10.99 | 0.99 | 3.923333 | 117.70 | 30 |
| 596 | 6.99 | 0.99 | 3.454286 | 96.72 | 28 |
| 597 | 8.99 | 0.99 | 3.990000 | 99.75 | 25 |
| 598 | 7.99 | 0.99 | 3.808182 | 83.78 | 22 |
| 599 | 9.99 | 0.99 | 4.411053 | 83.81 | 19 |
+-------------+---------+---------+----------+---------+--------------+
599 rows in set (0.04 sec)  

通过使用group by子句，服务器能够首先将customer_id列中具有相同值的行进行分组，然后将五个聚合函数应用于599个组中的每个组。

2.2 计数独立值

使用count()函数确定每个组中的成员数时，可以选择计算组中所有成员的数目，也可以选择只计算某个列的不同值数据。

例如，考虑以下查询，它以两种不同的方式将count()函数应用于customer_id列：

 mysql> SELECT COUNT(customer_id) num_rows,
 -> COUNT(DISTINCT customer_id) num_customers
 -> FROM payment;
+----------+---------------+
| num_rows | num_customers |
+----------+---------------+
| 16049 | 599 |
+----------+---------------+
1 row in set (0.01 sec)  

查询中的第一列只计算payment表中的行数，而第二列检查customer_id列中的值并且只计算唯一值的数目。因此，通过指定distinct，count()函数将检查组中每个成员的列值，以便查找和删除重复项，而不是简单地计算组中的值数。

2.3 使用表达式

除了使用列作为聚合函数的参数外，还可以使用表达式作为参数。例如，你可能希望找到影片租赁到归还所间隔的最大天数，可以使用下面的查询实现这点：

 mysql> SELECT MAX(datediff(return_date,rental_date))
 -> FROM rental;
+----------------------------------------+
| MAX(datediff(return_date,rental_date)) |
+----------------------------------------+
| 33 |
+----------------------------------------+
1 row in set (0.01 sec)  

datediff函数用于计算每次租赁操作的归还日期和租赁日期之间相差的天数，max函数用于返回最大值，在本例中为最长是33天。

虽然本例使用的表达式相当简单，但是实际上用于聚合函数的参数表达式可以根据需要增加复杂度，只要它们最后能返回一个数字、字符串或日期即可。在第十一章中，我将向你展示如何在聚合函数中使用case表达式，以确定某聚合中是否包含了特定的行。

2.4 处理Null值

在执行聚合函数或者其他类型的数值计算时，你始终应该考虑空值null对计算结果的影响。为了举例说明，我将构建一个简单的表来保存数字型数据，并用集合{1,3,5}将其初始化：

 mysql> CREATE TABLE number_tbl
 -> (val SMALLINT);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO number_tbl VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO number_tbl VALUES (3);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO number_tbl VALUES (5);
Query OK, 1 row affected (0.00 sec)  

考虑以下查询，它对一组数字集合执行五个聚合函数：

 mysql> SELECT COUNT(*) num_rows,
 -> COUNT(val) num_vals,
 -> SUM(val) total,
 -> MAX(val) max_val,
 -> AVG(val) avg_val
 -> FROM number_tbl;
+----------+----------+-------+---------+---------+
| num_rows | num_vals | total | max_val | avg_val |
+----------+----------+-------+---------+---------+
| 3 | 3 | 9 | 5 | 3.0000 |
+----------+----------+-------+---------+---------+
1 row in set (0.08 sec)  

结果如你所料：count(*)和count(val)返回的值都为3，sum(val)返回的值是9，max(val)返回5，avg(val)返回3。接下来，我将向number_tbl表中添加一个空值，并再次运行查询：

 mysql> INSERT INTO number_tbl VALUES (NULL);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT COUNT(*) num_rows,
 -> COUNT(val) num_vals,
 -> SUM(val) total,
 -> MAX(val) max_val,
 -> AVG(val) avg_val
 -> FROM number_tbl;
+----------+----------+-------+---------+---------+
| num_rows | num_vals | total | max_val | avg_val |
+----------+----------+-------+---------+---------+
| 4 | 3 | 9 | 5 | 3.0000 |
+----------+----------+-------+---------+---------+
1 row in set (0.00 sec)  

即使将空值添加到了表中，sum()、max()和avg()函数仍都返回相同的值，这表示它们忽略了遇到的任何空值。count(*)函数现在返回的值为4，这是因为number_tbl表包含四行，而count(val)函数仍然返3。区别在于count(*)的作用是统计行数，而count(val)统计val列中包含的值的数目，并且会忽略遇到的任何空值。

3. 产生分组

人们一般对原始数据没什么兴趣，而是希望能够对原始数据进行加工从而更好地满足他们的需求。常见的操作数据的例子包括：

• 生成某个地区的总额，如欧洲市场总销售额；

• 发现异常值（离群值），如2020年度最佳销售员；

• 确定频率，如每月租赁的电影数量。

要满足这些类型的查询，你需要请求数据库服务器通过一个或多个列或表达式将数据行分组。正如前面的例子所演示的，可以在查询中使用group by子句将数据分组。本节会说明如何按照一个或多个列对数据进行分组，如何使用表达式对数据进行分组，以及如何在组中生成合计数。

3.1 单列分组

对单列进行分组是最简单也是最常用的分组类型。例如，如果要查找与每个演员相关联的电影数量，只需对film_actor.actor_id列进行分组，如下所示：

 mysql> SELECT actor_id, count(*) 
 -> FROM film_actor 
 -> GROUP BY actor_id; 
+----------+----------+ 
| actor_id | count(*) | 
+----------+----------+ 
| 1 | 19 | 
| 2 | 25 | 
| 3 | 22 | 
| 4 | 22 | 
... 
| 197 | 33 | 
| 198 | 40 | 
| 199 | 15 | 
| 200 | 20 | 
+----------+----------+ 
200 rows in set (0.11 sec)  

这个查询生成200个分组，分别对应每个演员，然后对组中每个演员关联的电影数量求和。

3.2 多列分组

在某些情况下，可能需要生成跨多个列的分组。基于上一个例子，假设你想找到每个演员所关联电影的分级(G, PG, …)分组总数。下面的例子实现了这点：

 mysql> SELECT fa.actor_id, f.rating, count(*)
 -> FROM film_actor fa
 -> INNER JOIN film f
 -> ON fa.film_id = f.film_id
 -> GROUP BY fa.actor_id, f.rating
 -> ORDER BY 1,2;
+----------+--------+----------+
| actor_id | rating | count(*) |
+----------+--------+----------+
| 1 | G | 4 |
| 1 | PG | 6 |
| 1 | PG-13 | 1 |
| 1 | R | 3 |
| 1 | NC-17 | 5 |
| 2 | G | 7 |
| 2 | PG | 6 |
| 2 | PG-13 | 2 |
| 2 | R | 2 |
| 2 | NC-17 | 8 |
...
| 199 | G | 3 |
| 199 | PG | 4 |
| 199 | PG-13 | 4 |
| 199 | R | 2 |
| 199 | NC-17 | 2 |
| 200 | G | 5 |
| 200 | PG | 3 |
| 200 | PG-13 | 2 |
| 200 | R | 6 |
| 200 | NC-17 | 4 |
+----------+--------+----------+
996 rows in set (0.01 sec)  

该查询生成996个分组，通过将film_actor表与film表连接，每个组对应每个演员及电影级别的组合。除了将rating列添加到select子句之外，我还将其添加到了group by子句中，因为rating是从表中检索的，而不是通过聚合函数（如max或count）生成的。

3.3 使用表达式进行分组

除了使用列对数据进行分组外，还可以基于表达式生成的值进行分组。考虑以下查询，该查询按年份对租金进行分组：

 mysql> SELECT extract(YEAR FROM rental_date) year,
 -> COUNT(*) how_many
 -> FROM rental
 -> GROUP BY extract(YEAR FROM rental_date);
+------+----------+
| year | how_many |
+------+----------+
| 2005 | 15862 |
| 2006 | 182 |
+------+----------+
2 rows in set (0.01 sec)  

这个查询使用了一个相当简单的表达式，它使用extract()函数获取日期的年份部分，以便对rental表中的数据行进行分组。

3.4 产生合计数/汇总

在前面的“多列分组”中，我举了计算每个演员的电影数量和电影分级组合的例子。但是现在假设：除了每个演员和电影分级组合的合计数外，还需要为每个不同演员单独计算合计数。要解决这个问题，你可以另外使用一个查询并将结果合并到一起，也可以将查询结果导入电子表格，又或者是构建一个Python脚本、Java程序或其他方法来获取数据并执行额外的计算。不过更好的办法是使用with rollup选项让数据库服务器完成这项工作。下面是在group by子句中使用with rollup进行修改后的查询：

 mysql> SELECT fa.actor_id, f.rating, count(*)
 -> FROM film_actor fa
 -> INNER JOIN film f
 -> ON fa.film_id = f.film_id
 -> GROUP BY fa.actor_id, f.rating WITH ROLLUP
 -> ORDER BY 1,2;
+----------+--------+----------+
| actor_id | rating | count(*) |
+----------+--------+----------+
| NULL | NULL | 5462 |
| 1 | NULL | 19 |
| 1 | G | 4 |
| 1 | PG | 6 |
| 1 | PG-13 | 1 |
| 1 | R | 3 |
| 1 | NC-17 | 5 |
| 2 | NULL | 25 |
| 2 | G | 7 |
| 2 | PG | 6 |
| 2 | PG-13 | 2 |
| 2 | R | 2 |
| 2 | NC-17 | 8 |
...
| 199 | NULL | 15 |
| 199 | G | 3 |
| 199 | PG | 4 |
| 199 | PG-13 | 4 |
| 199 | R | 2 |
| 199 | NC-17 | 2 |
| 200 | NULL | 20 |
| 200 | G | 5 |
| 200 | PG | 3 |
| 200 | PG-13 | 2 |
| 200 | R | 6 |
| 200 | NC-17 | 4 |
+----------+--------+----------+
1197 rows in set (0.07 sec)  

现在的结果集比先前多出了201行数据，其中200行对应不同的两百个演员，还有一行是总合计数。对于200个演员的合计，其中rating列是空值，这是因为合计是针对电影级别而执行的。例如：查看actor_id为200的第一行数据，可以知道总共有20部影片与该演员相关，它等于每个等级的计数总和(4 NC-17 + 6 R + 2 PG-13 + 3 PG + 5 G)。对于第一行总合计数，actor_id和rating列都是空值，合计数是5462，它等于film_actor表中的行数。

注意：如果你使用Oracle Database，执行合计操作的语法就稍微有些不同了。使用Oracle时，上一个查询的group by子句如下所示：

 GROUP BY ROLLUP(fa.actor_id, f.rating)  

该语法的优点是，它允许你对group_by子句中的某个子集执行合计操作。例如，如果按列a、b和c进行分组，则可以通过以下方式要求服务器只对列b和c执行合计操作：

 GROUP BY a, ROLLUP(b, c)  

如果除了计算演员的合计数，还想计算每个电影级别的合计，则可以使用with cube选项，该选项将为所有可能的分组列组合生成合计行。不幸的是，MySQL的8.0版并不支持该选项，但在SQL Server和Oracle Database中该选项是可用的。

4. 分组过滤条件

我在第四章中介绍过各种类型的过滤条件，并且演示了如何在where子句中使用它们。对数据进行分组时，还可以在产生分组后对数据应用过滤条件。having子句就是放置这些类型的过滤条件的地方。考虑以下示例：

 mysql> SELECT fa.actor_id, f.rating, count(*) 
 -> FROM film_actor fa 
 -> INNER JOIN film f 
 -> ON fa.film_id = f.film_id 
 -> WHERE f.rating IN ('G','PG') 
 -> GROUP BY fa.actor_id, f.rating 
 -> HAVING count(*) > 9; 
+----------+--------+----------+ 
| actor_id | rating | count(*) | 
+----------+--------+----------+ 
| 137 | PG | 10 | 
| 37 | PG | 12 | 
| 180 | PG | 12 | 
| 7 | G | 10 | 
| 83 | G | 14 | 
| 129 | G | 12 | 
| 111 | PG | 15 | 
| 44 | PG | 12 | 
| 26 | PG | 11 | 
| 92 | PG | 12 | 
| 17 | G | 12 | 
| 158 | PG | 10 | 
| 147 | PG | 10 | 
| 14 | G | 10 | 
| 102 | PG | 11 | 
| 133 | PG | 10 | 
+----------+--------+----------+ 
16 rows in set (0.01 sec)  

这个查询有两个过滤条件：一个在where子句中，过滤掉级别不是G或PG的所有电影；另一个在having子句中，过滤掉参演电影数少于10部的演员。因此，其中一个过滤条件在数据分组之前对其进行操作，而另一个过滤条件在创建分组之后对数据进行操作。如果错误地将两个过滤条件都放在where子句中，将产生以下错误：

 mysql> SELECT fa.actor_id, f.rating, count(*)
 -> FROM film_actor fa
 -> INNER JOIN film f
 -> ON fa.film_id = f.film_id
 -> WHERE f.rating IN ('G','PG')
 -> AND count(*) > 9
 -> GROUP BY fa.actor_id, f.rating;
ERROR 1111 (HY000): Invalid use of group function  

因为查询的where子句中是不能包含聚合函数的，所以该查询失败。这是由于where子句中的过滤器在分组事件发生之前进行了计算，因此服务器此时还不能对分组执行任何函数。

注意：在向包含group by子句的查询中添加过滤条件时，请仔细考虑过滤条件是作用于原始数据（此时它属于where子句），还是作用于分组后的数据（此时它属于having子句）。