我们上一章讲到了 SQL 单行函数。实际上 SQL 函数还有一类，叫做聚合（或聚集、分组）函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

1. 聚合函数介绍

  • 什么是聚合函数

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

  • 聚合函数类型
    • AVG()
    • SUM()
    • MAX()
    • MIN()
    • COUNT()
  • 聚合函数语法
    image.png
  • 聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

    1.1 AVG和SUM函数

    AVG() 求某个数值字段的平均值
    SUM() 求某个数值字段的总和
    注意：以上两个函数都只能对数值字段使用
    1. # AVG和SUM
    2. SELECT
    3.   AVG(salary),
    4.   SUM(salary),
    5.   AVG(last_name),
    6.   SUM(last_name)
    7. FROM
    8.   employees;
    image.png

1.2 MAX和SUM函数

MAX() 求一组数据中的最大值
SUM() 对一组数据进行求和
注意：以上两个函数都可以对数值类型、字符串类型、日期时间类型的字段（或变量）进行处理

  1. # MIN和MAX
  2. # 适用于数值类型、字符串类型、日期时间类型的字段或变量
  3. SELECT
  4.     MIN(salary),
  5.     MAX(salary),
  6.     MIN(last_name),
  7.     MAX(last_name),
  8.     MAX(hire_date),
  9.     MIN(hire_date)
  10. FROM
  11.     employees;

image.png

1.3 COUNT函数

count() 计算指定字段在查询结构中出现的个数
注意：计算指定字段出现的个数的时候，是不计算NULL值的

  1. # count
  2. # 计算指定字段在查询结构中出现的次数
  3. SELECT
  4.     COUNT(salary),
  5.     COUNT(2 * salary),
  6.     COUNT(employee_id),
  7.     COUNT(department_id),
  8.     COUNT(1),
  9.     COUNT(2),
  10.     COUNT(*)
  11. FROM
  12.     employees;

image.png

统计表中有多少条数据：使用count(*)或者count(1)都可以实现，count(字段)的结果不一定对，因为统计的字段不包含为NULL的 SUM函数不会统计值为NULL的数据

  1. # 需求：查询公司的平均奖金率
  2. # 下面这个写法是错误的
  3. SELECT
  4.     AVG(commission_pct)
  5. FROM
  6.     employees;
  8. # 正确的写法
  9. SELECT
  10.     SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),
  11.     SUM(commission_pct) / COUNT(*),
  12.     AVG(IFNULL(commission_pct,0))
  13. FROM
  14.     employees;

image.png

问题：COUNT(*), COUNT(1), COUNT(2)用哪一个比较好 如果使用的是MyISAM存储引擎，那么三者效率相同，三种方式的时间复杂度都是O(1) 如果使用的是InnoDB存储引擎，那么三者效率：COUNT(*)=COUNT(1)>COUNT(字段)

2. GROUP BY

2.1 基本使用

需求：求出各个部门的平均工资

  1. -- GROUP BY的使用
  2. SELECT
  3.     e.department_id,
  4.     d.department_name,
  5.     AVG( e.salary ),
  6.     SUM( e.salary ) 
  7. FROM
  8.     employees e
  9.     LEFT JOIN departments d ON e.department_id = d.department_id 
  10. GROUP BY
  11.     e.department_id;

image.png

使用GROUP BY可以将表中的数据分成若干组，大概格式如下

  1. SELECT column, group_function(column)
  2. FROM table
  3. [WHERE    condition]
  4. [GROUP BY    group_by_expression]
  5. [ORDER BY    column];

注意：WHERE一定紧跟FROM的后面

2.2 使用多个列分组

需求：employee_id和job_id相同的员工分为一组，求其平均工资

  1. SELECT
  2.     e.department_id,
  3.     d.department_name,
  4.     e.job_id,
  5.     j.job_title,
  6.     AVG(e.salary)
  7. FROM
  8.     employees e
  9.     LEFT JOIN departments d ON e.department_id = d.department_id
  10.     LEFT JOIN jobs j ON e.job_id = j.job_id
  11. GROUP BY e.department_id,e.job_id;

image.png

注意：GROUP_BY后面的字段是不区分先后的，因为最后达到的分组状态都是一样的。所以上面的写法和下面给出的是一样的。

  1. SELECT
  2.     e.department_id,
  3.     d.department_name,
  4.     e.job_id,
  5.     j.job_title,
  6.     AVG(e.salary)
  7. FROM
  8.     employees e
  9.     LEFT JOIN departments d ON e.department_id = d.department_id
  10.     LEFT JOIN jobs j ON e.job_id = j.job_id
  11. GROUP BY e.job_id,e.department_id;
  12. # 分组的条件不分先后，因为最后的效果都是一样的

注意：在SELECT列表中所有未包含在组函数中的列都应出现在GROUP BY子句中

  1. -- SELECT中出现的非组函数的字段必须声明在group by中
  2. -- 反之，GROUP BY中声明的字段可以不出现在select中
  3. SELECT
  4.     e.department_id,
  5.     e.job_id,
  6.     AVG(e.salary)
  7. FROM
  8.     employees e
  9. GROUP BY
  10.     e.department_id;

image.png

2.3 在GROUP BY中使用WITH ROLLUP

使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

  1. -- 在GROUP BY中使用WITH ROLLUP
  2. SELECT
  3.     department_id,
  4.     AVG(salary)
  5. FROM
  6.     employees
  7. GROUP BY
  8.     department_id WITH ROLLUP;

image.png

注意：当使用来ROLLUP之后，不能同时使用ORDER BY子句来进行结果的排序。也就是说，这两者之间是互相排斥的。

