SQL统计出每个用户的累积访问次数

SQL统计出每个用户的累积访问次数

如下是用户访问数据

要求使用SQL统计出每个用户的累积访问次数，如下图

建表和插入数据

#建表
create table user_data(
	user_id varchar(50),
	visit_date date,
	visit_count int
);
###插入数据
insert into user_data values ('u01','2017-01-21',5);
insert into user_data values ('u02','2017-01-23',6);
insert into user_data values ('u03','2017-01-22',8);
insert into user_data values ('u04','2017-01-20',3);
insert into user_data values ('u01','2017-01-23',6);
insert into user_data values ('u01','2017-02-21',8);
insert into user_data values ('u02','2017-01-23',6);
insert into user_data values ('u01','2017-02-22',4);

进行预处理

select 
	user_id,
	date_format(visit_date,'%Y-%m') as date,
	sum(visit_count) as count
from user_data group by user_id,date order by user_id,date;

得到预处理结果（对用户id和月份进行分组，然后求和，在进行排序）

普通方法进行累积求和

分析：先得到上面的预测表，然后再从预处理表中查询出用户id相同，月份小于等于所需月份的访问次数，进行求和，得到累积值。

select 
	user_id,
	date,
	count,
	(select 
		sum(count) 
	from (select 
			user_id,
			date_format(visit_date,'%Y-%m') as date,
			sum(visit_count) as count
		from user_data group by user_id,date order by user_id,date) t where 
		user_id=t1.user_id and date<=t1.date) as amount
from 
	(select 
		user_id,
		date_format(visit_date,'%Y-%m') as date,
		sum(visit_count) as count
	from user_data group by user_id,date order by user_id,date) as t1;

结果

使用窗口函数（Mysql 8以上支持）

select 
	user_id,
	date_format(visit_date,'%Y-%m') as date,
	sum(visit_count) as count
    sum(count) over (partition by user_id order by date) as amount 
from
    user_data group by user_id,date order by user_id,date;

该语句未测试。