常用的聚合函数

1、什么是聚合函数？
聚合函数作用于一组函数，并对一组数据返回一个值。
2、常用的聚合函数类型
-AVG（）-SUM（）-MAX() -MIN（） -COUNT（）

#聚合函数
#常见的几个聚合函数
#AVG /  SUM  只适用于数值类型的字段（变量）
SELECT AVG(salary),SUM(salary),AVG(salary)*107
FROM employees;
#如下的操作没有意义
SELECT SUM(last_name),AVG(last_name),SUM(hire_date)
FROM employees;
#MAX / MIN 适用于数值类型、字符串类型、日期类型的字段（变量）
SELECT MAX(salary),MIN(salary)
FROM employees;

SELECT MAX(last_name),MAX(hire_date)
FROM employees;
#COUNT
#1、作用：计算指定字段在查询结果中出现的个数（不包含null值）
SELECT COUNT(employee_id),COUNT(salary),COUNT(2*salary),COUNT(1),COUNT(2)
FROM employees;

SELECT *
FROM employees;
#如果计算表中有多少条记录，如何实现
#count(*)
#count(1)
#count(具体字段)：不一定对！
#2、注意：计算指定字段出现的个数时，是不计算NULL值的
SELECT COUNT(commission_pct)
FROM employees;

SELECT commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
#3、公式：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; 
#需求：查询公司中平均奖金率
SELECT AVG(commission_pct)#错误de
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(字段)

#方差、标准差、中位数

#GROUP BY 的使用
#需求：查询各个部门的平均工资，最高工资
SELECT department_id,AVG(salary),MAX(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;
#结论1：SELECT中出现的非组函数必须声明在GROUP BY中。
#       反之，GROUP BY中声明的字段可以不出现在select中
#结论2：GROUP BY 声明在from后面，WHERE后面，ORDER BY前面，LIMIT前面
#结论3：MySQL中 GROUP BY使用 WITH ROLLUP
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;
#说明当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的
#HAVING的使用（用来过滤数据）
#练习：查询各个部门中最高工资比10000高的部门信息
#要求1：如果过滤函数中使用了聚合函数，则必须使用HAVING来替换where 。否则  报错
#要求2：HAVING必须声明在GROUP By的后面
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) >10000;
#开发中，我们使用的HAVING的前提是SQL中使用了GROUP BY

##练习：查询部门id为10,20,30,40这四个部门中最高工资比10000高的部门信息
#方式一 推荐   执行效率高
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) >10000;
#方式二
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的对比
1、适用范围上，HAVING更广
2、如果过滤条件中有聚合函数中，WHERE的效率更高
*/
#SQL底层的执行原理
#1、SELECT 语句的完整结构
/*
SELECT .....,.....,....(存在聚合函数)
FROM ....,....
SQL92语法：
SELECT ....,....,...(存在聚合函数）
FROM ....,....,....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...,...
HAVING  包含聚合函数的过滤条件
ORDER BY ...,...(ASC/ DESC)
SQL99语法:
SELECT .....,.....,....(存在聚合函数)
FROM ....,....
SQL92语法：
SELECT ....,....,...(存在聚合函数）
FROM ....JOIN(LEFT/RIGHT)....ON 多表的连接条件
(LEFT/RIGHT)JOIN... ON...
WHERE  不包含聚合函数的过滤条件
GROUP BY ...,...
HAVING  包含聚合函数的过滤条件
ORDER BY ...,...(ASC/ DESC)
LIMIT ...,...
*/
#2、SQL语句的执行过程
#FROM ...,...-> ON ->(LEFT / RIGHT  JOIN) -> WHERE -> GROUP BY ->	HAVING -> SELECT -> DISTINCT
# ->ORDER BY -> LIMIT