[MySQL]聚合函数与分组_mysql分组聚合函数

---

文章目录

• 1. 聚合函数介绍
• • 1.1 什么是聚合函数
  • 1.2 常用的聚合函数
• 2. 常用的聚合函数
• • 2.1 AVG()
  • 2.2 SUM()
  • 2.3 MAX()
  • 2.4 MIN()
  • 2.5 COUNT()
  • 2.6 补充
• 3. GROUP BY
• • 3.1 分组的基本使用
  • 3.2 使用多个列分组
  • 3.3 结论
  • 3.4 WITH ROLLUP
• 4. HAVING
• • 4.1 HAVING 的使用
  • 4.2 HAVING 与 WHERE 的对比
• 5. SELECT 的执行过程
• • 5.1 SELECT 语句的完整结构
  • 5.2 SELECT 的执行顺序
• 6. 聚合函数练习
• • 6.1 题目
  • 6.2 解答

---

1. 聚合函数介绍

1.1 什么是聚合函数

聚合（或聚集、分组）函数，是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

聚合函数作用于一组数据，并对一组数据返回一个值。

1.2 常用的聚合函数

• AVG()
• SUM()
• MAX()
• MIN()
• COUNT()

2. 常用的聚合函数

2.1 AVG()

只适用于数值类型的字段(或变量)

# 查询所有员工的平均工资
SELECT AVG(salary)
FROM employees;
1
2
3

2.2 SUM()

只适用于数值类型的字段(或变量)

# 查询所有员工的工资总和
SELECT
       SUM(salary),
       # 一共有107个员工
       AVG(salary) * 107
FROM employees;
1
2
3
4
5
6

2.3 MAX()

# 查询员工的最高工资
SELECT MAX(salary)
FROM employees;
1
2
3

MAX()适用于数值类型、字符串类型、日期时间类型等可以进行大小比较的字段(变量)。

SELECT MAX(last_name),
       MAX(hire_date)
FROM employees;
1
2
3

2.4 MIN()

# 查询员工的最低工资
SELECT MIN(salary)
FROM employees;
1
2
3

MIN()适用于数值类型、字符串类型、日期时间类型等可以进行大小比较的字段(变量)。

SELECT MIN(last_name),
       MIN(hire_date)
FROM employees;
1
2
3

2.5 COUNT()

计算指定字段在查询结果中出现的个数。

# 查询在全部的107行数据中，有几行拥有员工id这个字段
SELECT COUNT(employee_id)
FROM employees;
1
2
3

COUNT()统计个数，不关注字段的具体取值为多少，只关注某行数据是否具有该字段，该字段是否为空。不为空就加入计数。

SELECT COUNT(employee_id),
       count(salary),
       count(salary * 2)
FROM employees;
1
2
3
4

COUNT()统计常量

SELECT COUNT(employee_id),
       count(salary),
       count(salary * 2),
       count(1)
FROM employees;
1
2
3
4
5

计算表中有多少条记录：

SELECT COUNT(1),
       COUNT(*)
FROM employees;
1
2
3

不建议通过统计某个字段的取值的个数来统计表格的行数，因为有些字段的某些行可能取值为空，而COUNT()不会统计空值。

2.6 补充

AVG()、SUM()和COUNT()一样，会过滤空值。

SELECT AVG(commission_pct),
       SUM(commission_pct) / COUNT(commission_pct),
       # 由于AVG() SUM() COUNT() 都会过滤空值
       # 所以SUM(commission_pct) / 107会比上面的小
       SUM(commission_pct) / 107
FROM employees;
1
2
3
4
5
6

# 计算公司的平均奖金率
# 错误计算方法
# 因为有没有奖金的员工，而AVG()会默认过滤空值
# 所以不能使用这种计算方法
SELECT AVG(commission_pct)
FROM employees;
# 正确的计算方法
# 对于没有奖金的员工commission_pct=0，然后进行统计
SELECT AVG(IFNULL(commission_pct, 0)),
       SUM(commission_pct) / COUNT(*),
       SUM(IFNULL(commission_pct, 0)) / COUNT(*)
