mysql 游戏留存_mysql 查询次日留存 三日留存 七日留存

--sumdate--注册日期

--diff1--次日留存人数

--diff3--三日留存人数

--diff7--七日留存人数

select sumdate,SUM(diff1) diff1,SUM(diff3) diff3,SUM(diff7) diff7

from (

SELECT b.sumdate sumdate,a.guid,DATE(a.LOGINDATE),DATEDIFF(DATE(a.LOGINDATE),b.sumdate) diff,

if(DATEDIFF(DATE(a.LOGINDATE),b.sumdate)=1,1,0) as diff1,

if(DATEDIFF(DATE(a.LOGINDATE),b.sumdate)=3,1,0) as diff3,

if(DATEDIFF(DATE(a.LOGINDATE),b.sumdate)=7,1,0) as diff7

from 115_login_daily_tab a,

(select DATE(t.REGDATE) sumdate,t.guid new_user_guid from 115_regedit_tab t

where DATE(t.REGDATE) between '2016-03-01' and '2016-03-30'

GROUP BY DATE(t.REGDATE),t.guid ORDER BY DATE(t.REGDATE))b

where a.guid =b.new_user_guid and a.LOGINDATE

GROUP BY b.sumdate, a.guid,DATE(a.LOGINDATE) ORDER BY a.guid,DATE(a.LOGINDATE)

)logdiffs

GROUP BY sumdate