从 01 开始
2023-11-19
目录

聚合查询

# 80.聚合查询

随着表中记录（数据行）的不断积累，存储数据逐渐增加，有时我们可能希望计算出这些数据的合计值或者平均值等。

本章我们将学习使用 SQL 语句进行汇总操作的方法。

‍ ‍

# 聚合函数

通过 SQL 对数据进行某种操作或计算时需要使用函数。例如，计算表中全部数据的行数时，可以使用 COUNT ​函数 。

除此之外，SQL 中还有很多其他用于汇总的函数，常用的有：

  • ​COUNT​ ：计算表中的记录数（行数）
  • ​SUM​ ：计算表中数值列中数据的合计值
  • ​AVG​ ：计算表中数值列中数据的平均值
  • ​MAX​ ：求出表中任意列中数据的最大值
  • ​MIN​ ：求出表中任意列中数据的最小值

用于汇总的函数称为聚合函数或者聚集函数 。所谓聚合，就是将多行汇总为一行。实际上，所有的聚合函数都是这样，输入多行，输出一行。

函数，与我们在学校数学课上学到的意思是一样的，输入某个值（参数），就能输出计算后的结果（返回值）。

‍

# count 函数

使用方法：

select count(*) from Product;

--结果：
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

​COUNT()​ 中的星号，我们在之前已经学过，代表全部列的意思。注意，其他函数并不能将星号作为参数（如果使用会出错）。

‍

如果想要计算 null 之外的行数呢？这样参数就不是星号，而是具体的列了。例如想得到 purchase_price 列（进货单价）中非空行数的话：

SELECT COUNT(purchase_price)
  FROM Product;

--结果：
+-----------------------+
| COUNT(purchase_price) |
+-----------------------+
|                     6 |
+-----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

对于 COUNT 函数来说，参数列不同计算的结果也会发生变化。

‍

# sum 函数

顾名思义，就是求和的，例如求出销售单价的合计值：

SELECT SUM(sale_price)
  FROM Product;

--结果：
+-----------------+
| SUM(sale_price) |
+-----------------+
|           16780 |
+-----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

得到的结果 16780 日元，是所有销售单价（sale_price 列）的合计。注意，如果有 null，则会忽略。

虽然之前说过，“四则运算中如果存在 NULL ，结果一定是 NULL”，但是聚合函数在计算之前就已经把 NULL 排除在外了，因此我们可以忽略 null。

刚刚讲的 count 函数也是会将 NULL 排除在外。但 COUNT(*) 例外。

‍

# avg 函数

avg 是 average 的缩写，即平均的意思。例如计算销售单价的平均值：

SELECT AVG(sale_price)
  FROM Product;

--结果：
+-----------------+
| AVG(sale_price) |
+-----------------+
|       2097.5000 |
+-----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

计算式：（1000+500+4000+3000+6800+500+880+100） / 8 = 2097.5

‍

同理，如果包含 null，则会排除 null 后再计算。例如计算 purchase_price：

SELECT AVG(sale_price), AVG(purchase_price)
  FROM Product;

--结果：
+-----------------+---------------------+
| AVG(sale_price) | AVG(purchase_price) |
+-----------------+---------------------+
|       2097.5000 |           2035.0000 |
+-----------------+---------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

purchase_price 的计算式：（500+320+2800+2800+500+790） / 6 = 2035

需要注意的是分母是 6 而不是 8，减少的两个也就是那两条 NULL​ 的数据。

‍

# max 和 min 函数

想要计算出多条记录中的最大值或最小值，可以分别使用 MAX 和 MIN 函数，它们是英语 maximam（最大值）和 minimum（最小值）的缩写，很容易记住。

这两个函数的语法与 SUM 的语法相同，使用时需要将列作为参数。举例：

SELECT MAX(sale_price), MIN(purchase_price)
  FROM Product;

--结果：
+-----------------+---------------------+
| MAX(sale_price) | MIN(purchase_price) |
+-----------------+---------------------+
|            6800 |                 320 |
+-----------------+---------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

‍

