hive 编写sql实现每个用户截止到每月为止的最大单月访问次数和累 计到该月的总访问次数

1.1 编写sql实现每个用户截止到每月为止的最大单月访问次数和累 计到该月的总访问次数

userid,month,visits
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

create table if not exists visits(
id string,
month string,
visits int
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile
;

load data local inpath '/root/hivedata/visits.txt' into table visits;

select
id,
month,
visits,
max(visits) over(distribute by id sort by month) maxvisits,
sum(visits) over(distribute by id sort by month) sumvisits
from
(select 
id,
month,
sum(visits) visits
from visits
group by id,month) tmp
;