hive sql练习

字段说明： 
** 用户名，月份，访问次数 
数据内容如下

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

现要求出：每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数，

uname	umonth	acount	max_acess	sum_acess
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

create external table if not exists t_access(
uname string comment '用户名',
umonth string comment '月份',
ucount int comment '访问次数'
) comment '用户访问表' 
row format delimited fields terminated by "," 
location "/hive/t_access"; 

select 
uname,
umonth,
sum(ucount) acount
from t_access 
group by uname,umonth
order by uname,umonth      ;t1

select
uname,
umonth,
acount,
max(acount)over(partition by uname order by umonth)  max_acess,
sum(acount)over(partition by uname order by umonth)  sum_acess
from t1   ;t2

select
uname,
umonth,
acount,
max(acount)over(partition by uname order by umonth)  max_acess,
sum(acount)over(partition by uname order by umonth)  sum_acess
from(
select 
uname,
umonth,
sum(ucount) acount
from t_access 
group by uname,umonth
order by uname,umonth
) t1