8. 聚合函数

8.1 聚合函数介绍

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

• 聚合函数传参需要是一个表中的字段名

• 聚合函数无法嵌套使用

  # 例如这样是不行的
  AVG(SUM(字段名))
  # SUM(字段名)是一个数值，但是AVG需要传入字段名，所以无法嵌套使用
  

基本使用

# 只有数值型数据可以使用AVG()和SUM()
# 查看工作带有REP的员工的平均工资、总工资、最大工资、最小工资
SELECT AVG(salary),SUM(salary),MIN(salary),MAX(salary)
FROM employees 
WHERE job_id LIKE "%REP%";

# 任何数据都可以用MIN()和MAX()，例如数值，字符串和日期
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;

# COUNT()计数函数，作用于任何类型
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

8.2 GROUP BY

单列分组

GROUP BY的作用就是分组，可以将表中的数据分为若干组

# 先来看看分组后的表格是什么样的
SELECT *
FROM employees
GROUP BY department_id;

最终的结果却是只有单一的department_id，但是其实对应的数据是表中出现的对应department_id对应的第一条数据。

# 实际使用
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
/*
+---------------+--------------+
| department_id | AVG(salary)  |
+---------------+--------------+
| NULL          |  7000.000000 |
|            10 |  4400.000000 |
|            20 |  9500.000000 |
|            30 |  4150.000000 |
|            40 |  6500.000000 |
|            50 |  3475.555556 |
|            60 |  5760.000000 |
|            70 | 10000.000000 |
|            80 |  8955.882353 |
|            90 | 19333.333333 |
|           100 |  8600.000000 |
|           110 | 10150.000000 |
+---------------+--------------+
12 rows in set (0.04 sec)
*/

分类的字段不用包含在SELECT中

多列分组

# GROUP BY可以对多个列进行分组操作
# 多个列分组操作实质上就是排列组合
# 假设字段一有A、B，字段二有a，b，这两个分组后会有四个组Aa,Ab,Ba,Bb
# 根据部门和工作分组查询工资
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;
/*
+---------------+------------+-------------+
| department_id | job_id     | SUM(salary) |
+---------------+------------+-------------+
|            90 | AD_PRES    |    24000.00 |
|            90 | AD_VP      |    34000.00 |
|            60 | IT_PROG    |    28800.00 |
|           100 | FI_MGR     |    12000.00 |
|           100 | FI_ACCOUNT |    39600.00 |
|            30 | PU_MAN     |    11000.00 |
|            30 | PU_CLERK   |    13900.00 |
|            50 | ST_MAN     |    36400.00 |
|            50 | ST_CLERK   |    55700.00 |
|            80 | SA_MAN     |    61000.00 |
|            80 | SA_REP     |   243500.00 |
| NULL          | SA_REP     |     7000.00 |
|            50 | SH_CLERK   |    64300.00 |
|            10 | AD_ASST    |     4400.00 |
|            20 | MK_MAN     |    13000.00 |
|            20 | MK_REP     |     6000.00 |
|            40 | HR_REP     |     6500.00 |
|            70 | PR_REP     |    10000.00 |
|           110 | AC_MGR     |    12000.00 |
|           110 | AC_ACCOUNT |     8300.00 |
+---------------+------------+-------------+
20 rows in set (0.06 sec)
*/

WITH ROLLUP

使用WITH ROLLUP之后，查询的分组记录之后会增加一条记录，该记录会对聚合函数求所有记录的聚合函数，其余字段为NULL。

SELECT department_id,AVG(salary),COUNT(department_id)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;
/*
+---------------+--------------+----------------------+
| department_id | AVG(salary)  | COUNT(department_id) |
+---------------+--------------+----------------------+
|            90 | 19333.333333 |                    3 |
|           100 |  8600.000000 |                    6 |
|           110 | 10150.000000 |                    2 |
| NULL          | 11809.090909 |                   11 |
+---------------+--------------+----------------------+
4 rows in set (0.06 sec)
*/

注：使用ROLLUP的时候，不能用ORDER BY字句进行排序，两者互斥

8.3 HAVING

HAVING的使用

HAVING需要和GROUP BY配合使用，相当于GROUP BY中的WHERE条件，但是WHERE不能使用聚合函数，因此需要用HAVING。

HAVING使用

• 行已经被分组
• 使用了聚合函数
• 满足HAVING的条件的分组会被显示
• HAVING不能单独使用，需要配合GROUP BY

# 查询最大工资大于10000的部门和其最大工资
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
/*
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
|            20 |    13000.00 |
|            30 |    11000.00 |
|            80 |    14000.00 |
|            90 |    24000.00 |
|           100 |    12000.00 |
|           110 |    12000.00 |
+---------------+-------------+
6 rows in set (0.04 sec)
*/

HAVING和WHERE的比较

• WHERE可以直接用字段作为条件，不能用分组的聚合函数作为条件；

  HAVING必须要和GROUP配合使用，可以把分组计算的函数和分组字段作为条件

• WHERE是先筛选后连接

  HAVING是先连接后筛选

  这决定了WHERE比HAVING效率更高，因为连接表后，数据成倍数上升，再筛选效率低

开发中使用，分组相关用HAVING，普通条件用WHERE可以提高效率

8.4 SELECT执行顺序

SQL查询语句顺序

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...

SQL的执行顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

在这七个过程中，每一个过程都会产生一个虚拟表，然后将这个虚拟表传递到下一个步骤。

在FROM结果如果涉及到多表查询

• 直接查询多表会在FORM阶段获取到一个笛卡尔积表
• 然后通过ON来筛选获取筛选后的表
• 如果添加外部行(左连接、右连接)，这里会在上面表的基础上增加外部行。

8.5 练习

【题目】
#1.where子句可否使用分组组函数进行过滤?
不能

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

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

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

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

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

# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT d.department_name, d.location_id, COUNT(e.employee_id),AVG(e.salary)
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_id;

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