聚合函数笔记

广导原子
123 阅读4分钟

聚合函数

1.常见的几个聚合函数

1.1

  • AVG()

  • SUM()

查询salary字段的平均值和最大值：

SELECT AVG(salary),SUM(salary)AVG(salary) * 107; #一共有107条记录
from employees;

结果：

image.png

这两个函数只对==数值类型==的字段和变量适用

如下操作没有意义

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

结果：

image.png

1.2

  • MAX
  • MIN

查询salary字段的最大值和最小值：

 SELECT MAX(salary),MIN(salary)
 FROM employees;

结果：

image.png

对于max 和 min 函数其他类型字段的使用：

查询名字的最大最小值

 SELECT MAX(last_name),MIN(last_name)
 FROM employees;

结果：

image.png

可以查询的原因：只要字段可以排序，那么就可以使用max min 函数

所以max min 函数适用于数值类型，字符串类型，日期时间类型的字段或变量

1.3

  • COUNT 作用：计算指定字段在查询结构中出现的==次数==
SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary)
FROM employees;

结果：

image.png

其他字段：

SELECT COUNT(*),COUNT(1),COUNT(2)
FROM employees;

结果：

image.png

由于COUNT 会自动跳过null，所以统计总数不推荐使用具体字段，可以使用*，1, 2等

AVG 和 SUM 一样会跳过null

AVG = SUM / COUNT

需求：查询公司中平均的奖金率：

错误的:

SELECT AVG(commission_pct)
FROM employees;

由于AVG 会自动跳过null,所以没有奖金的员工不会作为分母进入计算

正确的：

SELECT SUM(commission_pct) / COUNT(1)
FROM employees;

2. GROUP BY 的使用

需求：查询各个部门的平均工资

SELECT AVG(salary)
FROM employees
GROUP BY department_id;

结果：

image.png

有点丑，我们加上department_id 字段

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

image.png

GROUP BY 会根据部门id进行分组，AVG 统计每个部门的平均薪资

需求：查询各个department_id,job_id的平均工资

SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;

结果：

image.png

department_id 和 job_id 的顺序是可以调换的

SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;

image.png

由于相同的job_id 和 department_id 会被分到同一组，所以这两种写法没有任何区别

GROUP BY 的错误使用方式：

SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY department_id;# 漏了job_id

image.png

select 语句中有的字段，GROUP BY 也必须要有，反之，GROUP BY有的语句，select不一定要有

使用顺序

GROUP BY 使用声明在FROM,WHERE 后面，在ORDER BY,LIMIT前面

WITH ROLLUP 和 GROUP BY的使用

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

image.png

对比不加 WITH ROLLUP

image.png

多了一条 其实是将二图所有AVG(salary) 加起来再取平均值

需求：查询各个部门的平均工资，并按照平均工资升序排序

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

结果：

image.png

如果加入 WITH ROLLUP 呢

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

image.png

结果报错了

这是因为 WITH ROLLUP 和 ORDER BY 是冲突的，所以二者不能同时出现

3. HAVING 的使用

​ 需求：查询各个部门中最高工资比10000高的部门信息

我们现查一下各个部门中最高工资

SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id;

结果：

image.png

然后过滤一下

SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary) > 10000 	
GROUP BY department_id;

结果：

image.png 寄了捏

where 不能和聚合函数一起用

这里我们用having

那就将where替换一下

SELECT department_id,MAX(salary)
FROM employees
HAVING MAX(salary) > 10000 	
GROUP BY department_id;

运行一下

image.png 又寄了捏

HAVING 必须在 GROUP BY 后面

SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

结果：

image.png

成啦

总结一下：对有聚合函数的查询语句进行过滤操作，需要使用having，而且having要在 GROUP BY 的后面

另外 having 在开发中必须和 GROUP BY 一起使用

需求：查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息

方式一：

 SELECT department_id,MAX(salary)
 FROM employees
 WHERE department_id IN (10,20,30,40) 
 GROUP BY department_id
 HAVING MAX(salary) > 10000;

结果：

image.png

方法二

 SELECT department_id,MAX(salary)
 FROM employees
 GROUP BY department_id
 HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);

结果：

image.png 结论

  1. 当过滤条件中有聚合函数的时候，此过滤函数必须声明在having当中
  2. 当过滤条件中没有聚合函数的时候，则此过滤条件声明在WHERE 和 HAVING 中都可以，但是更建议声明在where中，因为效率更高
  3. where 和 having 效率的对比：
    1. 从适用范围上来讲，having的适用范围更广
    2. 如果过滤条件中没有聚合函数：这种情况下，where的效率要高于having

4.SQL 语句的执行过程

#sql 99 语法
/*
select ........(存在聚合函数)
from ......(left / right)join...on 多表的连接条件
(left / right)join...on
where 不包含聚合函数的过滤条件
order by....(asc / desc)
limit ......
*/

执行过程：from ...,... -> on -> (left / right join) -> where -> group by -> having -> select -> distinct -> order by -> limit

avatar
文章
阅读
粉丝