應水友要求幫忙寫的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我覺得已經接近最優了。

留存率計算

相關文章