8. 聚合函数
1. 聚合函数介绍
聚合函数又称组函数
什么是聚合函数
聚合函数作用于一组数据,并对一组数据返回一个值。
聚合函数类型
以下聚合函数都自动忽略字段中的NULL
- AVG()
- SUM()
- MAX()
- MIN()
- COUNT()
AVG和SUM函数
只适用于数值类型的字段(或变量)
SELECT AVG(salary),SUM(salary),AVG(salary) * 107
FROM employees;
MIN和MAX函数
适用于数值类型、字符串类型、日期时间类型的字段(或变量)
SELECT MAX(last_name),MIN(hire_date)
FROM employees;
字符串类型使用时结果取决于对应的编码集
COUNT函数
计算指定字段在查询结构中出现的个数(不包含NULL值的),适用于任意数据类型。
计算表中有多少条记录有三种方式
-
方式1:COUNT(*)
-
方式2:COUNT(1)
-
方式3:COUNT(具体字段) : 不一定对!
SELECT COUNT(*),COUNT(1),COUNT(commission_pct)-- 107,107,35
FROM employees;
公式:AVG = SUM / COUNT
-- 查询公司中平均奖金率
#错误的!
SELECT AVG(commission_pct)
FROM employees;
#正确的:
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),-- 写法一
AVG(IFNULL(commission_pct,0))-- 写法二
FROM employees;
使用COUNT(*)、COUNT(1)、COUNT(具体字段) 效率比较:
如果使用的是MyISAM 存储引擎,则三者效率相同,都是O(1);
如果使用的是InnoDB 存储引擎,则三者效率:COUNT(*) = COUNT(1)> COUNT(字段)
count(*) 是 SQL92 定义的标准统计行数的语法,推荐使用
2. GROUP BY
基本使用
GROUP BY子句可以将表中的数据分成若干组
在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中,反之,GROUP BY中声明的字段可以不出现在SELECT中
-- 每个部门每种岗位的人数
SELECT department_id,job_id,COUNT(*)
FROM employees
GROUP BY job_id,department_id;
-- 错误的
SELECT department_id,job_id,COUNT(*)
FROM employees
GROUP BY department_id;-- 每个部门不只一种岗位的员工
明确:WHERE一定放在FROM后面
SELECT department_id,job_id,SUM(salary)
FROM employees
WHERE department_id IN(50,60)
GROUP BY department_id,job_id;
使用多个列分组
上面已经展示了多个列分组的情况
GROUP BY中使用WITH ROLLUP
使用 WITH ROLLUP 关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量。
SELECT department_id,job_id,SUM(salary)
FROM employees
WHERE department_id IN(50,60)
GROUP BY department_id,job_id WITH ROLLUP;
3. HAVING
基本使用
过滤分组:HAVING子句
-
如果过滤条件中使用了聚合函数,则必须使用HAVING来替换WHERE。否则,报错。
-
HAVING 必须声明在 GROUP BY 的后面。
-
开发中,我们使用HAVING的前提是SQL中使用了GROUP BY。
-- 方式一,推荐,执行效率高
SELECT department_id,job_id,COUNT(*)
FROM employees
WHERE department_id IN(60,70,80)
GROUP BY job_id,department_id
HAVING COUNT(1)>5;
-- 方式二
SELECT department_id,job_id,COUNT(*)
FROM employees
GROUP BY job_id,department_id
HAVING COUNT(1)>5 AND department_id IN(60,70,80);
当过滤条件中没有聚合函数时,则此过滤条件声明在WHERE中或HAVING中都可以。但是,建议大家声明在WHERE中。
WHERE 与 HAVING 的对比
- 从适用范围上来讲,HAVING的适用范围更广。
- 如果过滤条件中没有聚合函数:这种情况下,WHERE的执行效率要高于HAVING
4. SELECT的执行过程
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 ...,....
SQL语句的执行过程:
组函数操作的是查询结果,WHERE语句中不能使用聚合函数
FROM ...,...-> ON -> (LEFT/RIGNT JOIN) -> WHERE ->
GROUP BY -> HAVING -> SELECT -> DISTINCT ->
ORDER BY -> LIMIT
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库