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

創(chuàng)新互聯(lián)專注于遼寧企業(yè)網(wǎng)站建設(shè),成都響應(yīng)式網(wǎng)站建設(shè)公司,成都做商城網(wǎng)站。遼寧網(wǎng)站建設(shè)公司,為遼寧等地區(qū)提供建站服務(wù)。全流程專業(yè)公司，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務(wù)

求每個(gè)用戶單月的訪問(wèn)次數(shù)和總訪問(wèn)次數(shù)
create external table if not exists t_access(
uname string comment '用戶名',
umonth string comment '月份',
ucount int comment '訪問(wèn)次數(shù)'
) comment '用戶訪問(wèn)表'
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è)用戶截止到每月為止的最大單月訪問(wèn)次數(shù)和累計(jì)到該月的總訪問(wèn)次數(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