每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数
原始数据
三个字段的意思:
用户名,月份,访问次数
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
目标数据
用户名,月份,访问次数 最大访问次数 总访问次数
A 1 20 20 20
A 2 10 30 20
...
B 4 50 82 82
解决方法:
方法一:
select name,
month,
pv,
sum(pv) over (partition by name order by month asc rows between unbounded preceding and current row) as spv,
max(pv) over (partition by name order by month asc rows between unbounded preceding and current row) as mpv from exercise_pv_temp;
第一个要点:sum max count min avg partition by name order by month asc rows between unbounded preceding and current row partition by name:严格来说是分区,事实上,你完全可以理解成是分组
order by month asc: 每组数据按照month升序排序
rows between A and B: 到底哪些记录作为一组来计算,添加一个窗口的边界
A:unbounded preceding (从起始数据开始)
3 preceding (当