窗口函数

作用

以当前行R为基准，关联出若干行mR（窗口），对mR进行聚合，计算出聚合值x，将x附加到R上。

窗口由关键字over指定

聚合操作支持所有内置和自定义的聚合函数，还支持几个特有的函数，如：row_number，rank，lag，lead

本质

替代关联和子查询的简写语法

窗口范围指定

全表数据

over()：窗口为全表数据

over( order by B)：全表根据B排序，窗口为全表数据自第0行起，到当前行的全部行

当前行所在分组

over( partition by A)：根据列A分组，窗口为当前行所在分组的全部行

over( partition by A order by B)：根据列A分组，然后在组内根据B排序，窗口为组内第0行至当前行的全部行

 当前行的前后若干行

over( rows between current row and X following)：从当前行起，到向后X行

聚合函数

常规的聚合函数，如max，avg，min等等

专用函数：row_number，rank，lag，lead

自定义统计函数

举例

1、现在有张学生成绩表，统计每个学生分数与平均分差距

select *,total_score  -  avg( total_score ) over() as from_avg  from stu_info

2、在上一个基础上，统计每个学生分数与省内平均分的差距

select *,total_score  -  avg( total_score ) over( partition by bir_place ) as from_avg  from stu_info

3、统计每个学生省内排名

select *, row_number() over( w_place order by total_score desc)  as range_place, total_score  -  avg( total_score ) over( w_place ) as from_avg  from stu_info
window w_place as ( partition by bir_place )

这个例子值得注意的地方是row_number函数的使用，在这种情况下可以用count(*)代替。上面的语句等效于：

select *, count(* )over( w_place order by total_score desc)  as range_place, total_score  -  avg( total_score ) over( w_place ) as from_avg  from stu_info
window w_place as ( partition by bir_place )

因为：

over( order by B)：全表根据B排序，窗口为全表数据自第0行起，到当前行的全部行

排序后的窗口大小实际上就是排名。

4、统计每个学生与下一名考生的差距

select *,row_number() over( order by total_score desc)  as sRange,  total_score - lead(total_score)over( order by total_score desc )   as from_next from stu_info

这里需要注意的是，在统计from_next时，由于是按照total_score降序排序的，窗口over( order by total_score desc)其实并不包含下一名考生的成绩，但是使用lead（leg同理）可以拓宽窗口。

5、现有销售表如下，统计累计销售额

select *,sum(total) over(order by good_name) as gAll from
	(select *,good_price*good_num as total from good_saled_info order by good_num) as tab_1
order by good_name

注意看，gall这一行数据的累加的