当前位置：首页 > article >正文

常用的聚合函数

article 2025/1/6 12:14:59

聚合函数类型

AVG() /SUM()

MAX() /MIN()

COUNT()

AVG和SUM函数

sum/avg 的计算都是将null值过滤调的

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

#AVG/SUM：只使用于数值类型的字段（或变量）
SELECT AVG(salary),SUM(salary),AVG(salary)*107
FROM employees;
#SUM(salary)与AVG(salary)*107相等

MIN和MAX函数

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

#字符串可以排序自然就可以找出最大与最小的字段

#MAX/MIN：使用于数值类型，字符串类型，日期时间类型的字段
SELECT MAX(salary),MIN(salary)
FROM employees;

SELECT MAX(last_name),MIN(last_name)
FROM employees;
#字符串可以排序自然就可以找出最大与最小的字段

COUNT函数

COUNT(*)返回表中记录总数，适用于任意数据类型。

#COUNT：

#①作用：计算指定字段在查询结构中出现的个数（不包含是null值的）

#②注意计算指定字段出现的个数时时，是不计算NULL值的。

#③AVG=SUM/COUNT------------------三种方式计算的COUNT都成立

#sum/avg 的计算都是将null值过滤调的，所以AVG=SUM/COUNT总是成立

SELECT COUNT(employee_id),COUNT(salary),COUNT(1)
FROM employees;#结果：107  107  107

SELECT COUNT(commission_pct)
FROM employees;#结果：35  不计算NULL值

#sum/avg 的计算都是将null值过滤调的，所以AVG=SUM/COUNT总是成立
SELECT AVG(salary),SUM(salary)/COUNT(salary),
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),
SUM(commission_pct)/107
FROM employees;
/*结果显示：AVG(salary)与SUM(salary)/COUNT(salary)相等
            AVG(commission_pct)与SUM(commission_pct)/COUNT(commission_pct)相等
            AVG(commission_pct)与SUM(commission_pct)/107不相等
*/            
            

#需求：查询公司中平均奖金率(要将没有奖金的人也带上)
SELECT SUM(commission_pct)/COUNT(1)#或者是COUNT(*)
FROM employees;

#如何计算表中有多少条记录，如何实现

#方式一：COUNT(*)

#方式二：COUNT(一个数字)

#方式三：COUNT(具体字段)：结果不一定正确！

使用COUNT(*)，COUNT(一个数字)，COUNT(具体字段)那个效率更高呢？

如果使用MyISAM存储引擎，则三者的效率一样，都是O(1)

如果使用的是InnoDB存储引擎，则是COUNT(*)=COUNT(一个数字)>COUNT(具体字段)

GROUP BY 的使用

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

#结论1：SELECT中出现的非组函数的字段必须声明在CROUP BY 中，

#反之，CROUP BY中声明的字段可以不出现在SELECT中。

#结论2：CROUP BY 声明在from后面，where后面，order by 前面，limit前面

#结论3：MySQL GROUP BY中使用WITH ROLLUP (最后将所有的数据看成一组)

# 与order by 要慎用(8.0+之后不会报错)

#GROUP  BY 的使用
#需求：查询各个部门的平均工资和最高工资
#使用单个列分组
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id;
#没有部门的人和在一起组成一个组


#使用多个列分组
#需求：查询各个department_id,job_id的平均工资
#方式一：
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
#先按department_id（写在前面的）分；在按job_id（写在后面的）细分
#方式二：
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;
#只有部门号和工种都一样才会被分到一组计算平均值

#错误的（只能写一个部门号，可是一个部门里面会有多个工种）
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id;
#结论1：SELECT中出现的非组函数的字段必须声明在CROUP BY 中，
#反之，CROUP BY中声明的字段可以不出现在SELECT中。
#结论2：CROUP BY 声明在from后面，where后面，order by 前面，limit前面
#结论3：MySQL GROUP BY中使用WITH ROLLUP (最后将所有的数据看成一组)
#        与order by 要慎用(8.0+之后不会报错)
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;

#需求：查询各个部门的平均工资，按照平均工资升序排列
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC;


SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;

HAVING的使用（用来过滤数据的）

#要求1：如果过滤条件中使用了聚合函数，必须使用HAVING来代替WHERE。否则报错。

#要求2：HAVING必须声明在GROUP BY的后面。

#要求3：开发中我们使用HAVING的前提是SQL中使用了GROUP BY。

代码：

#练习：查询各个部门中最高工资比10000高的部门信息
#错误的
#执行顺序：执行完from以后就执行Where，此时还没有分组 
#所有这时Where中的MAX(salary)与select中的MAX(salary)不是同一个
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary)
GROUP BY department_id;


#正确的
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000;

#没有GROUP BY就没有分组，那么将所有数据看成时一组（HAVING是对分组进行过滤的）
SELECT department_id,MAX(salary)
FROM employees
HAVING MAX(salary)>10000;#将所有数据看成一组过滤完以后就只有一个数据，没有比较的必要

#查询各个部门id为10，20，30，40这4个部门中最高工资比10000高的部门信息
#方式一：（先分组再在分组中进行过滤）
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 AND department_id IN (10,20,30,40);
#方式二：(先将不是指定部分的数据过滤调再分组)--------------执行效率高
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary)>10000;

#结论:当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中

# 当过滤条件中没有聚合函数时，则过滤条件声明在WHERE或HAVING中都可，建议使用WHERE

where与having的对比

1.从使用范围来讲，HAVING的适用范围更广。

2.如果过滤条件中没有聚合函数：这中情况下，WHERE的执行效率要高于HAVING

WHERE 先筛选数据再关联，执行效率高不能使用分组中的计算函数进行筛选

HAVING 可以使用分组中的计算函数在最后的结果集中进行筛选，执行效率较低

#SQL底层执行原理

SQL92语法：

SELECT....,...,...,....(存在聚合函数)

FROM ...,...,...

WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件