聚合函数

本文笔记根据【b站 尚硅谷-宋红康 MySQL 课程】整理。笔记和代码存储在我的 GitHub 库中 github.com/isKage/sql-notes。

不同的数据库管理系统 DBMS 的函数使用差异极大，SQL 语言函数的可移植性很差。

MySQL 提供的内置函数包括单行函数和聚合函数，这章讲解聚合函数。

聚合函数 ：对一组数据进行操作，输入一组数据的集合，返回结果值

1 基础聚合函数

对一组数据进行操作，输入一组数据的集合，返回结果值——例如：求一组数据的最大值，平均值，个数等

1.1 AVG 和 SUM 均值和加和

对于数值型数据：使用 AVG 求这一列数据的平均值；使用 SUM 求这一列数据的加和

1
2
3
4
5
6
7
8
9
10
11
SELECT AVG(e.salary) "avg_salary",
       SUM(e.salary) "sum_salary"
FROM employees e;


+-------------+------------+
| avg_salary  | sum_salary |
+-------------+------------+
| 6461.682243 |  691400.00 |
+-------------+------------+
1 row in set (0.00 sec)

1.2 MIN 和 MAX 最小和最大

对于数值型数据：使用 MIN 求这一列数据的最小值；使用 MAX 求这一列数据的最大值

1
2
3
4
5
6
7
8
9
10
11
SELECT MIN(e.salary) "min_salary",
       MAX(e.salary) "max_salary"
FROM employees e;


+------------+------------+
| min_salary | max_salary |
+------------+------------+
|    2100.00 |   24000.00 |
+------------+------------+
1 row in set (0.00 sec)

对于日期数据：使用 MIN 求这一列最早的日期；使用 MAX 求这一列最晚的日期

1
2
3
4
5
6
7
8
9
10
SELECT MIN(e.hire_date) "early_salary",
       MAX(e.hire_date) "late_salary"
FROM employees e;

+--------------+-------------+
| early_salary | late_salary |
+--------------+-------------+
| 1987-06-17   | 2000-04-21  |
+--------------+-------------+
1 row in set (0.00 sec)

对于字符串数据： MIN 代表这一列在字符表中最早出现； MAX 代表这一列在字符表中最晚出现

1
2
3
4
5
6
7
8
9
10
SELECT MIN(e.last_name) "A...",
       MAX(e.last_name) "Z..."
FROM employees e;

+------+---------+
| A... | Z...    |
+------+---------+
| Abel | Zlotkey |
+------+---------+
1 row in set (0.00 sec)

其他的类型均可使用 MIN 和 MAX ，可以自己尝试

1.3 COUNT 返回记录数

COUNT(*) 返回表中记录总数，适用于任意数据类型，可以理解为对行计数。

可以使用 WHERE增加限制条件

1
2
3
4
5
6
7
8
9
10
SELECT COUNT(*)
FROM employees e
WHERE e.salary >= 5000;

+----------+
| COUNT(*) |
+----------+
|       58 |
+----------+
1 row in set (0.01 sec)

COUNT(列名) 返回不为空的行数

1
2
3
4
5
6
7
8
9
SELECT COUNT(e.commission_pct) "commission_pct not null"
FROM employees e;

+-------------------------+
| commission_pct not null |
+-------------------------+
|                      35 |
+-------------------------+
1 row in set (0.00 sec)

COUNT(*) 会统计值为 NULL 的行，而 COUNT(列名) 不会统计此列值为 NULL 的行

2 GROUP BY 分组查询

GROUP BY 可以依照某些条件/某列对数据进行分组输出

格式

1
2
3
4
5
SELECT 列名1, 列名2, 函数(列名3)
FROM 表名
[WHERE 条件] -- 可加限制条件
GROUP BY 列名
[ORDER BY 列名];  -- 可选

WHERE 条件 一定紧跟表后，无论是 FROM 表名 还是 FROM 表名1 JOIN 表名2 ON ... ，总之 WHERE 一定在表之后

2.1 以单个列为依据分组

  • 例：按照 employees.department_id 分组，并连接表 departments 查询部门名。

展示的列新增 department_name 和平均工资 AVG(e.salary) （此处使用格式化输出，保留 2 位小数，并命名为 “avg_salary_dept”）。采用内连接，去除 NULL ，因为我们要查的是切实存在的部门。GROUP BY 指定按照 e.department_id 分组。

1
2
3
4
5
6
7
SELECT e.department_id, d.department_name, FORMAT(AVG(e.salary), 2) "avg_salary_dept"
FROM employees e
         JOIN departments d
              ON e.department_id = d.department_id
# WHERE e.department_id != 20
GROUP BY e.department_id -- 按照 employees.department_id 分组
ORDER BY avg_salary_dept DESC; -- 按照 avg_salary_dept 列排序

2.2 以多个列为依据分组

  • 例：按 department_id, job_id 分组，并计算 salary

简单的查询

1
2
3
4
SELECT e.department_id dept_id, e.job_id, SUM(salary)
FROM employees e
# WHERE e.department_id IS NOT NULL
GROUP BY department_id, job_id; -- 按照 department_id, job_id 分组

如果想要展示部门名，和工作职位名，同样可以多表连接，下面这个例子综合使用之前章节的知识：GROUP BY 依据 e.department_id, e.job_id 分组，AVG 函数分组计算平均薪水；JOIN 连接多表，查询 dept_name 和 job_name 字段并展示；WHERE 紧跟表后添加限制条件，REGEXP 使用正则表达寻找字段 d.department_name 以 S 开头的字符串。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT e.department_id        dept_id,
       d.department_name      dept_name,
       e.job_id,
       j.job_title            job_name,
       FORMAT(AVG(salary), 2) avg_salary -- 分组计算平均薪水
