【sql】之函数：聚合函数 | sql底层执行原理

聚合函数

-- 聚合函数
# 1.常见的聚合函数

SELECT AVG(salary),SUM(salary)
FROM employees;

SELECT MAX(salary),MIN(salary),MAX(last_name),MIN(last_name)
FROM employees;  #适用于数值类型、字符串、时间和日期类型的字段

#count：计算指定字段在查询结果中出现的个数
SELECT COUNT(employee_id),COUNT(salary),COUNT(1)
FROM employees;
#计算表中有多少条记录？
#1.count(1) 2.count(*) 3.count(字段)：不计算空值

#公式：AVG=SUM/COUNT
SELECT AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),SUM(commission_pct)/107
FROM employees;
#例：查询公司中平均奖金率
SELECT AVG(commission_pct)
FROM employees;  #wrong!
SELECT SUM(commission_pct)/COUNT(IFNULL(commission_pct,0)),SUM(commission_pct)/COUNT(1)
FROM employees;  #right!

# 2.group by
#查询各部门的平均工资，最高工资
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id; #按部门id分组
#查询各个部门，工种的平均工资
SELECT department_id,job_id,AVG