前言

依据对用户 ID、拜访日期、拜访次数的若干条数据，剖析得出每个用户的累计拜访次数，波及字符串替换、工夫函数、开窗、求和等技术。

1. 如果有用户拜访数据（用户 ID，拜访日期，拜访次数）

2. 心愿失去每个月用户的拜访累计次数

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;

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;

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;

The End