注意：SUM/AVG 函数只能对数值类型的列使用，而 MAX/MIN 函数上可以适用于任何数据类型的列，例如日期和字符串类型（因为这两个类型是可以比较出大小的）

‍

# 聚合函数和 distinct

在 select 基础中，我们说过想要知道商品的种类，可以用 distinct 关键字来删除重复行：

SELECT DISTINCT product_type FROM Product;

--结果
+--------------+
| product_type |
+--------------+
| 衣服         |
| 办公用品     |
| 厨房用具     |
+--------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

‍

如果想要计算出商品种类的个数呢？使用 distinct 去除重复行后，再自己数也行；但有个更快的方法：使用 count 的时候，在参数上加上 distinct：

SELECT COUNT(DISTINCT product_type)
  FROM Product;
--结果：
+------------------------------+
| COUNT(DISTINCT product_type) |
+------------------------------+
|                            3 |
+------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

‍

注意，这时 distinct 必须写在括号中。这是因为必须要在计算行数之前删除 product_type 列中的重复数据。

如果 distinct 写在括号外的话，就会先计算出数据行数，然后再删除重复数据；但是计算出来的数据就是 8，再删除重复行也是 8，所以是无效计算。

如果在 sum/avg 函数中传入 distinct 关键字，也是一样的，会删除重复行后再计算

‍

‍

# 分组

‍

# group by

之前我们讲的聚合函数，都是对表中的所有数据进行汇总处理。有时候，我们需要将表先分成几组，然后再汇总。这里我们可以用 group by 子句，语法结构：

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;
1
2
3

‍

例如，如果想要计算每个商品种类，各有多少个商品，那么就得先将表按商品种类分组，然后再计算每一组有多少行：

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;

--结果：
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
| 办公用品     |        2 |
| 厨房用具     |        4 |
+--------------+----------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

‍

使用 GROUP BY 子句时，会将表中的数据分为多个组进行处理。如图：

​​

‍

在 GROUP BY 子句中指定的列称为聚合键 或者分组列 。由于能够决定表的切分方式，所以是非常重要的列。当然，GROUP BY 子句也和 SELECT 子句一样，可以通过逗号分隔指定多列。

此外，GROUP BY 子句的书写位置也有严格要求，一定要写在 FROM 语句之后（如果有 WHERE 子句的话需要写在 WHERE 子句之后）。

‍

目前 SQL 的子句还没有全部登场，已经出现的各子句的暂定顺序：SELECT → FROM → WHERE → GROUP BY

‍

另外需要注意的是，GROUP BY 子句的结果通常都包含多行，这些结果是随机排序的。

‍

‍

# 聚合键包含 null 的情况

当聚合键中包含 NULL 时，也会将 NULL 作为一组特定的数据。例如，将进货单价（purchase_price ）作为聚合键：

SELECT purchase_price, COUNT(*)
  FROM Product
 GROUP BY purchase_price;

--结果：
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
|            500 |        1 |
|            320 |        1 |
|           2800 |        2 |
|           5000 |        1 |
|           NULL |        2 |
|            790 |        1 |
+----------------+----------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

‍

示意图：​

​​

‍

# 使用 where 时的分组结果

在使用了 GROUP BY​ 子句的 SELECT​ 语句中，也可以正常使用 WHERE​ 子句。首会先根据 WHERE​ 子句指定的条件进行过滤，然后再进行汇总处理。例如：

SELECT purchase_price, COUNT(*)
  FROM Product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;

--结果：
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
|            500 |        1 |
|           2800 |        1 |
+----------------+----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

‍

执行过程分析：

首先会根据 WHERE​ 子句对记录进行过滤，得到的结果只有 2 行：

​product_type​
（商品种类）		 ​product_name​
（商品名称）		 ​product_id​
（商品编号）		 ​sale_price​
（销售单价）		 ​purchase_price​
（进货单价）		 ​regist_date​
（登记日期）
衣服 T 恤衫 ​0001​ ​1000​ ​500​ ​2009-09-20​
衣服 运动 T 恤 ​0003​ ​4000​ ​2800​

‍

然后再对这个结果进行分组，得到的结果就是这样了：

+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
|            500 |        1 |
|           2800 |        1 |
+----------------+----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7

