Shulou(Shulou.com)06/03 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.