FROM employees;
1
2
3
4
5
6
7
8
9
10
11
12

如何需要统计表中的记录数，使用COUNT(*)、COUNT(1)、COUNT(具体字段) 哪个效率更高呢？
如果使用的是MyISAM 存储引擎，则三者效率相同，都是O(1)
如果使用的是InnoDB 存储引擎，则三者效率：COUNT(*) = COUNT(1)> COUNT(字段)

3. GROUP BY

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

3.1 分组的基本使用

# 查询各个部门的员工的平均工资
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
1
2
3
4

# 查询每个工作的平均工资
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id;
1
2
3
4

3.2 使用多个列分组

# 查询各个(department_id, job_id)的平均工资
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id;
1
2
3
4

分组的字段可以调换顺序，结果一致。

最后结果都为(department_id, job_id)一样的为同一组，只是分组的顺序不一样，一个先根据部门分组再根据工作分组，一个根据工作分组再根据部门分组，最终显示的结果一样。

# 查询各个(job_id, department_id)的平均工资
SELECT job_id, department_id, AVG(salary)
FROM employees
GROUP BY job_id, department_id;
1
2
3
4

3.3 结论

1. 结论1：
   SELECT中出现的非组函数的字段必须声明在GROUP BY 中。反之，GROUP BY中声明的字段可以不出现在SELECT中。
2. 结论2：
   GROUP BY 声明在FROM后面、WHERE后面，ORDER BY 前面、LIMIT前面

3.4 WITH ROLLUP

MySQL中GROUP BY中使用WITH ROLLUP，在所有查询出的分组记录之后增加一条记录，该记录是对所有组在进行一次统计，比如计算每组的平均工资，WITH ROLLUP新加的记录会计算每组合起来总的平均工资，即公司的平均工资。

SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;
1
2
3

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

MySQL8.0，不报错，WITH ROLLUP新加记录也会参与排序

SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;
1
2
3
4

4. HAVING

4.1 HAVING 的使用

HAVING子句用于过滤分组之后的数据。

1、如果过滤条件中使用了聚合函数，或行已经被分组，则必须使用HAVING来替换WHERE。否则，报错。
2、HAVING 必须声明在 GROUP BY 的后面。

# 查询各个部门中最高工资大于10000的部门信息
SELECT department_id, MAX(salary) max_salary
FROM employees
GROUP BY department_id
# 对分组后的数据进行筛选，选出部门中最高工资大于10000的部门
HAVING max_salary > 10000;
1
2
3
4
5
6

开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。

如果没有使用GROUP BY，使用HAVING，则整个表中的所有记录会被当成一组，然后对这一组记录根据HAVING中的条件进行筛选。

SELECT department_id, MAX(salary) max_salary
FROM employees
HAVING max_salary > 10000;
1
2
3

HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

# 查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
SELECT department_id, MAX(salary) max_salary
FROM employees
WHERE department_id in (10, 20, 30, 40)
GROUP BY department_id
HAVING max_salary > 10000;
1
2
3
4
5
6

# 查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
SELECT department_id, MAX(salary) max_salary
FROM employees
GROUP BY department_id
# 对记录进行分组后，筛选出部门id为10, 20, 30, 40的组
HAVING department_id in (10, 20, 30, 40) AND
       max_salary > 10000;
1
2
3
4
5
6
7

1、当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
2、当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。但是，建议大家声明在WHERE中。

上述两种方式，方式一的执行效率高于方式二，即WHERE的效率会高于HAVING。

4.2 HAVING 与 WHERE 的对比

