sql-留存

--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