5、聚合函数、group by（分组）、having -mysql

今天药忘吃喽~ 2024-03-31 08:51 71阅读 0赞

#### 聚合函数 ####

*  聚合函数
 *   *  一、聚合函数介绍
     *   *   *  1、AVG 和 SUM 函数
             *  2、MIN 和 MAX 函数
             *  3、COUNT 函数
     *  二、 GROUP BY
     *   *   *  1、基本使用
             *  2、使用多个列分组
             *  3、GROUP BY 中使用 WITH ROLLUP（了解）
     *  三、HAVING
     *   *   *  1、基本使用
             *  2、where 和 having 的对比
     *  四、SELECT 的执行过程
     *   *   *  1、查询的结构
             *  2、SELECT执行顺序
             *  3、SQL 的执行原理
     *  五、课后练习题

## 聚合函数 ##

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

### 一、聚合函数介绍 ###

*  **什么是聚合函数**
    
    **聚合函数**作用于一组数据，并对一组数据返回一个值。
    
    ![在这里插入图片描述][acbe00b1cca14ed4ad8f7bbd09d3fdbf.png]
    
        SELECT TRUNCATE(MAX(salary),0) AS "max_salary" FROM employees;
 *  **常见聚合函数类型**
    
     *  **AVG()**—— 求平均值
     *  **SUM()**—— 求和
     *  **MAX()**—— 求最大值
     *  **MIN()** —— 求最小值
     *  **COUNT()** —求数量
 *  **聚合函数语法**  
    ![在这里插入图片描述][d017fc82d5b54d90a575ff6c33199d06.png]
 *  **聚合函数不能嵌套调用**。比如不能出现类似“`AVG(SUM(字段名称))`”形式的调用。

--------------------

##### 1、AVG 和 SUM 函数 #####

可以对 **数值型数据** 使用`AVG` 和 `SUM` 函数。

mysql> SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';
    +-------------+-------------+-------------+-------------+
    | AVG(salary) | MAX(salary) | MIN(salary) | SUM(salary) |
    +-------------+-------------+-------------+-------------+
    | 8272.727273 |    11500.00 |     6000.00 |   273000.00 |
    +-------------+-------------+-------------+-------------+

--------------------

##### 2、MIN 和 MAX 函数 #####

可以对 **任意数据类型** 的数据使用 `MIN` 和 `MAX` 函数。

mysql> SELECT MIN(hire_date), MAX(hire_date) FROM employees;
    +----------------+----------------+
    | MIN(hire_date) | MAX(hire_date) |
    +----------------+----------------+
    | 1987-06-17     | 2000-04-21     |
    +----------------+----------------+

--------------------

##### 3、COUNT 函数 #####

*  `COUNT(*)`返回表中记录总数，适用于**任意数据类型**。
    
        mysql> SELECT COUNT(*) FROM employees WHERE department_id = 50;
        +----------+
        | COUNT(*) |
        +----------+
        |       45 |
        +----------+
 *  `COUNT(expr)` 返回 **expr不为空** 的记录总数。
    
        mysql> SELECT COUNT(commission_pct) FROM employees WHERE  department_id = 50;
        +-----------------------+
        | COUNT(commission_pct) |
        +-----------------------+
        |                     0 |
        +-----------------------+
 *  **问题：用count(\*)，count(1)，count(列名)谁好呢?**
    
     *  对于`MyISAM`引擎的表是三者 **没有区别** 的。这种引擎内部有一计数器在维护着行数。
     *  对于`Innodb`引擎的表用`count(*)`,`count(1)`直接读行数，复杂度是`O(n)`，因为`innodb`真的要去数一遍。但好于具体的 `count(列名)`。
 *  **问题：能不能使用count(列名)替换count(\*)?**
    
    不要使用 `count(列名)`来替代 `count(*)`，`count(*)`是 `SQL92` 定义的标准统计行数的语法，跟数据库无关，跟 `NULL` 和`非 NULL` 无关。
    
    说明：`count(*)`会统计值为 `NULL` 的行，而 `count(列名)`不会统计此列为 `NULL` 值的行。

--------------------

### 二、 GROUP BY ###