1. 从适用范围来讲，HAVING 的使用范围更广。
2. 如果过滤条件中没有聚合函数：这种情况下，WHERE的执行效率要高于HAVING。
   • WHERE会先过滤掉无用的数据，然后对过滤后的数据进行分组筛选操作，分组筛选操作的数据数小；而HAVING是先对所有的数据进行分组筛选，操作的数据数大，所以WHERE的执行效率要高于HAVING。

5. SELECT 的执行过程

5.1 SELECT 语句的完整结构

#sql92语法：
SELECT ...., ...., ....(存在聚合函数)
FROM ..., ...., ....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ..., ....
HAVING 包含聚合函数的过滤条件
ORDER BY ...., ...(ASC / DESC )
LIMIT ..., ....
1
2
3
4
5
6
7
8

#sql99语法：
SELECT ...., ...., ....(存在聚合函数)
FROM ... 
(LEFT / RIGHT)JOIN ....ON 多表的连接条件 
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ..., ....
HAVING 包含聚合函数的过滤条件
ORDER BY ...., ...(ASC / DESC )
LIMIT ..., ....
1
2
3
4
5
6
7
8
9
10

5.2 SELECT 的执行顺序

FROM ..., ...-> 
ON -> 
(LEFT/RIGNT  JOIN) -> 
WHERE -> 
GROUP BY -> 
HAVING -> 
SELECT -> 
DISTINCT -> 
ORDER BY -> 
LIMIT
1
2
3
4
5
6
7
8
9
10

由于GROUP BY分组操作的执行在WHERE之后，所以不能使用WHERE过滤分组后的数据；
由于SELECT的执行时间在WHERE之后，所以在WHERE中不能使用SELECT中字段的别名。

6. 聚合函数练习

6.1 题目

#1.where子句可否使用组函数进行过滤? 

#2.查询公司员工工资的最大值，最小值，平均值，总和 

#3.查询各job_id的员工工资的最大值，最小值，平均值，总和 

#4.选择具有各个job_id的员工人数 

# 5.查询员工最高工资和最低工资的差距（DIFFERENCE） 

# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内 

# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序 

# 8.查询每个工种、每个部门的部门名、工种名和最低工资
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

6.2 解答

1.where子句可否使用组函数进行过滤?

不能，使用组函数对数据进行过滤只能使用HAVING
1

2.查询公司员工工资的最大值，最小值，平均值，总和

# 2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),
       MIN(salary),
       AVG(salary),
       SUM(salary)
FROM employees;
1
2
3
4
5
6

3.查询各job_id的员工工资的最大值，最小值，平均值，总和

# 3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT job_id,
       MAX(salary),
       MIN(salary),
       AVG(salary),
       SUM(salary)
FROM employees
GROUP BY job_id;
1
2
3
4
5
6
7
8

4.查询各个job_id的员工人数

# 4.查询各个job_id的员工人数
SELECT job_id,
       COUNT(*)
FROM employees
GROUP BY job_id;
1
2
3
4
5

5.查询员工最高工资和最低工资的差距（DIFFERENCE）

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

6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

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

7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序

# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT d.department_name,
       d.location_id,
       # 由于有些部门没有员工，由于连接表的方式为左连接，
       # 没有员工的部门也存在一条记录，使用COUNT(*)，统计记录数
       # 没有员工的部门会统计出来1，所以不使用COUNT(*)使用COUNT(e.employee_id)
       # 统计每个部门的员工
       COUNT(e.employee_id),
       # 没有员工的部门，salary为null，计算出来为null
       AVG(e.salary) avg_sal
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
# 出现在SELECT子句中的字段必须也要出现在GROUP BY子句中
GROUP BY d.department_name, d.location_id
ORDER BY avg_sal DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

# 8.查询每个工种、每个部门的部门名、工种名和最低工资
# 由于有些部门没有员工所以这些部门的工种以及对应的最低工资为null
SELECT e.job_id,
       d.department_name,
       MIN(e.salary)
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY e.job_id, d.department_name;
1
2
3
4
5
6
7
8