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