​GROUP BY​ 和 WHERE​ 并用时，SQL 语句的执行顺序是这样的：FROM​ → WHERE​ → GROUP BY​ → SELECT​。

这与子句的书写顺序有些不同，这是由于在 SQL 语句中，书写顺序和 DBMS 内部的执行顺序并不相同。这也是 SQL 难以理解的原因之一。

‍

# 常见错误

目前，我们已经学习了聚合函数和 GROUP BY 子句的基本使用方法。由于方便，使用频率高，但也容易出错。下面说一些常见错误：

# 在 SELECT 子句中书写了多余的列

使用聚合函数时，SELECT 子句中只能写这些元素：

  • 常数
  • 聚合函数
  • GROUP BY 子句中指定的列名（也就是聚合键）

‍

在 select 基础中讲过，直接书写常数相当于为结果添加一列，这没什么问题；写聚合函数也没什么问题（之前一直都在用 count，sum 等函数）。

经常会出现的错误就是把聚合键之外的列名书写在 SELECT 子句之中，例如：

SELECT product_name, purchase_price, COUNT(*)
  FROM Product
 GROUP BY purchase_price;

--结果：
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.Product.product_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
1
2
3
4
5
6

‍

可以看到执行失败，列 product_name 并不在 group by 子句中。

‍

不支持这种语法的原因，大家仔细想一想应该就明白了。我们分析下执行顺序：

  1. 从 Product 表中取出全部数据
  2. 然后根据 purchase_price 分组
  3. 此时，purchase_price 为 2800 的为一组
  4. 但是，purchase_price 为 2800 的一组中，product_name 是不同的（一个是运动 T 恤，一个是菜刀）
  5. 那么，select 到底是显示哪一个 product_name 呢？DBMS 也不知道，所以发生了错误

​​

​

# 在 GROUP BY 子句中写了列的别名

之前我们学过，SELECT 子句中的项目可以通过 AS 关键字来指定别名。但是，在 GROUP BY 子句中是不能使用别名的，例如这样：

select product_type as pt, count(*)
from Product
group by pt;
1
2
3

‍

为什么会出错？这就是执行顺序的问题了，group by 先执行，它是不知道 select 中定义的别名的

在部分 DBMS 中，可能这样写不会出错，但这不是通用的写法，因此尽量别用

‍

‍

# 在 WHERE 子句中使用聚合函数

举个例子，我们目前根据商品分类进行分组，并计算每一类商品的数量：

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;

--结果：
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
| 办公用品     |        2 |
| 厨房用具     |        4 |
+--------------+----------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

‍

‍

‍

如果我们想要取出恰好包含 2 行数据的组该怎么办呢？满足要求的是“办公用品”和“衣服”。

那么能用 where 吗？试试看：

SELECT product_type, COUNT(*)  
FROM Product 
WHERE COUNT(*) = 2
 GROUP BY product_type;

--结果：
ERROR 1111 (HY000): Invalid use of group function
1
2
3
4
5
6
7

‍

当然也是不行的，这也是因为子句的执行顺序。

‍

# 为聚合结果指定条件

‍

# having 子句

在分组后，如何指定条件来寻去某一组呢？例如选出“聚合结果正好为 2 行的组”？

可以用 having 子句，语法如下

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……
HAVING <分组结果对应的条件>
1
2
3
4

‍

‍

HAVING 子句必须写在 GROUP BY 子句之后，其在 DBMS 内部的执行顺序也排在 GROUP BY 子句之后。

现在我们就可以对分组结果指定条件了：

SELECT product_type, COUNT(*)  
FROM Product 
GROUP BY product_type
HAVING COUNT(*) = 2;

--结果：
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
| 办公用品     |        2 |
+--------------+----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

‍

执行顺序分析：首先得到分组结果如下

product_type COUNT(*)
厨房用具 4
衣服 2
办公用品 2

‍

然后再根据 having 子句中的条件，对这个结果进行过滤，得到 COUNT (*) 为 2 的数据。

‍

再比如，我们想要看看哪一类商品的销售单价的平均值大于等于 2500 元，那么就先分组，然后在 having 子句中用 avg 函数：

