背景
今天来讲讲MySQL8的新特性开窗函数,特别高大上的词哈,其实开窗函数就是对某块具有特性的记录集合执行的特殊统计函数。怎么听着这么熟悉,这不就是聚合函数么,没错,开窗函数的本质功能和聚合函数是一致的,只不过开窗函数更强大更具灵活性,具体语法如下。
开窗函数名([<字段名>]) over ([partition by <分组字段>] [order by <排序字段> [desc|asc]])
[]表示这块数据是可有可无的
<xxx>xxx是对这块数据的说明
- partition by :按照指定 分组字段 对数据进行分区,开窗函数在不同的分区中 分别 执行
- order by:按照指定字段排序,也是在不同分区内执行
看起来还是有点复杂的,但其实用起来还是很简单的,下面以员工表为案例带大家初探门径,员工表数据如下。
分部门统计员工的平均工资
不对啊,这个需求我好像使用聚合函数就可以实现啊
SELECT dname,avg(salary) FROM `employee` GROUP BY dname
没错用聚合函数也可以实现,相同功能用开窗函数的语法是这样的
SELECT dname,avg(salary) over (partition by dname) FROM `employee`
可以看到两者的结果之间有一个区别,就是开窗函数返回了很多条数据,而普通的聚合函数只返回了四行数据,欲知为何,先将开窗函数改成以下指令
SELECT *,avg(salary) over (partition by dname) FROM `employee`
执行结果如下所示
可以看到员工表中所有的数据都查询了出来,这就是两者之间的区别
- avg是对数据分组后将一组的数据聚合平均
- 开窗函数的avg是先对数据分组,然后对分组的每一条数据进行聚合平均,然后将结果放到每一条记录中
按入职时间降序分部门统计员工的工资累加值
SQL如下
select *,sum(salary) over (partition by dname order by hiredate desc) from employee
执行结果如下
如果把排序条件去掉就是分部门统计员工的工资总和
分部门统计以入职时间升序得到与前后两位同事的平均工资
乖乖,这需求都得想一想才绕的明白,下面直接看SQL
select *,avg(salary) over (partition by dname order by hiredate asc rows between 1 preceding and 1 following) from employee
rows between a preceding and b following
的意思就是当前行的前a行和当前行的后b行
SQL的执行结果如下
按工资降序分部门统计员工信息,显示行号
SQL如下
select *,row_number() over (partition by dname order by salary) from employee
按入职时间升序分部门统计员工与下一个入职的员工的薪资差
SQL如下
SELECT *,( currSalary - nextSalary ) diff
FROM
(
SELECT
*,
salary currSalary,
last_value( salary ) over ( PARTITION BY dname ORDER BY hiredate rows BETWEEN 0 preceding AND 1 following ) nextSalary
FROM
employee
) r;
执行结果如下