访问量的统计(hSQL实现)

有如下用户访问数据

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;