窗口函数详解

窗口函数可以进行排序、生成序列号等一般聚合函数无法实现的高级操作。其语法通常为
<窗口函数> over (partition by <列> order by <列> )

• partiton by 分组
• order by 排序

窗口函数大致分为两类
（1）能够作为窗口函数的聚合函数（sum、avg、count、max、min）
参考https://www.jb51.net/article/91232.htm，数据源同

earnmonth 打工月份
area 打工地区
sno 打工者编号
sname 打工者姓名
times 本月打工次数
singleincome 每次赚多少钱
personincome 当月总收入
1.1 sum 函数
eg：按地区、月份统计总收入

select  sno,earnmonth,area,sum(personincome) over(partition by area,earnmonth order by sno) from earnings 

1、需要在sum()的括号内指定作为聚合对象的列
2、计算出的数值是按照partition by子句指定的area分组，按照order by子句指定的sno升序排列，计算出该组内工号“小于自己”的打工者的总收入合计。
由于sum窗口函数的计算逻辑类似金字塔堆积，逐行增加计算对象，因而该函数常用于随时可能增加的销售总额计算（按照时间统计）
1.2 avg、max、min函数

select distinct earnmonth 月份, area 地区,  
    max(personincome) over(partition by earnmonth,area) 最高值,  
    min(personincome) over(partition by earnmonth,area) 最低值,  
    avg(personincome) over(partition by earnmonth,area) 平均值,  
    Sum(Personincome) Over(Partition By Earnmonth,Area) 总额  
from earnings;

1.3 指定窗口范围
eg：按地区、月份统计平均总收入

select  sno,earnmonth,area,round(avg(personincome) over(partition by area,earnmonth order by sno),2) avg_income from earnings 

由结果可以看出，与sum作为聚合函数相同，avg的统计对象也是该组内工号“小于自己”的打工者的总收入。
以“自身记录（当前记录）”作为基准进行统计，就是将聚合函数作为窗口函数使用的最大特征
由此，引入计算移动平均的方法：在窗口中指定更加详细的统计范围
eg：计算本行及前两行的平均值

select  sno,earnmonth,area,round(avg(personincome) over(order by sno rows 2 preceding),2) avg_income_3 from earnings 

关键字解释
rows 行
preceding 之前
following 之后
①计算对象指定为当前行及前5行 rows 5 preceding
②计算对象指定为当前行及后5行 rows 5 following
③计算对象指定为当前行及前后1行 rows between 1 preceding and 1 following
（2）专用窗口函数
2.1 rank 函数
计算排序，如果存在相同的位次，则会跳过之后的位次。比如有三条记录排在第一位时：1、1、1、4
2.2 dense_rank 函数
计算排序，如果存在相同的位次，则不会跳过之后的位次。比如有三条记录排在第一位时：1、1、1、2
2.3 row_number 函数
计算排序，位次连续且唯一。比如有三条记录排在第一位时：1、2、3、4
2.4 lag与lead函数
lag与lead函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率。

例如：lead(field, num, defaultvalue) field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值。

Select Earnmonth 本月,Sname 打工者,  
    lag(decode(personincome,0,'没赚','赚了'),1,'未知') over(partition by sname order by earnmonth) 上月,  
    Lead(Decode(personincome,0,'没赚','赚了'),1,'未知') Over(Partition By Sname Order By Earnmonth) 下月  
from earnings;