有如下用户访问数据
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 |
要求使用HSQL统计出每个用户的累积访问次数,如下表所示
解答思路:
- – 建表
create table text1(
userid string ,
visitDate string ,
visitCount int
)
row format delimited fields terminated by ‘\t’;
**
- – 插入数据
**
insert into table text1 values
(‘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);
select * from text1;
- –实现结果
–思路:
–1. 先将日期格式用正则表达式修改为目标格式,需要用函数date_format,regexp_replace;
–2. 然后求小计值,用聚合函数sum;
–3. 最后求累计值,用窗口函数sum.
select
t2.userid,t2.visitDate,t2.visit_cnt,
sum(t2.visit_cnt) over (partition by userid order by visitDate rows between unbounded preceding and current row ) as total_visit_cnt
from (
select
userid,visitDate,
sum(visitCount) as visit_cnt
from (select userid,visitCount,date_format(regexp_replace(visitDate,‘/’,‘-’),‘yyyy-MM’) as visitDate from text1) t1
group by userid, visitDate) t2
order by t2.userid,t2.visitDate;