语法:
<窗口函数> over ([partition by <分组coulums>]
order by <排序columns> [窗口子句])
窗口函数经常用来计算排序,也可以用在一些聚合函数上。
窗口函数-聚合函数
聚合窗口函数
SELECT a.*,
sum(grade) over (order by stu_id) as cum_sum,
avg(grade) over (order by stu_id) as cum_avg
FROM student_new a
cum_sum计算直到当前行的分数总和,cum_avg计算直到当前行的平均成绩
其他窗口函数:
-
lag()和lead()取前N行或后N行,
-
first_values和last_values(取第一行和最后一行)
其他窗口函数
select a.*,
lag(grade,1,-1) over (order by grade desc) as before_grade, --第二个参数指前1行,第三个参数为缺省情况
lead(grade,1,-1) over (order by grade desc) as next_grade,
first_values(grade) over (order by grade desc) as max_grade
last_values(grade) over (order by grade desc) as min_grade
from student_new a
窗口子句:上面的窗口函数都是作用在整个数据中或partition by的分区中,还可以通过窗口子句选择函数作用范围。
计算移动平均值
select a.*,
avg(grade) over (order by stu_id rows 2 preceding) as moving_avg
from student_new a
窗口子句中的rows 2 preceding是指数据范围为当前行的前两行到当前行,也可以用following关键字替换preceding,含义是当前行到后两行,还可以用rows between n preceding and n following 表示前n行到后n行。