189 8069 5689
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 external table if not exists t_access(
uname string comment '用户名',
umonth string comment '月份',
ucount int comment '访问次数'
) 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
每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数，结果数据格式如下
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