应水友要求帮忙写的SQL

留存率:
某一个时段内,使用过该产品的用户,在未来时间段还使用的比率。

原来搞过一个hive的
http://blog.itpub.net/29254281/viewspace-2097338/

以我们的登录表为例
space_user_task_statistics_log 表如果loginflag = 1 说明用户登录过产品.其中createDate 表示登录日期。

查询计算'2017-11-06' 至'2017-11-12' 登录过的用户，在后来6周的留存信息。

其中nums是数字辅助表

create table nums(id int not null primary key);
delimiter $$
create procedure pCreateNums(cnt int)
begin
declare s int default 1;
truncate table nums;
while s<=cnt do
insert into nums select s;
set s=s+1;
end while;
end $$
delimiter ;
delimiter $$
create procedure pFastCreateNums(cnt int)
begin
declare s int default 1;
truncate table nums;
insert into nums select s;
while s*2<=cnt do
insert into nums select id+s from nums;
set s=s*2;
end while;
end $$
delimiter ;

初始化数字辅助表
call pFastCreateNums(100000);

查询如下：

		select d.startdate ,d.enddate,   
	
		count(   
	
		    distinct   
	
		    case when t2.createdate between d.startdate and d.enddate then t2.userid else null end   
	
		) as '周活'   
	
		from   
	
		(   
	
		    select distinct t1.userid,t1.createdate basedate,l.createdate from (   
	
		        SELECT   
	
		            log.userid, log.createDate   
	
		        FROM   
	
		            space_user_task_statistics_log log   
	
		        WHERE   
	
		            log.loginflag = 1   
	
		            AND log.createDate between '2017-11-06' and '2017-11-12'   
	
		    ) t1   
	
		    left join   
	
		    space_user_task_statistics_log l   
	
		    on(t1.userid=l.userid and l.createDate between '2017-11-06' and ('2017-11-06' + interval 6*7-1 day))   
	
		    -- order by t1.userid,t1.createdate,l.createdate   
	
		) t2   
	
		left join   
	
		(   
	
		    select   
	
		    '2017-11-06' + interval (id-1)*7 day startdate   
	
		    ,   
	
		    '2017-11-06' + interval (id)*7-1 day enddate   
	
		    from nums where id<=6   
	
		) d   
	
		on(t2.createdate between d.startdate and d.enddate)   
	
		group by d.startdate ,d.enddate

这个SQL我觉得已经接近最优了。

ITPub博客

留存率计算

最新文章