您现在的位置是:首页 > 正文

[MySQL]聚合函数与分组

2024-01-30 21:10:43阅读 0

在这里插入图片描述



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;

在这里插入图片描述

网站文章

  • 二分图相关

    二分图相关一、染色法判定二分图二分图要求边的两端点处于不同的集合,那么将两端点分别染成不同的颜色,如果没有冲突,则说明是二分图。首先是dfs函数,深搜进行染色:bool dfs(int u,int c...

    2024-01-30 21:10:36
  • 在ML中缺乏数据可是个大问题,亲测有效的5种方法帮您解决

    在ML中缺乏数据可是个大问题,亲测有效的5种方法帮您解决

    https://www.toutiao.com/a6701193162699833859/ 在我做过的很多项目中,公司虽然有非常棒的AI商业创意,但当他们意识到自己没有足够的数据时,却会慢慢的变得沮丧起来。然而,确实有解决的方案。本文的目的是简要地向你介绍其中的一些在我的实践中已经证明有效的方法,而不是列出所有现有的解决方案。 数据稀缺问题非常重要,因为数据是任何人工智能项目的...

    2024-01-30 21:10:30
  • JavaWeb学习笔记——jQuery动画、事件

    jQuery-3动画方法练习:品牌显示事件文档加载事件绑定事件移除事件冒泡事件对象练习:图片跟随 动画 方法 基本: show([speed,[easing],[fn]]) 显示 hide([spee...

    2024-01-30 21:10:01
  • 结构体变量

    在C语言中,可以使用结构体(Struct)来存放一组不同类型的数据。结构体的定义形式为: struct 结构体名{ 结构体所包含的变量或数组 }; 结构体是一种集合,它里面包含了多个变量或数组,它们的...

    2024-01-30 21:09:56
  • Java面试注意

    3125

    2024-01-30 21:09:48
  • python 依赖下载

    https://www.lfd.uci.edu/~gohlke/pythonlibs/#scipy

    2024-01-30 21:09:20
  • 轻松学懂图(下)——Dijkstra和Bellman-Ford算法

    轻松学懂图(下)——Dijkstra和Bellman-Ford算法

    概述 在上一篇文章中讲述了Kruskal和Prim算法,用于得到最小生成树,今天将会介绍两种得到最短路径的算法——Dijlkstra和Bellman-Ford算法 Dijkstra算法 算法的特点: ...

    2024-01-30 21:09:14
  • 顺序队列的基本操作实现

    顺序队列的基本操作

    2024-01-30 21:09:08
  • 电脑桌面计算机打开不显示硬盘信息,电脑加硬盘后不显示不出来怎么办

    1.给电脑添加了一个新硬盘,可是显示不出来怎么办1、在计算机上安装硬盘后,打开计算机,在计算机桌面上,选择我的计算机并右键单击“管理”进入计算机管理界面。2、选择“磁盘管理”,系统将弹出来检测新硬盘并...

    2024-01-30 21:09:00
  • 欢迎使用CSDN-markdown编辑器

    项目Properties -> Project Facets -> Runtime -> New -> Add a tomcat server ,JRE 选择 JRE1.8.0_XX.

    2024-01-30 21:08:31