留存率計算

壹頁書發表於2017-12-26
應水友要求幫忙寫的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是數字輔助表
  1. create table nums(id int not null primary key);

  3. delimiter $$
  4. create procedure pCreateNums(cnt int)
  5. begin
  6.     declare s int default 1;
  7.     truncate table nums;
  8.     while s<=cnt do
  9.         insert into nums select s;
  10.         set s=s+1;
  11.     end while;
  12. end $$
  13. delimiter ;

  15. delimiter $$
  16. create procedure pFastCreateNums(cnt int)
  17. begin
  18.     declare s int default 1;
  19.     truncate table nums;
  20.     insert into nums select s;
  21.     while s*2<=cnt do
  22.         insert into nums select id+s from nums;
  23.         set s=s*2;
  24.     end while;
  25. end $$
  26. delimiter ;

初始化數字輔助表
call pFastCreateNums(100000);

查詢如下：
  1. select d.startdate ,d.enddate,  
  2. count(  
  3.     distinct  
  4.     case when t2.createdate between d.startdate and d.enddate then t2.userid else null end  
  5. ) as '周活'  
  6. from  
  7. (  
  8.     select distinct t1.userid,t1.createdate basedate,l.createdate from (  
  9.         SELECT  
  10.             log.userid, log.createDate  
  11.         FROM  
  12.             space_user_task_statistics_log log  
  13.         WHERE  
  14.             log.loginflag = 1  
  15.             AND log.createDate between '2017-11-06' and '2017-11-12'  
  16.     ) t1  
  17.     left join  
  18.     space_user_task_statistics_log l  
  19.     on(t1.userid=l.userid and l.createDate between '2017-11-06' and ('2017-11-06' + interval 6*7-1 day))  
  20.     -- order by t1.userid,t1.createdate,l.createdate  
  21. ) t2  
  22. left join  
  23. (  
  24.     select  
  25.     '2017-11-06' + interval (id-1)*7 day startdate  
  26.     ,  
  27.     '2017-11-06' + interval (id)*7-1 day enddate  
  28.     from nums where id<=6  
  29. ) d  
  30. on(t2.createdate between d.startdate and d.enddate)  
  31. group by d.startdate ,d.enddate  

這個SQL我覺得已經接近最優了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-2149332/，如需轉載，請註明出處，否則將追究法律責任。

相關文章