mysql 查询次日留存 三日留存 七日留存

--sumdate--返回结果的注册日期

--REGDATE--子表注册日期

--LOGINDATE--次日登陆日期

--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 login_daily_tab a, 
                    (select DATE(t.REGDATE) sumdate,t.guid new_user_guid from 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