扫码加入数据分析学习群

1.定义：开窗函数是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着 不同的窗口，这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算，只不过它更具灵活性， 它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。

2.语法：开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [< 滑动窗口>])

开窗函数的一个概念是当前行，当前行属于某个窗口，窗口由over关键字来指定函数执行的窗口范围， 如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，开窗函数基于所有行进行计 算；如果不为空，则有三个参数来设置窗口：

partition by子句：按照指定字段进行分区，两个分区由边界分隔，开窗函数在不同的分区内分别 执行，在跨越分区边界时重新初始化。

order by子句：按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和 partition by子句配合使用，也可以单独使用。

frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。

对于滑动窗口的范围指定，通常使用 between frame_start and frame_end 语法来表示行范围， frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：

current row 边界是当前行，一般和其他范围关键字一起使用 
unbounded preceding 边界是分区中的第一行 
unbounded following 边界是分区中的最后一行 
expr preceding 边界是当前行减去expr的值 
expr following 边界是当前行加上expr的值

比如，下面都是合法的范围：

rows between 1 preceding and 1 following 窗口范围是当前行、前一行、后一行一共三行记录。 
rows  unbounded preceding 窗口范围是分区中的第一行到当前行。 
rows between unbounded preceding and unbounded following 窗口范围是当前分区中所有行， 等同于不写。

开窗函数和普通聚合函数的区别：

（1）聚合函数是将多条记录聚合为一条；而开窗函数是每条记录都会执行，有几条记录执行完还是几 条。

（2）聚合函数也可以用于开窗函数中。

eg1.查询所有员工的平均工资

(1)聚合函数

select avg(sal) from emp;

#返回结果只有一行

（2）开窗函数

select *,avg(sal) over() 平均工资 from emp;

#当over中没有指定分区、排序和滑动窗口时，将整个表作为一个区域，默认计算的是所有值

eg2.查询各部门平均工资

(1)聚合函数

select deptno,avg(sal) 平均工资
from emp
group by deptno;

（2）开窗函数

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

#当over中指定了分区，但没有指定排序和滑动窗口，默认计算的是当前分区内的所有值

eg3.查询各部门累积工资

开窗函数

select *,sum(sal) over (partition by deptno order by hiredate) 累积工资 from emp;

#当over中指定了分区和排序，没有指定滑动窗口，默认计算的是分区内第一行到当前行的值

eg.4查询各部门按入职日期计算移动平均工资

开窗函数

select *,avg(sal) over (partition by deptno order by hiredate rows between 1 preceding and 1 following) 移动平均工资 from emp;

#当over中指定了分区、排序和滑动窗口，那么计算的就是当前分区内滑动窗口的值（当前行与前一行、后一行的移动平均）