Hive Sum MAX Over Demo (number of visits per month and total visits)
ABEI 2015-01 Jol 5
AJI 2015-01 Jol 15
BBJ 2015-01 Jol 5
ABEI 2015-01 Jol 8
BJR 2015-01 BJI 25
ABEI 2015-01 Jol 5
ABEI 2015-02 Jol 4
ABEI 2015-02 Jol 6
BBJ 2015-02 Jol 10
BBJ 2015-02 Jol 5
ABEI 2015-03 Regent 16
ABEI 2015-03 Jr.22
BBJ 2015-03 Regent 23
BBJ 2015-03 Jol 10
BBJ 2015-03 Jol 1
Calculate the number of visits and the total number of visits per user per month
Create external table if not exists t_access (
Uname string comment 'user name'
Umonth string comment 'month'
Ucount int comment 'visits'
) comment 'user access Table'
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
The maximum number of visits per user in a single month up to each month and the total number of visits accumulated to that month. The resulting data format is as follows
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
