計算本年使用者最大未登入時間段

壹頁書發表於2017-12-14
 計算本年使用者最大未登入時間段

space_user_task_statistics_log 如果使用者登入過,則將資訊記入該表
主要資訊有使用者ID和登入時間


space_user 記錄使用者基本資訊. 主要包括使用者ID和註冊時間資訊.

需求：
查所有使用者在2017年最大的未登入時間段

如果使用者在2017年才註冊,那麼有效時間從註冊時間開始算。

其實這是一個MySQL計算連續範圍的問題。

http://blog.itpub.net/29254281/viewspace-1385638/

SQL
  1. select userid,startdate,enddate,diff from (  
  2.     select t3.*,case when userid=@gid then @rn:=@rn+1 when @gid:=userid then @rn:=1 end rank  
  3.     from (  
  4.         select userid,dt-interval rn day c,min(dt) startdate,max(dt) enddate,    
  5.         datediff(max(dt),min(dt)) diff  
  6.         from (  
  7.             select   
  8.             cal.dt,cal.userid,case when cal.userid=@gid then @rn:=@rn+1 when @gid:=cal.userid then @rn:=1 end rn  
  9.             from   
  10.             (  
  11.                 select dt,userid from (  
  12.                     select '2017-01-01'+interval id-1 day dt from nums,(select @gid:=0,@rn:=0) vars where id<=dayofyear(now())  
  13.                 ) var,  
  14.                 (  
  15.                     select u.userid,u.registertime from space_user u   
  16.                     where exists(select * from space_user_task_statistics_log l where l.createDate>='2017-01-01' and l.userid=u.userid)  
  17.                 ) userlist  
  18.                 where dt>=date(userlist.registertime)  
  19.             ) cal   
  20.             left join   
  21.             (  
  22.                 select log.userid,log.createDate from   
  23.                 space_user_task_statistics_log log     
  24.                 where log.loginflag=1 and  log.createDate>='2017-01-01'   
  25.             ) t1 on(t1.userid=cal.userid and cal.dt=t1.createDate)  
  26.             where createdate is null  
  27.             order by userid,dt  
  28.         ) t2   
  29.         group by userid,dt-interval rn day  
  30.     ) t3   
  31.     order by userid,diff  desc  
  32. ) t4 where rank=1  
  33. order by diff desc;  

生產查詢一次耗時10分鐘左右.


diff 是最大未登入時間段的天數.

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

相關文章