hive-分析、窗口函数的使用

5-1:sum,avg,min,max窗口内聚合分析

over (partition by col1 order by col2 rows between unbounded[n] preceding and current row[n following])

PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点，
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING：表示到后面的终点

select

id

,order_num

,sum(order_num) over (partition by id)  `分组内所有行`

,sum(order_num) over (partition by id order by dt) `从起点到当前行`

,sum(order_num) over (partition by id order by dt rows between unbounded preceding and current row) `默认为从起点到当前行`

,sum(order_num) over (partition by id order by dt rows between 3 preceding and current row) `当前行+往前3行`

,sum(order_num) over (partition by id order by dt rows between current row and 3 following) `当前行+往后3行`

,sum(order_num) over (partition by id order by dt rows b