MySQL-开窗函数

背景

今天来讲讲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;

执行结果如下