Hive Sum MAX Over Demo(單月訪問次數(shù)和總訪問次數(shù))

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,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,1

求每個(gè)用戶單月的訪問次數(shù)和總訪問次數(shù)
create external table if not exists t_access(
uname string comment '用戶名',
umonth string comment '月份',
ucount int comment '訪問次數(shù)'
) comment '用戶訪問表'
row format delimited fields terminated by ","
location "/user/hive/t_access";

load data local inpath "/root/tmonthcount.txt" into table t_access;

select tba.*,tbb.allCount
from
(
select uname,umonth,sum(ucount) as tuconut
from t_access
group by uname,umonth) tba
join (select uname,sum(ucount) as allCount from t_access group by uname) tbb on tbb.uname=tba.uname
;

select uname,umonth,ucount,sum(ucount) over(partition by uname,umonth) as tuconut,sum(ucount) over(partition by uname) as allCount
from t_access;

A 2015-01 33 81
A 2015-02 10 81
A 2015-03 38 81
B 2015-01 30 79
B 2015-02 15 79
B 2015-03 34 79

每個(gè)用戶截止到每月為止的最大單月訪問次數(shù)和累計(jì)到該月的總訪問次數(shù)，結(jié)果數(shù)據(jù)格式如下

select tmp.*
,max(tmp.tuconut) over(partition by tmp.uname order by tmp.umonth rows between unbounded preceding and current row) as maxCount
,sum(tmp.tuconut) over(partition by tmp.uname order by tmp.umonth rows between unbounded preceding and current row) as allCount
from
(select uname,umonth,sum(ucount) as tuconut
from t_access
group by uname,umonth) tmp;

A 2015-01 33 33 33
A 2015-02 10 33 43
A 2015-03 38 38 81
B 2015-01 30 30 30
B 2015-02 15 30 45
B 2015-03 34 34 79