窗口函数本身是可以按照partition进行分区然后进行聚合函数的
第1类
Row_Number,Rank,Dense_Rank
排序窗口函数,不过多介绍
第2类
SUM、AVG、MIN、MAX
作用之一是求第一行的数据不断往后累加得出
1行
1+2行
1+2+3行
1+2+3+4行
此类可以附带参数选择特定的多少行
- PRECEDING:往前
- FOLLOWING:往后
- CURRENT ROW:当前行
- UNBOUNDED:起点
- UNBOUNDED
- PRECEDING 表示从前面的起点
- UNBOUNDED FOLLOWING:表示到后面的终点
有排序和不排序完全不同
排序:分组内排序后数据不断累积,比如sum后 1、2、 3 、6 、12 为新列
没有 order 只有 partition,12、12 、12、12、12
第3类
NTILE:用于将分组数据按照顺序切分成n片,返回当前切片值,如果切片不均匀,默认增加第一个切片的分布。NTILE不支持ROWS BETWEEN
使用场景:
- 如一年中,统计出工资前1/5之的人员的名单,使用NTILE分析函数,把所有工资分为5份,为1的哪一份就是我们想要的结果.
- sale前20%或者50%的用户ID
第4类
LEAD,LAG,FIRST_VALUE,LAST_VALUE
lag与lead函数可以返回上下行的数据
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)
使用场景:通常用于统计某用户在某个网页上的停留时间
FIRST_VALUE:取分组内排序后,截止到当前行,第一个值
LAST_VALUE:取分组内排序后,截止到当前行,最后一个值
如果不指定ORDER BY,则默认按照记录在文件中的偏移量进行排序,会出现错误的结果
第5类
很少用到
CUME_DIST,PERCENT_RANK
CUME_DIST 小于等于当前值的行数/分组内总行数
比如,统计小于等于当前薪水的人数,所占总人数的比例
PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1