MySQL中聚合函数那些事（超级详细的总结）_mysql语句中使用聚合函数后之前的id有办法知道吗-CSDN博客

本文链接：https://blog.csdn.net/qiangqiang103/article/details/122838875

聚合函数

1、常见的几个聚合函数

AVG / SUM：只适用于数值类型的字段（或变量）
MAX / MIN：适用于数值类型、字符串类型、日期时间类型的字段（或变量）
COUNT
- 作用：计算指定字段在查询结果中出现的个数
- 注意：计算指定字段出现的个数时，是不计算NULL值的。
方差、标准差、中位数

# 1.1 AVG / SUM
	SELECT AVG(salary), SUM(salary), AVG(salary) * 107 FROM employees;
	
	# 1.2 MAX / MIN
	SELECT MAX(salary), MIN(salary) FROM employees;
	SELECT MAX(last_name), MIN(last_name), MAX(hire_date),MIN(hire_date) FROM employees;
	
	# 1.3 COUNT
	SELECT COUNT(employee_id),COUNT(salary) FROM employees;
	# 如何计算表中有多少条记录，如何实现
	# 方式1： count(*)
	# 方式2： count(1)
	# 方式3： count(具体字段)；不一定对！
	
	# 需求： 查询公司中平均的奖金率
	# 错误的！
	SELECT AVG(commission_pct) FROM employees;
	#正确的：
	SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct, 0)) FROM employees;

如果需要计算表中的记录数，使用count(*), count(1), count(具体字段) 哪个效率更高

如果采用的是MyISAM引擎，三者的效率相同。如果采用的是InnoDB引擎，三者效率：count(*) = count(1) > count(字段)，我么尽量使用count( *)

在MySQL InnoDB存储引擎中，count(*) 和count(1)都是对所有结果进行count。如果有where子句，则是对所有符合筛选条件的数据进行统计，如果没有where子句，则是对数据表的数据行数进行统计。

因此count(*)和count(1)本质上没有区别，执行的复杂度都是O（N），也就是采用全表扫描，进行循环+计数的方式进行统计

如果是MySQL MyISAM存储引擎，统计数据表的行数只需要O（1）的复杂度，这是因为没长MyISAM的数据表都有一个meta信息存储了row_count值，而一致性则由表级锁来保证。因为InnoDB支持事务，采用行级锁和MVCC机制，所以无法想MyISAM一样，只维护一个row_count变量，因此需要采用扫描全表，进行循环+计数的方式来完成统计。

需要注意的是，在实际执行中，count(*)和count(1)的执行时间可能略有差别，不过你还是可以把他俩的执行效率看成是相等的。

另外在InnoDB引擎中，如果采用Count(1)和Count(*)来统计数据行数，要尽量采用二级索引，因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于count( *)和count(1)来说，他们不需要查找具体的行，只需要统计行数，系统会自动采用占用空间更小的二级索引来进行统计。

然后如果想要查找具体的行，那么采用主键索引的效率更高，如果有多个二级索引，会使用key_len小的二级索引进行扫描，当没有二级索引的时候，采用采用主键索引来进行统计。

2、GROUP BY 的使用

# 需求：查询各个部门的平均工资，最高工资
	SELECT department_id, AVG(salary), SUM(salary) FROM employees GROUP BY department_id;
	
	# 需求：查询各个job_id的平均工资
	SELECT job_id, AVG(salary) FROM employees GROUP BY job_id;

	# 需求：查询各个department_id,job_id的平均工资
	SELECT department_id, job_id, AVG(salary) FROM employees GROUP BY department_id, job_id;
	
	# 结论：select中出现的非组函数的字段必须声明在group BY中。
	# 	反之，GROUP BY中声明的字段可以不出现在SELECT中
	#结论2：GROUP BY声明在FROM后面、WHERE后面，ORDER BY前面，LIMIT前面
	#结论3：MySQL中GROUP BY中使用WITH ROLLUP,当使用rollup时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和 ORDER BY是互相排斥的。
	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;

3、HAVING的使用(用来过滤数据)


	# 要求1：如果过滤条件中使用了聚合函数，必须使用having替换WHERE, 否则报错
	# 要求2：HAVING必须声明在GROUP BY的后面
	# 要求3：开发中，我们使用HAVING的前提是使用了GROUP BY
	# 练习：查询各个部门中最高工资比10000高的部门信息
	SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000;
	# 查询部门id为10,20,30,40这四个部门中最高工资比10000高的部门信息
	# 方式1：
	SELECT department_id, MAX(salary) FROM employees WHERE department_id IN(10,20,30,40) GROUP BY department_id HAVING MAX(salary) > 10000
	# 方式2：
	SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40)
	# 结论：当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中，当过滤条件中没有聚合函数时，则此过程中声明在WHERE中和HAVING中都可以，但是，建议大家声明在WHERE中
	
	/*
		WHERE与HAVING的对比：
			从使用范围上看，HAVING的适用范围更广
			如果过滤条件中没有聚合函数：这种情况下，WHERE的执行效率更高，WHERE是先筛选再连接，HAVING是先连接再筛选
	*/

4、SQL的底层执行原理

sql92语法：
SELECT ...,...(存在聚合函数)
FROM ...,...,...
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ....,...
HAVING包含聚合函数的过滤条件
ORDER BY ...,...(ASC,DESC)
LIMIT ..., ...

SQL语句的执行过程：

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