***hive练手01***

userId visitDate visitCount
u01 2017/1/21 5
u02 2017/1/23 6
u03 2017/1/22 8
u04 2017/1/20 3
u01 2017/1/23 6
u01 2017/2/21 8
u02 2017/1/23 6
u01 2017/2/22 4

用户id 月份 小计 累积
u01 2017-01 11 11
u01 2017-02 12 23
u02 2017-01 12 12
u03 2017-01 8 8
u04 2017-01 3 3

select
userid,
date_format(regexp_replace(visitdate,’/’,’-’),‘yyyy-MM’) mn,
visitcount
from action;t1

userid mn visitcount
u01 2017-01 5
u02 2017-01 6
u03 2017-01 8
u04 2017-01 3
u01 2017-01 6
u01 2017-02 8
u02 2017-01 6
u01 2017-02 4

select
userid,
mn,
sum(visitcount) sum_visit
from (select
userid,
date_format(regexp_replace(visitdate,’/’,’-’),‘yyyy-MM’) mn,
visitcount
from action)t1
group by
userid,mn;t2

userid mn sum_visit
u01 2017-01 11
u01 2017-02 12
u02 2017-01 12
u03 2017-01 8
u04 2017-01 3

select
userid,
mn,
sum_visit,
sum(sum_visit) over(partition by userid order by mn)
from(select
userid,
mn,
sum(visitcount) sum_visit
from (select
userid,
date_format(regexp_replace(visitdate,’/’,’-’),‘yyyy-MM’) mn,
visitcount
from action)t1
group by
userid,mn)t2;

userid mn sum_visit sum_window_0
u01 2017-01 11 11
u01 2017-02 12 23
u02 2017-01 12 12
u03 2017-01 8 8
u04 2017-01 3 3