MySQL基础篇——第08章 聚合函数

MySQL基础篇——第08章 聚合(分组)函数

1. 常用聚合(分组)函数——Group Function

• 聚合(分组)函数，是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值
• 分组函数在 SELECT 或 HAVING 子句中使用，表示对每一组数据的操作。若仅在 SELECT 中使用，且未使用 GROUP BY 分组，则表示对表中所有数据的操作
• 常用的聚合函数：AVG() / SUM() / MIN() / MAX() / COUNT()
• 聚合函数不能嵌套调用。比如不能出现类似 AVG(SUM(字段名称)) 形式的调用

1.1 AVG() 和 SUM()

• AVG() 和 SUM() 对当前分组中所有记录的某个字段求平均(或求和)

• AVG() 和 SUM() 函数只适用于数值类型的字段（或变量），不适用于字符串类型或日期时间类型的字段（或变量）

• AVG() 和 SUM() 在计算过程中，自动把当前字段中的NULL值过滤掉了

SELECT AVG(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

1.2 MIN() 和 MAX()

• MIN() 和 MAX() 返回当前分组中，所有记录某个字段的最小值(或最大值)

• MIN() 和 MAX() 函数适用于任意数据类型的字段（或变量）

• MIN() 和 MAX() 在计算过程中，自动把当前字段中的NULL值过滤掉了

SELECT MIN(salary), MAX(salary), MIN(last_name), MAX(last_name), MIN(hire_date), MAX(hire_date)
FROM employees;

1.3 COUNT()

• COUNT(*) / COUNT(常数) 返回当前分组的记录(行)总数，适用于任意数据类型**

SELECT COUNT(*), COUNT(1)
FROM employees
WHERE department_id = 50;

• COUNT(字段) 返回当前分组中，该字段(列)不为NULL的记录(行)总数

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;

• 由于 AVG() 和 SUM() 在计算过程中，自动过滤NULL值，故满足 AVG(字段) = SUM(字段) / COUNT(字段)

# 二者是等价的
SELECT AVG(commission_pct), SUM(commission_pct) / COUNT(commission_pct)
FROM employees;

---

• 举例：查询公司所有员工平均奖金率

# 错误写法：
SELECT AVG(commission_pct)
FROM employees;

# 正确写法：
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct, 0))
FROM employees;
# 或
SELECT AVG(IFNULL(commission_pct, 0))
FROM employees;

• 统计当前分组的记录数，使用 COUNT(*) / COUNT(常数) / COUNT(字段) 哪个效率更高？
  • 如果使用的是MyISAM存储引擎，则三者效率相同，都是 o(1)
  • 如果使用的是Innodb存储引擎，则三者效率： COUNT(*) = COUNT(常数) > COUNT(字段)

2. GROUP BY 分组操作

2.1 基本使用

可以使用 GROUP BY 子句将表中的数据分成若干组

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

1. 使用多个字段(列)进行分组时， GROUP BY 子句中字段声明的先后顺序对查询的结果没有影响
2. SELECT 子句中声明的所有未包含在聚合(分组)函数中的字段，都必须声明在 GROUP BY 子句中

• 在查询的结果集中，每组的某个字段理论上只能取一个值，代表该组这一字段的值。如果某个字段只在 SELECT 子句中声明，而未在 GROUP BY 中声明，则无法确定该字段的取值

3. 反之，GROUP BY 子句中声明的字段可以不出现在 SELECT 子句中，即可以不在结果集中显示
4. 声明顺序：SELECT —> FROM —> (JOIN …… ON) —> WHERE —> GROUP BY —> HAVING —> ORDER BY —> LIMIT

---

• 举例：查询 employees 表中各部门的平均工资

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-luhF8eYR-1652660358875)(MySQL基础篇——第08章 聚合函数.assets/image-20220422145840330.png)]

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id; # 按照`department_id`字段对表中所有记录分组

2.2 使用多个列分组

• 举例：查询 employees 表中各部门各工种的平均工资（按照 department_id 和 job_id 进行分组）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7CSdbyTi-1652660358876)(MySQL基础篇——第08章 聚合函数.assets/image-20220422151458007.png)]

# 方式一：
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;

# 方式二：
SELECT job_id, department_id, SUM(salary)
FROM employees
GROUP BY job_id, department_id;

• 先按照 department_id 分组或先按照 job_id 分组，二者从本质上是一样的。即使用多个字段分组，字段的先后顺序没有影响

2.3 GROUP BY 中使用 WITH ROLLUP

使用 WITH ROLLUP 关键字，在所有查询出的分组记录之后增加一条记录，该记录在分组统计数据基础上再进行相同的统计（SUM, AVG, COUNT …）

SELECT department_id, AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

注意：当使用 ROLLUP 时，不能同时使用 ORDER BY 子句进行结果排序，即 ROLLUP 和 ORDER BY 是互相排斥的

3. HAVING 对分组进行过滤

3.1 基本使用

可以使用 HAVING 子句将表中的数据分成若干组

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

1. 如果过滤条件中使用了聚合(分组)函数，则必须使用 HAVING 来替换 WHERE
   • 非法使用聚合函数：不能在 WHERE 子句中使用聚合函数
2. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用，且必须紧跟 GROUP BY 子句之后
3. HAVING 的使用场景是表中的记录(行)已经使用 GROUP BY 进行了分组操作
4. 满足 HAVING 子句中条件的分组将显示在结果集中

---

• 举例1：查询部门最高工资比1000高的部门

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XfThlft1-1652660358877)(MySQL基础篇——第08章 聚合函数.assets/image-20220422210804356.png)]

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

