Hive基本操作之用户访问次数统计

yaong

前言

根据对用户ID、访问日期、访问次数的若干条数据,分析得出每个用户的累计访问次数,涉及字符串替换、时间函数、开窗、求和等技术。

1. 假如有用户访问数据(用户ID,访问日期,访问次数)

userIdvisitDatevisitCount
u012017/1/215
u022017/1/236
u032017/1/228
u042017/1/203
u012017/1/236
u012017/2/218
u022017/1/236
u012017/2/224

2. 希望得到每个月用户的访问累计次数

用户id月份小计累积
u012017-011111
u012017-021223
u022017-011212
u032017-0188
u042017-0133

3. 启动hive,选库,建表

create table action
    (userId string,
    visitDate string,
    visitCount int) 
row format delimited fields terminated by "\t";

4. 将数据导入表

load data local inpath '/data/action.dat' into table action;

5. 转换日期字符串为标准格式

select
     userId,
     date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
     visitCount
from
     action;
useridmnvisitcount
u012017-015
u022017-016
u032017-018
u042017-013
u012017-016
u012017-028
u022017-016
u012017-024

6. 计算每人单月访问量

select
    userId,
    mn,
    sum(visitCount) mn_count
from
    (select
         userId,
         date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
         visitCount
    from
         action) as t1
group by 
    userId,mn;
useridmnmn_count
u012017-0111
u012017-0212
u022017-0112
u032017-018
u042017-013

7. 按月累计访问量

select
    userId,
    mn,
    mn_count,
    sum(mn_count) over(partition by userId order by mn)
from (select
        userId,
        mn,
        sum(visitCount) mn_count
    from
        (select
             userId,
             date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
             visitCount
        from
             action) as t1
    group by 
        userId,mn) as t2;
useridmnmn_countsum_window_0
u012017-011111
u012017-021223
u022017-011212
u032017-0188
u042017-0133

The End

阅读 425
1 声望
0 粉丝
0 条评论
你知道吗?

1 声望
0 粉丝
文章目录
宣传栏