我们有如下的用户访问数据
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
–建表
drop table if exists test_one;
create table test_one(
userId string comment '用户id',
visitDate string comment '访问日期',
visitCount bigint comment '访问次数'
) comment '第一题'
row format delimited fields terminated by '\t';
–插入数据
insert into table test_one values('u01','2017/1/21',5);
insert into table test_one values('u02','2017/1/23',6);
insert into table test_one values('u03','2017/1/22',8);
insert into table test_one values('u04','2017/1/20',3);
insert into table test_one values('u01','2017/1/23',6);
insert into table test_one values('u01','2017/2/21',8);
insert into table test_one values('u02','2017/1/23',6);
insert into table test_one values('u01','2017/2/22',4);
最终sql:
-- group by分组 在用开窗函数 来分上下界限
select
userId `用户id`,
visitDate `月份`,
sum_mn `小计`,
sum(sum_mn) over(partition by userId rows between UNBOUNDED PRECEDING and current row) `累计`
from
(
select
t1.userId,
t1.visitDate,
sum(t1.visitCount) sum_mn
from
(
select
userId,
--date_format(to_date(from_unixtime(UNIX_TIMESTAMP(visitDate,'yyyy/MM/dd'))),'yyyy-MM') visitDate,
date_format(regexp_replace(visitdate,"/","-"),'yyyy-MM') visitDate,
visitCount
from test_one
) t1
group by userId,visitDate
) t2;