FROM employees e
         -- 多表连接，查询 dept_name 和 job_name
         JOIN departments d
         JOIN jobs j
              ON e.department_id = d.department_id AND e.job_id = j.job_id
WHERE d.department_name REGEXP '^S' -- 匹配以 S 开头的字符串
GROUP BY e.department_id, e.job_id -- 按照 e.department_id, e.job_id 分组
ORDER BY avg_salary DESC; -- 根据 avg_salary 降序

2.3 WITH ROLLUP 添加记录

在 GROUP BY 后加上 WITH ROLLUP 会为结果添加一条记录（行数据）。

  • 分组依据列返回 NULL
  • 被操作的列，会再次操作，以下面代码为例

当按照 e.department_id 分组且剔除 e.department_id < 30 行数据后，会对计算完新表的 sum_id 和 avg_salary 再进行一次 SUM 和 AVG

1
2
3
4
5
6
7
SELECT e.department_id      id,
       SUM(e.department_id) sum_id,
       AVG(e.salary)        avg_salary
FROM employees e
WHERE e.department_id < 30
GROUP BY e.department_id
WITH ROLLUP; -- 添加一行数据：再进行一次 SUM(sum_id) AVG(avg_salary) 操作

【注意】分组时选择查询的表一定要保证查询后数量相同

【注意】WITH ROLLUP 和 ORDER BY 不能同时使用

【注意】WHERE 可以在查表时进行约束，但不能对 GROUP BY 分组时进行约束

3 GROUP BY … HAVING 有约束的分组

3.1 HAVING 使用聚合函数进行限制

在行已经被分组后，且保证查询列中使用了聚合函数：则满足 HAVING 条件 子句中条件的分组将被显示。

注意：HAVING 不能单独使用，必须和 GROUP BY 一起使用

1
2
3
4
5
6
SELECT 列名1, 列名2, 函数(列名3)
FROM 表名
[WHERE 条件] -- 可加限制条件
GROUP BY 列名
HAVING 条件 -- 分组的过滤条件
[ORDER BY 列名];  -- 可选
  • 例：按部门分组，返回最大工资。然后查询这些最大工资大于 10000 的部门 id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT department_id, MAX(salary) "max_larger_than_10000"
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

+---------------+-----------------------+
| department_id | max_larger_than_10000 |
+---------------+-----------------------+
|            20 |              13000.00 |
|            30 |              11000.00 |
|            80 |              14000.00 |
|            90 |              24000.00 |
|           100 |              12000.00 |
|           110 |              12000.00 |
+---------------+-----------------------+
6 rows in set (0.01 sec)

3.2 HAVING 和 WHERE

【注意】不可使用 WHERE 对聚合函数进行约束

1
2
3
4
5
6
SELECT department_id, MAX(salary) "max_larger_than_10000"
FROM employees
WHERE MAX(salary) > 10000  -- WHERE 后不可加聚合函数
GROUP BY department_id;

ERROR 1111 (HY000): Invalid use of group function
  • WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的聚合函数作为筛选条件
  • HAVING 必须要与 GROUP BY配合使用，可以把分组中的聚合函数和分组字段作为筛选条件
  • 多表连接时，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。所以 WHERE 会效率更高

4 SELECT 语句【总结】

4.1 查询的常见格式

结合之前章节的知识（SELECT 语句、运算符与 SQL 的正则表达式、排序与分页：ORDER BY 子句 和 LIMIT 子句、多表查询、单行函数），我们可以总结常见的 SELECT 语句查询格式：

1
2
3
4
5
6
7
8
9
10
-- 方式1 : 使用 WHERE 连接
SELECT 列名1, 列名2, 列名3, ...
FROM 表名1, 表名2, 表名3
WHERE 多表的连接条件 (不含聚合函数)
	AND 条件
	AND 条件
GROUP BY 列名, 列名, ...
HAVING 过滤条件 (可含聚合函数)
ORDER BY 列名 ASC[DESC]
LIMIT 起始行数, 行数
1
2
3
4
5
6
7
8
9
10
11
12
-- 方式2 : 使用 JOIN 连接
SELECT 列名1, 列名2, 列名3, ...
FROM 表名1 
	JOIN 表名2
		ON 条件
	JOIN 表名3
		ON 条件
WHERE 过滤条件 (不含聚合函数)
GROUP BY 列名, 列名, ...
HAVING 过滤条件 (可含聚合函数)
ORDER BY 列名 ASC[DESC]
LIMIT 起始行数, 行数

4.2 SELECT 语句的执行顺序

  • 关键字必须按照
1
2
3
4
5
6
7
SELECT ...
FROM ... [JOIN ... ON ...]
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ... ASC[DESC]
LIMIT ..., ...
  • SQL 实际执行顺序为
1
2
3
4
5
6
7
FROM ... [JOIN ... ON ...]
WHERE ...
GROUP BY ...
HAVING ...
SELECT ... DISTINCT ...
ORDER BY ... ASC[DESC]
LIMIT ..., ...

即：先找到表（FROM） ，然后根据条件过滤（WHERE）得到虚拟的一张表，然后分组（GROUP BY），分组后再依照聚合函数或分组依据的列等条件进行过滤（HAVING），最后锁定需要输出的字段/列（SELECT），去除重复行数据（DISTINCT）排序（ORDER BY）分页（LIMIT）输出。