SELECT product_type, AVG(sale_price)
  FROM Product
 GROUP BY product_type
HAVING AVG(sale_price) >= 2500;
--结果：
+--------------+-----------------+
| product_type | AVG(sale_price) |
+--------------+-----------------+
| 衣服         |       2500.0000 |
| 厨房用具     |       2795.0000 |
+--------------+-----------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

‍

# having 子句的限制

HAVING 子句和包含 GROUP BY 子句时的 SELECT 子句一样，能够使用的要素有一定的限制，限制内容也是完全相同的。HAVING 子句中能够使用的 3 种要素如下所示。

  • 常数
  • 聚合函数
  • GROUP BY 子句中指定的列名（即聚合键）

‍

之前我们这样写：

SELECT product_type, COUNT(*)  
FROM Product 
GROUP BY product_type
HAVING COUNT(*) = 2;
1
2
3
4

其中 COUNT( * ) 是聚合函数，2 是常数，全都满足上述要求。

‍

但如果这样写：

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type
HAVING product_name = '圆珠笔';

--结果：
ERROR 1054 (42S22): Unknown column 'product_name' in 'having clause'
1
2
3
4
5
6
7

这也很好理解，having 子句是在 group by 子句后执行的，在 group by 子句，结果是这样：

product_type COUNT(*)
厨房用具 4
衣服 2
办公用品 2

这个结果并没有 product_name 列，因此当然会失败。

‍

‍

# 使用 having 还是 where？

有些条件既可以写在 having 子句中，也可以写在 where 子句中，例如统计“衣服”类商品的数量。如果用 having：

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type
HAVING product_type = '衣服';

--结果：
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
+--------------+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

‍

如果用 where：

SELECT product_type, COUNT(*)
  FROM Product
WHERE product_type = '衣服'
 GROUP BY product_type;

--结果：
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
+--------------+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

‍

可以看到结果是一样的。貌似两种写法都可以。

其实，从以下角度来看，用 where 字句更好：

  • 可读性
  • 性能

可读性：WHERE 子句和 HAVING 子句的作用不同。如前所述，HAVING 子句是用来指定“组”的条件的。因此，“行”所对应的条件还是应该写在 WHERE 子句当中。这样一来，书写出的 SELECT 语句不但可以分清两者各自的功能，理解起来也更加容易。

WHERE 子句 = 指定行所对应的条件

HAVING 子句 = 指定组所对应的条件

性能：现在讲性能可能太早了，这里简单说下，不理解也没关系。

将条件写在 WHERE 子句中要比写在 HAVING 子句中的处理速度更快，返回结果所需的时间更短。

如果不使用 where，那么使用聚合函数时，DBMS 会对全表的数据进行处理；

而如果使用了 where，那么 DBMS 会先根据 where 的条件，过滤一遍全表的数据，得到的结果是比全表的数据要少的，此时再用聚合函数进行处理；而由于数据量比较小，处理速度也更快了

此外，WHERE 子句更具速度优势的另一个理由是，可以对 WHERE 子句指定条件所对应的列创建索引 ，这样也可以大幅提高处理速度。

创建索引是一种非常普遍的提高 DBMS 性能的方法，效果也十分明显，后续再详细说明

‍

‍

# 推荐阅读

关于 group by 的用法 原理-CSDN 博客 (opens new window)：配合图片来讲解，可以更好地理解 group by

看一遍就理解：数据库 group by 详解 (opens new window)：讲的比较深，可以等后面再回过头来看该文

容易被轻视的主角，神奇的 SQL 之 HAVING (opens new window)

‍

‍

（完）

‍

‍

‍

上次更新: 2024/12/4 21:15:24
运算符
数据的排序

← 数据的排序→

最近更新
01
我们这代人存不下钱的原因是什么？ - 知乎 转载
12-13
02
互联网越来越孤岛化了
12-06
03
2024 年 11 月记
11-30
更多文章>
Theme by Vdoing | Copyright © 2022-2024 | 粤 ICP 备 2022067627 号 -1 | 粤公网安备 44011302003646 号 | 点击查看十年之约