![在这里插入图片描述][6cc2b7fc5e8e409fb4b64ba891d92407.png]

##### 1、基本使用 #####

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

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

> 明确：`group by` 声明在 `from` 后面、`where` 后面，`order by` 前面、`limit` 前面

*  1.1、在`SELECT`列表中所有未包含在组函数中的列都应该包含在 `GROUP BY`子句中
    
        mysql> 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 |
        +---------------+--------------+
 *  1.2、包含在 `GROUP BY` 子句中的列不必包含在`SELECT` 列表中
    
        mysql> SELECT AVG(salary) FROM employees GROUP BY department_id ;
        +--------------+
        | AVG(salary)  |
        +--------------+
        |  7000.000000 |
        |  4400.000000 |
        |  9500.000000 |
        |  4150.000000 |
        |  6500.000000 |
        |  3475.555556 |
        |  5760.000000 |
        | 10000.000000 |
        |  8955.882353 |
        | 19333.333333 |
        |  8600.000000 |
        | 10150.000000 |
        +--------------+
 *  **注意点：**  
    上面的1.1、1.2也是说的同一个问题，先看一个`错误`例子，如下：
    
        SELECT department_id,job_id,AVG(salary) 
        FROM employees
        GROUP BY department_id;
    
    为什是错误的例子呢？当根据 `department_id` 进行排序，但是要查询出 `department_id` 和 `job_id`  
    `department_id` 排序可以列出来，但是 `job_id` 可能有多个，这个时候它应该列出来哪个呢？即使列出来也是错的
    
     *  **得出的结论**
        
         *  `select` 中出现的非组函数的字段必须声明在 `group by` 中
         *  反之，`group by` 中声明的字段可以不出现在 `select` 中。

--------------------

##### 2、使用多个列分组 #####

![在这里插入图片描述][26c83142020449c7a75446b65540cd12.png]

> 上述多列分组说明：  
> 先根据 department\_id 分组，这个组里面有些员工的 job\_id 不同，我们在按照 job\_id 进行分组  
> group by 后面的多个列的顺序不分先后，即：  
> `group by department_id,job_id == group by job_id,department_id`

# 查询各个部门 department_id,job_id 的总工资
    mysql> SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;
    +---------+------------+-------------+
    | dept_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 |
    +---------+------------+-------------+

--------------------

##### 3、GROUP BY 中使用 WITH ROLLUP（了解） #####

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

mysql> SELECT department_id,AVG(salary) FROM employees WHERE department_id > 80 GROUP BY department_id WITH ROLLUP;
    +---------------+--------------+
    | department_id | AVG(salary)  |
    +---------------+--------------+
    |            90 | 19333.333333 |
    |           100 |  8600.000000 |
    |           110 | 10150.000000 |
    |          NULL | 11809.090909 |
    +---------------+--------------+

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

--------------------

### 三、HAVING ###

##### 1、基本使用 #####

![在这里插入图片描述][886471a419624ea9a49c8ff718bf15d5.png]

*  **过滤分组：HAVING子句**
    
     *  1、**行已经被分组**。（`第一次筛选后的二次筛选`）
     *  2、使用了聚合函数。
     *  3、满足`HAVING` 子句中条件的分组将被显示。
     *  4、`HAVING` 不能单独使用，必须要跟 `GROUP BY` 一起使用。
    
    ![在这里插入图片描述][6565b3f7e0ff488fb94e5bf07471462f.png]
    
        # 部门最高工资比 10000 高的部门
        mysql> 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 |
        +---------------+-------------+
 *  **非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数**。如下：
    
        SELECT   department_id, AVG(salary)
        FROM     employees
        WHERE    AVG(salary) > 8000
        GROUP BY department_id;
    
    ![在这里插入图片描述][738d28fcd2f44ef88c27dcf2ebe13678.png]

--------------------

##### 2、where 和 having 的对比 #####

