使用SQL统计出每个用户的累积访问次数
我们有如下的用户访问数据
|
userId |
visitDate |
visitCount |
|
u01 |
2017/1/21 |
5 |
|
u02 |
2017/1/23 |
6 |
|
u03 |
2017/1/22 |
8 |
|
u04 |
2017/1/20 |
3 |
|
u01 |
2017/1/23 |
6 |
|
u01 |
2017/2/21 |
8 |
|
U02 |
2017/1/23 |
6 |
|
U01 |
2017/2/22 |
4 |
要求使用SQL统计出每个用户的累积访问次数，如下表所示：
|
用户id |
月份 |
小计 |
累积 |
|
u01 |
2017-01 |
11 |
11 |
|
u01 |
2017-02 |
12 |
23 |
|
u02 |
2017-01 |
12 |
12 |
|
u03 |
2017-01 |
8 |
8 |
|
u04 |
2017-01 |
3 |
3 |
数据集
u01 2017/1/21 5
u02 2017/1/23 6
u03 2017/1/22 8
u04 2017/1/20 3
u01 2017/1/23 6
u01 2017/2/21 8
u02 2017/1/23 6
u01 2017/2/22 4
1）创建表
create table action
(userId string,
visitDate string,
visitCount int)
row format delimited fields terminated by "\t";
2）加载数据
hive (task)> load data local inpath '/opt/temp/action.txt' overwrite into table action;
执行sql
with t1 as(select userId,date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') as visitDate ,visitCount from action),
t2 as(select userId,visitDate,sum(visitCount) as visitCount_sum from t1 group by userId,visitDate),
t3 as(select userId,visitDate,visitCount_sum,sum(visitCount_sum) over(partition by userId order by visitDate asc rows between UNBOUNDED PRECEDING and current row) as heji from t2)
select * from t3 order by userId;