窗口函数可以理解为给数据划到一个窗口内并排上序号。
over()即那个窗口函数，需要搭配其他函数进行分析
over()括号内部可以加上partiton by进行分组，加上order by进行排序。不加上partition by是对所有数据开了一个窗，加上partition by是对分组内部所有数据进行开窗。加上order by对分组内数据按排序进行开窗。
形如over(partition by class order by score)，这个会对class字段进行分组，然后在分组的内部基于score进行排序。
此外还可以给窗口计算加行范围，over(rows between 开始位置 and 结束位置),开始位置和结束位置可以填的包括
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED PRECEDING：窗口的起点
UNBOUNDED FOLLOWING：窗口的终点
形如：over(partition by class order by score rows between UNBOUNDED PRECEDING and 1 PRECEDING)，这个会对class字段进行分组，然后在排序后从窗口第一个值取到当前行前一个值
常见用法介绍
先假设一个总分表score_table有姓名name、班级class、总分score三个字段
在窗口内使用排序函数
row_number()、rank()、dense_rank()
对各个班级内部进行总分排名
对排名连续无相同名次，比如有三个分数98、98、97，排名为1、2、3，使用row_number()
形如sql：
select *, row_number() over(partition by class order by score desc) as num from score_table;
对排名不连续有相同名次，比如有三个分数98、98、97，排名为1、1、3，使用rank()
形如sql：
select *, rank() over(partition by class order by score desc) as num from score_table;
对排名连续有相同名次，比如有三个分数98、98、97，排名为1、1、2使用dense_rank()
形如sql：
select *,dense_rank() over(partition by class order by score desc) as num from score_table;
在窗口内使用聚合函数
avg(列名)、sum(列名)、max(列名)、min(列名)
计算排挤内前各个名次的平均分
形如sql：
select *, avg(score) over(partition by class order by score desc) as num from score_table;
在窗口内其他函数的使用
ntile(n)函数
把窗口内按班级分组后的数据按先成绩排名后分成3份，分成1、2、3组
形如sql：
select *, ntile(3) over(partition by class order by score desc) as num from score_table;
lag(列名,往前的行,默认值)
把窗口内按班级分组后的数据，取该分组内每一行数据的前一名同学的成绩，如果第一名就显示100；
形如sql：
select *, lag(score,1,100) over(partition by class order by score desc) as num from score_table;
lead(列名,往后的行,默认值)
把窗口内按班级分组后的数据，取该分组内每一行数据的后一名同学的成绩，如果最后一名就显示0；
形如sql：
select *, lag(score,1,0) over(partition by class order by score desc) as num from score_table;
first_value(列名)和last_value(列名)
把窗口内按班级分组后的数据，取该分组内第一名同学的成绩
形如sql：select *, first_value(score) over(partition by class order by score desc) as num from score_table;
把窗口内按班级分组后的数据，取该分组内最后一名同学的成绩
形如sql：select *, last_value(score) over(partition by class order by score desc) as num from score_table;
总结：
窗口函数的使用大致遵循以下
函数名字(arg1, arg2, ... argN) OVER( [PARTITION BY 分组列] [ORDER BY 排序列] [计算的行范围] )
本文举的函数有row_number()、rank()、dense_rank()、avg(列名)、sum(列名)、max(列名)、min(列名)、ntile(n)、lag(列名,往前的行,默认值)、lead(列名,往后的行,默认值)、first_value(列名)和last_value(列名)
网友评论