IT数码 购物 网址 头条 软件 日历 阅读 图书馆
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓ 		图片批量下载器
↓批量下载图片,美女图库↓ 		图片自动播放器
↓图片自动播放器↓ 		一键清除垃圾
↓轻轻一点,清除系统垃圾↓
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁
 
   -> 大数据 -> [MySQL]聚合函数与分组 -> 正文阅读

[大数据][MySQL]聚合函数与分组

在这里插入图片描述

文章目录

1. 聚合函数介绍

1.1 什么是聚合函数

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

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

在这里插入图片描述

1.2 常用的聚合函数

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

2. 常用的聚合函数

2.1 AVG()

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

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

在这里插入图片描述

2.2 SUM()

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

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

在这里插入图片描述

2.3 MAX()

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

在这里插入图片描述

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

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

在这里插入图片描述

2.4 MIN()

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

在这里插入图片描述

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

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

在这里插入图片描述

2.5 COUNT()

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

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

在这里插入图片描述

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

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

在这里插入图片描述

COUNT()统计常量

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

在这里插入图片描述

计算表中有多少条记录：

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

在这里插入图片描述

不建议通过统计某个字段的取值的个数来统计表格的行数，因为有些字段的某些行可能取值为空，而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;

在这里插入图片描述

# 计算公司的平均奖金率
# 错误计算方法
# 因为有没有奖金的员工，而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;

在这里插入图片描述
在这里插入图片描述

如何需要统计表中的记录数，使用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;

在这里插入图片描述

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

在这里插入图片描述

3.2 使用多个列分组

在这里插入图片描述

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

在这里插入图片描述

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

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

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

在这里插入图片描述

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;

在这里插入图片描述

注意：
当使用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;

在这里插入图片描述

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;

在这里插入图片描述

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

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

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

在这里插入图片描述

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;

在这里插入图片描述

# 查询部门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、当过滤条件中有聚合函数时，则此过滤条件必须声明在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 ..., ....

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

5.2 SELECT 的执行顺序

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

由于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.查询每个工种、每个部门的部门名、工种名和最低工资

6.2 解答

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

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

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

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

在这里插入图片描述

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

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

在这里插入图片描述

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

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

在这里插入图片描述

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

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

在这里插入图片描述

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;

在这里插入图片描述

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;

在这里插入图片描述

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;

在这里插入图片描述
  大数据 最新文章
实现Kafka至少消费一次
亚马逊云科技：还在苦于ETL？Zero ETL的时代
初探MapReduce
【SpringBoot框架篇】32.基于注解+redis实现
Elasticsearch：如何减少 Elasticsearch 集
Go redis操作
Redis面试题
专题五 Redis高并发场景
基于GBase8s和Calcite的多数据源查询
Redis——底层数据结构原理
上一篇文章      下一篇文章      查看所有文章
加:2022-10-08 20:48:45  更:2022-10-08 20:50:17 
 
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁

360图书馆 购物 三丰科技 阅读网 日历 万年历 2025年5日历 -2025/5/1 8:14:34-

图片自动播放器
↓图片自动播放器↓ 		TxT小说阅读器
↓语音阅读,小说下载,古典文学↓ 		一键清除垃圾
↓轻轻一点,清除系统垃圾↓ 		图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  IT数码