函数
1.row_number
2. lag
- 使用方法:
lag(字段名称,偏移量,默认值)
- 作用:取指定字段当前行向前指定偏移量的值
3.lead
- 使用方法:
lead(字段名称,偏移量,默认值)
- 作用:取指定字段当前行向后指定偏移量的值
4. 聚合函数
5. 排序
rank
: 并列排序,且跳过,即1,1,3
dense_rank
:并列排序,不跳过,即1,1,2
6. 极值
7. 切片ntile
window子句
向前preceding
over(partition by id order by date rows between 1 preceding and current row -- 前一行和当前行
向后following
over(partition by id order by date rows between 1 preceding and 1 following -- 前一行、当前行和后一行
当前行 current row
over(partition by id order by date rows between unbounded preceding and current row -- 起点到当前行
起点:unbounded
over(partition by id order by date rows between unbounded preceding and unbounded following -- 起点到结束