image.png

3. HAVING的使用

image.png

作用：用来过滤数据

  1. 行已经被分组。
  2. 使用了聚合函数。
  3. 满足HAVING 子句中条件的分组将被显示。
  4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

3.1 练习： 查询各个部门中最高工资比10000高的部门信息

  1. SELECT
  2.     e.department_id,
  3.     d.department_name,
  4.     MAX(e.salary)
  5. FROM
  6.     employees e
  7.     LEFT JOIN departments d ON e.department_id = d.department_id
  8. GROUP BY
  9.     e.department_id,
  10.     d.department_name
  11. HAVING
  12.     MAX(e.salary) > 10000;

要求：

  1. 如果过滤条件中使用了聚合函数，那么就必须使用HAVING来代替WHERE。否则会报错
  1. SELECT
  2.     MAX(salary)
  3. FROM
  4.     employees
  5. WHERE
  6.     MAX(salary) > 10000;

image.png

  1. HAVING必须声明在GROUP BY的后面

image.png

  1. 开发中，使用HAVING的前提是SQL语句中使用了GROUP BY

3.2 练习：查询部门id为10，20，30，40这四个部门中最高工资比10000高的部门信息

  1. -- 练习：查询部门id为10，20，30，40这四个部门中最高工资比10000高的部门信息
  2. -- 方式一：
  3. SELECT
  4.     e.department_id,
  5.     d.department_name,
  6.     MAX(e.salary)
  7. FROM
  8.     employees e
  9.     LEFT JOIN departments d ON e.department_id = d.department_id
  10. WHERE
  11.     e.department_id IN (10,20,30,40)
  12. GROUP BY
  13.     e.department_id,
  14.     d.department_name
  15. HAVING
  16.     MAX(e.salary) > 10000;
  18. -- 或者
  19. -- 方式二：
  20. SELECT
  21.     e.department_id,
  22.     d.department_name,
  23.     MAX(e.salary)
  24. FROM
  25.     employees e
  26.     LEFT JOIN departments d ON e.department_id = d.department_id
  27. GROUP BY
  28.     e.department_id
  29. HAVING
  30.     e.department_id IN (10,20,30,40)
  31.     AND MAX(e.salary) > 10000;

开发中比较推荐使用第一种方式，第一种方式的执行效率会更高。

结论：

  1. 当过滤条件中有聚合函数的时候，那么过滤条件必须声明在HAVING中
  2. 当过滤条件中没有聚合函数的时候，那么此过滤条件声明在WHERE或HAVING中都是可以的，但是更推荐声明在WHERE中

WHERE和HAVING的对比

  1. 适用范围：HAVING的适用范围更广
  2. 如果过滤条件中不存在聚合函数，那么这种情况下，WHERE的执行效率更高

**=>** 有聚合函数使用**HAVING**，没有则使用**WHERE**

4. SQL底层执行原理

4.1 SELECT语句的完整结构

SQL92

  1. SELECT ...,...
  2. FROM ...,...
  3. WHERE 多表的连接条件
  4. AND 不包含组函数的连接条件
  5. GROUP BY ...,...
  6. HAVING 包含组函数的连接条件
  7. ORDER BY ... DESC / ASC
  8. LIMIT ...,...

SQL99

  1. SELECT ...,...
  2. FROM ...,...
  3. (LEFT/RIGHT) JOIN ... ON ...
  4. (LEFT/RIGHT) JOIN ... ON ...
  5. WHERE 不包含组函数的筛选条件
  6. AND 不包含组函数的筛选条件
  7. GROUP BY ...,...
  8. HAVING 包含组函数的筛选条件
  9. ORDER BY ... ASC / DESC
  10. LIMIT ...,...

其中，各个关键字的作用如下

  1. 其中：
  2. （1）from：从哪些表中筛选
  3. （2）on：关联多表查询时，去除笛卡尔积
  4. （3）where：从表中筛选的条件
  5. （4）group by：分组依据
  6. （5）having：在统计结果中再次筛选
  7. （6）order by：排序
  8. （7）limit：分页

4.2 SQL语句的执行过程

4.2.1 关键字的顺序

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

4.2.2 SELECT语句的执行过程

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

image.png
解释一：为什么在3.2的练习中更推荐使用方式1？

假设经过from操作后，查询出来的数据一共有10w条

  1. 如果使用方式一，经过WHERE中的筛选条件过后，会过滤掉很多的数据，剩下的数据再经过分组和HAVING的进一步筛选，所消耗的时间就很低。
  2. 如果使用方式二，那么就会直接在10w条数据的基础上直接进行分组，然后再进行筛选。这样的话效率一定是比方式一更低的。

解释二：为什么在ORDER BY中可以使用SELECT中的别名，而WHERE中不可以

根据上面给出的执行顺序可以知道，ORDER BY是在SELECT后面才执行的，而WHERE在SELECT的前面，所以是这样。

如果你写了一条SQL语句，那么其关键字顺序和执行顺序如下

  1. SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
  2. FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
  3. WHERE height > 1.80 # 顺序 2
  4. GROUP BY player.team_id # 顺序 3
  5. HAVING num > 2 # 顺序 4
  6. ORDER BY num DESC # 顺序 6
  7. LIMIT 2 # 顺序 7

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

4.3 SQL的执行原理

SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

  1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
  2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
  3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。

当我们拿到了查询数据表的原始数据，也就是最终的虚拟表 vt1，就可以在此基础上再进行 WHERE 阶段。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2。

然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4。

当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段。

首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2。

当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段，得到虚拟表 vt6。

最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段，得到最终的结果，对应的是虚拟表 vt7。

当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。