大数据第46天—Mysql练习题12道之一--杨大伟

我们有如下的用户访问数据

 

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 bigint comment '用户id',
    visitDate bigint 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);

--查询
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;