8. 聚合函数

1. 聚合函数介绍

聚合函数又称组函数

什么是聚合函数

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

聚合函数类型

以下聚合函数都自动忽略字段中的NULL

  1. AVG()
  2. SUM()
  3. MAX()
  4. MIN()
  5. 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;

195934cc0489e15039d7a1e6b29b85b9.png

3. HAVING

基本使用

过滤分组:HAVING子句

  1. 如果过滤条件中使用了聚合函数,则必须使用HAVING来替换WHERE。否则,报错。

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

  3. 开发中,我们使用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 的对比

  1. 从适用范围上来讲,HAVING的适用范围更广。
  2. 如果过滤条件中没有聚合函数:这种情况下,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

eea29e3587dd9cb017cd7ac38e7fb4d7.png

8b59e69a301eeb292fdd95eeaac7ceab.png

posted @   LemonPuer  阅读(118)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示