窗口函数有点类似于LSTM中的时间窗的概念
# 窗口函数形式
# 如果没有over,则窗口为全部数据
function(column1) # 此处function可以为avg、sum等
over(
partition by # 分组统计,要分列的组,有点类似group by
order by # 要排序的列
rows between # 数据窗口范围
)
# rows between使用方法
rows between 2 preceding and current row # 取当前行和前面两行
rows between unbounded preceding and current row # 包括本行和之前所有的行
rows between current row and unbounded following # 包括本行和之后所有的行
rows between 3 preceding and 1 following # 前三行和后一行,共五行
# 默认情况
# 当order by 后面缺少窗口从句条件时,默认为:
# 当前行所有之前所有的
rows between unbounded preceding and current row
# 当order by和窗口从句都缺失,默认为:
# 全部行
rows between unbounder preceding and unbounded following