*  **区别1**：
    
     *  `WHERE` 可以直接使用表中的字段作为筛选条件，但 **不能使用分组中的计算函数** 作为筛选条件；
     *  `HAVING` 必须要与 `GROUP BY` 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
        
        这决定了，在需要对数据进行分组统计的时候，`HAVING` 可以完成 `WHERE` 不能完成的任务。这是因为，在查询语法结构中，`WHERE` 在 `GROUP BY` 之前，所以无法对分组结果进行筛选。`HAVING` 在 `GROUP BY` 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 `WHERE` 无法完成的。另外，`WHERE`排除的记录不再包括在分组中。
 *  **区别2： 如果需要通过连接从关联表中获取需要的数据**
    
     *  `WHERE` 是先筛选后连接，
     *  `HAVING` 是先连接后筛选。
        
        这一点，就决定了在关联查询中，`WHERE` 比 `HAVING` 更高效。因为 `WHERE` 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。`HAVING` 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

**小结如下：**

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>优点</th> 
   <th>缺点</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>WHERE</strong></td> 
   <td>先筛选数据再关联，执行<code>效率高</code></td> 
   <td>不能使用分组中的计算函数进行筛选</td> 
  </tr> 
  <tr> 
   <td><strong>HAVING</strong></td> 
   <td>可以使用分组中的计算函数</td> 
   <td>在最后的结果集中进行筛选，执行<code>效率较低</code></td> 
  </tr> 
 </tbody> 
</table>

**开发中的选择：**

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

--------------------

### 四、SELECT 的执行过程 ###

##### 1、查询的结构 #####

#方式1：
    SELECT ...,....,...
    FROM ...,...,....
    WHERE 多表的连接条件
    AND 不包含组函数的过滤条件
    GROUP BY ...,...
    HAVING 包含组函数的过滤条件
    ORDER BY ... ASC/DESC
    LIMIT ...,...
    
    #方式2：
    SELECT ...,....,...
    FROM ... JOIN ... 
    ON 多表的连接条件
    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：分页

--------------------

##### 2、SELECT执行顺序 #####

你需要记住 SELECT 查询时的两个顺序：

*  **1、关键字的顺序是不能颠倒的**：
    
        SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
 *  **2、SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：**
    
        FROM - on -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
    
    ![在这里插入图片描述][65ddb7947e4f4ab0a78cc704e7c131e3.png]

比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：

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

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

##### 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` 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

--------------------

### 五、课后练习题 ###

# 1、where 子句中可否使用组函数进行过滤？no
    
    # 2、查询公司员工工资的最大值，最小值，平均值，总和
    SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM employees;
    
    # 3、查询各 job_id 的员工工资的最大值，最小值，平均值，总和
    SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
    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)) AS "difference"
    FROM employees;
    
    # 6、查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
    SELECT manager_id, MIN(salary)
    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(employee_id),AVG(salary) AS "avg_salary"
    FROM departments d LEFT JOIN employees e ON d.`department_id` = e.`department_id`
    GROUP BY department_name,location_id
    ORDER BY avg_salary DESC;
     
    # 8、查询每个工种、每个部门的部门名、工种和最低工资
    SELECT d.department_name,e.job_id,MIN(salary)
    FROM departments d LEFT JOIN employees e ON d.department_id = e.`department_id`
    GROUP BY e.job_id,d.department_name;

[acbe00b1cca14ed4ad8f7bbd09d3fdbf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/f299199e0e2a458a8681d4ebe48a7e30.png
[d017fc82d5b54d90a575ff6c33199d06.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/33363b8e987f42018a53a8dd03a3e2ea.png
[6cc2b7fc5e8e409fb4b64ba891d92407.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/da0edd3014a54bd8ad85654bad719f4e.png
[26c83142020449c7a75446b65540cd12.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/fb26ccf2fec543a8bf4ffc07f6f4e944.png
[886471a419624ea9a49c8ff718bf15d5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/367c74fcfb0d4b9fbbc1b5165ba85028.png
[6565b3f7e0ff488fb94e5bf07471462f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/c5c260fb98e949adb00375f534a89c20.png
[738d28fcd2f44ef88c27dcf2ebe13678.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/d2ab89f9d31d4aef92c133a9cea45c28.png
[65ddb7947e4f4ab0a78cc704e7c131e3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/11629f5e205f455aba1e4d6f8b51356b.png