「SQL 数据分析系列」8. 分组和聚合

写在前面：

大家好，我是强哥，一个热爱分享的技术狂。目前已有 12 年大数据与 AI 相关项目经验， 10 年推荐系统研究及实践经验。平时喜欢读书、暴走和写作。

业余时间专注于输出大数据、AI 等相关文章，目前已经输出了 40 万字的推荐系统系列精品文章，今年 6 月底会出版「构建企业级推荐系统：算法、工程实现与案例分析」一书。如果这些文章能够帮助你快速入门，实现职场升职加薪，我将不胜欢喜。

想要获得更多免费学习资料或内推信息，一定要看到文章最后喔。

内推信息

如果你正在看相关的招聘信息，请加我微信：liuq4360，我这里有很多内推资源等着你，欢迎投递简历。

免费学习资料

如果你想获得更多免费的学习资料，请关注同名公众号【数据与智能】，输入“资料”即可！

学习交流群

如果你想找到组织，和大家一起学习成长，交流经验，也可以加入我们的学习成长群。群里有老司机带你飞，另有小哥哥、小姐姐等你来勾搭！加小姐姐微信：epsila，她会带你入群。

第八章分组和聚合

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

分组概念

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

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 部及以上电影的客户。

聚合函数

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

• 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 美元。希望上面的查询能让你了解这些聚合函数的作用，下一节会进一步阐明如何使用这些函数。

隐式分组和显式分组

在前面的示例中，查询返回的每个值都是由聚合函数生成的。因为没有使用 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 个组中的每个组。

计数独立值

使用 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()函数将检查组中每个成员的列值，以便查找和删除重复项，而不是简单地计算组中的值数。

使用表达式

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

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 表达式，以确定某聚合中是否包含了特定的行。

处理 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 列中包含的值的数目，并且会忽略遇到的任何空值。

产生分组

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

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

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

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

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

单列分组

对单列进行分组是最简单也是最常用的分组类型。例如，如果要查找与每个演员相关联的电影数量，只需对 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 个分组，分别对应每个演员，然后对组中每个演员关联的电影数量求和。

多列分组

在某些情况下，可能需要生成跨多个列的分组。基于上一个例子，假设你想找到每个演员所关联电影的分级(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）生成的。

使用表达式进行分组

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

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 表中的数据行进行分组。

产生合计数/汇总

在前面的“多列分组”中，我举了计算每个演员的电影数量和电影分级组合的例子。但是现在假设：除了每个演员和电影分级组合的合计数外，还需要为每个不同演员单独计算合计数。要解决这个问题，你可以另外使用一个查询并将结果合并到一起，也可以将查询结果导入电子表格，又或者是构建一个 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 中该选项是可用的。

分组过滤条件

我在第四章中介绍过各种类型的过滤条件，并且演示了如何在 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 子句）。