开窗函数

什么是开窗函数? 开窗函数是在MySQL8.0以后才新加的功能，因此，要想直接使用开窗函数，则mysql版本要8.0以上。其实开窗函数是在满足某种条件的记录集合上执行的特殊函数。开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。接下来，我们介绍开窗函数的相关语法；具体语法如下：

开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [< 细分窗口>])

rows between xxx and xxx 可出现的关键词： unbounded ：无限的 preceding ：在…之前 following：在…之后 current row：当前行

-- over()中的关键字：
-- 行数在前2行和后4行
over（order by salary rows between 2 preceding and 4 following）
-- 下面三条语句等效（都是查所有数据）：
-- 行数前无限到后无限
over（order by salary rows between unbounded preceding and unbounded following）
over（order by salary range between unbounded preceding and unbounded following）
-- 等效
over(partition by null)

row_number() over(partition by … order by …)
rank() over(partition by … order by …)
dense_rank() over(partition by … order by …)
count() over(partition by … order by …)
max() over(partition by … order by …)
min() over(partition by … order by …)
sum() over(partition by … order by …)
avg() over(partition by … order by …)
first_value() over(partition by … order by …)
last_value() over(partition by … order by …)
lag() over(partition by … order by …)
lead() over(partition by … order by …)

引入 oracle 数据库的 Scott 测试表数据

数据说明

1.emp员工表

2.dept部门表

3.salgrade工资等级表

查询所有员工的平均工资

select avg(sal) from emp;

使用开窗函数实现

select *,avg(sal) over () from emp;

从结果上看我们的窗口函数显示的是每一行，而普通的聚合函数则是只有一个结果

查询部门的平均工资

select deptno, avg(sal) avg_sal from emp group by deptno;

接下来使用开窗函数查询部门的平均工资

select *,avg(sal) over(partition by deptno) avg_sal from emp;

既然开窗函数都可以通过我们的一般聚合函数实现，那么为什么还要有开窗函数呢？其实，我们在前下面的一个案例，就只能用开窗函数实现了，其它的函数不可以实现的；

查询员工工资，并显示第一行到最后一行的工资汇总

select emp.*, sum(sal)over(order by sal rows between unbounded preceding and unbounded following )from emp;

查询员工工资，并显示第一行到当前行的工资汇总

select emp.*, sum(sal)over(order by sal rows between unbounded preceding and current row )from emp;

查询员工工资，并显示从当前行到最后一行的汇总

select emp.*, sum(sal)over(order by sal rows between current row and unbounded following)from emp;

查询员工工资，并显示从上一行到当前行的汇总

select e.*,sum(sal) over(order by sal rows between 1 preceding and current row ) from emp e;

查询员工工资，并显示上一行和下一行的汇总

select emp.*,sum(SAL) over ( order by SAL rows between 1 preceding and 1 following ) from emp;

查询员工工资，按照部门分组并显示每个组的工资总和

select emp.*,sum(sal) over (partition by emp.deptno order by sal rows between unbounded preceding and unbounded following )from emp;

查询员工工资，按照部门分组并显示每个组从第一行到当前行的工资总和

select emp.*,sum(sal)over(partition by deptno order by sal rows between unbounded preceding and current row )from emp;

普通的聚合函数用group by分组，每个分组返回一个统计值。 分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

first_value()与last_value()

取首尾记录值

select emp.*,last_value(sal) over (order by sal) from emp;
select emp.*,first_value(sal) over (order by sal) from emp;
select emp.*,last_value(sal) over (order by sal rows between unbounded preceding and unbounded following ) from emp;

rank(),dense_rank()与row_number()：求排序 以下三个分析函数用于计算一个行在一组有序行中的排位 序号从1开始 ROW_NUMBER 返回连续的排序，不论值是否相等 DENSE_RANK 具有相等值的行排序相同，序号是连续的 RANK 具有相等值的行排序相同，序数随后跳跃

select emp.*,row_number() over (order by sal desc) from emp;
select emp.*,rank() over (order by sal) from emp;
select emp.*,dense_rank() over (order by sal) from emp;

lag() 允许您回顾多行并从当前行访问行的数据

lead()从同一结果集中的当前行访问后续行的数据。

select emp.*,lag(SAL,1) over (order by sal) from emp;
select emp.*,lead(SAL,1) over (order by sal) from emp;