前言

依据对用户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,     visitCountfrom     action;

6. 计算每人单月访问量

select    userId,    mn,    sum(visitCount) mn_countfrom    (select         userId,         date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,         visitCount    from         action) as t1group 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