• 举例2：查询部分id为10，20，30，40这四个部门中最高工资比10000高的部门

# 方式一：将普通条件写在WHERE子句中，将包含分组函数的条件写在HAVING子句中（推荐，执行效率高）
SELECT department_id, MAX(salary)
FROM employees
WHERE department_id IN (10, 20, 30, 40)
GROUP BY department_id
HAVING MAX(salary) > 10000;

# 方式二：将普通条件和包含分组函数的条件都写在HAVING子句中
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING department_id IN (10, 20, 30, 40)
AND MAX(salary) > 10000;

3.2 WHERE 和 HAVING 的对比

• 区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组函数和分组字段作为筛选条件

  • 在查询语法结构中，WHERE 在 GROUP BY 之前执行，即在分组之前就执行了，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后执行，故可以使用分组函数和分组字段，对分组的结果集进行筛选
  • WHERE排除的记录不再包括在分组中

• 区别2：如果需要通过连接从关联表中获取数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选

  • 在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高
  • HAVING 则使用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低

	优点	缺点
WHERE	先筛选数据再关联，执行效率高	不能使用分组函数进行筛选
HAVING	可以使用分组函数，对分组的结果集进行筛选	在最后的结果集中进行筛选，执行效率较低

• 开发中的选择：
  • WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别

4. 总结：SELECT 查询语句

4.1 SELECT 语句的完整结构

# 方式一：SQL92语法
SELECT [DISTINCT] ..., ...., ...
FROM ..., ..., ....
WHERE 多表的连接条件 AND/OR 不包含分组函数的过滤条件
GROUP BY ..., ...
HAVING 包含分组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ..., ...

# 方式二：SQL99语法
SELECT [DISTINCT] ..., ...., ...
FROM ... (LEFT/RIGHT)JOIN ... ON 多表的连接条件
(LEFT/RIGHT)JOIN ... ON ...
WHERE 不包含分组函数的过滤条件 AND/OR 不包含分组函数的过滤条件
GROUP BY ..., ...
HAVING 包含分组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ..., ...

# 其中：
#（1）FROM：从哪些表中筛选
#（2）ON：关联多表查询时，去除笛卡尔积
#（3）WHERE：从表中筛选的条件
#（4）GROUP BY：分组依据
#（5）HAVING：在分组统计结果中再次筛选
#（6）ORDER BY：排序
#（7）LIMIT：分页

4.2 SELECT 语句底层的执行顺序

FROM -> ON -> (LEFT/RIGHT) JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

---

1. 首先执行 FROM 子句。如果是多表联查，还会经历以下步骤：

   • 通过 JOIN 求笛卡尔积，得到 虚拟表 vt1-1
   • 通过 ON 的连接条件对 虚拟表 vt1-1 进行筛选，得到 虚拟表 vt1-2
   • 如果使用左外连接或右外连接，则在 虚拟表 vt1-2 的基础上添加外部行，得到 虚拟表 vt1-3
   • 如果操作的是两张以上的表，即有多个 JOIN ... ON 子句，则重复上述步骤。最终得到 虚拟表 vt1

2. 执行 WHERE 子句。根据不包含分组函数的条件过滤 虚拟表 vt1 的记录，得到 虚拟表 vt2 。此时的表包含所有字段(列)

3. 执行 GROUP BY 子句。根据分组字段对 虚拟表 vt2 进行分组，得到 虚拟表 vt3

4. 执行 HAVING 子句。根据包含分组函数的条件对 虚拟表 vt3 中的分组进行过滤，保留满足条件的组，得到 虚拟表 vt4

---

5. 执行 SELECT 子句。选择指定的字段(列)，并给字段取别名；然后执行 DISTINCT，过滤重复记录(行)，得到 虚拟表 vt5

---

6. 执行 ORDER BY 子句。按照指定字段对 虚拟表 vt5 进行排序，得到 虚拟表 vt6
7. 执行 LIMIT 子句。根据指定的位置偏移量和条目数，取出 虚拟表 vt6 中的指定记录(行)，得到最终的结果 虚拟表 vt7

5. 课后练习

# 1.where子句可否使用组函数进行过滤?
# 不可以！`WHERE`在`GROUP BY`之前执行，即在分组之前就执行了，所以无法使用组函数对分组结果进行过滤

# 2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary) "max_sal", MIN(salary) "min_sal", AVG(salary) "avg_sal", SUM(salary) "sum_sal"
FROM employees;

# 3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT job_id, MAX(salary) "max_sal", MIN(salary) "min_sal", AVG(salary) "avg_sal", SUM(salary) "sum_sal"
FROM employees
GROUP BY job_id;

# 4.选择具有各个job_id的员工人数
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;

# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary) - MIN(salary) "DIFFERENCE"
FROM employees;

# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT manager_id, MIN(salary) "min_sal"
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;

# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT d.`department_name`, d.`location_id`, COUNT(e.`employee_id`), AVG(e.`salary`) "avg_sal"
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY d.`department_name`, d.`location_id` # SELECT子句中声明的所有未包含在分组函数中的字段，必须声明在GROUP BY中
ORDER BY avg_sal DESC;

# 8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT d.`department_name`, e.`job_id`, MIN(e.`salary`) "min_sal"
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY d.`department_name`, e.`job_id`;

name, d.location_id` # SELECT子句中声明的所有未包含在分组函数中的字段，必须声明在GROUP BY中
ORDER BY avg_sal DESC;

8.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT d.department_name, e.job_id, MIN(e.salary) “min_sal”
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_name, e.job_id;