看完本题,最后写出来的SQL比较长,但是都是由小的SQL拼接出来的
获取新用户登陆--MIN(date)
获取留存,即为新用户登陆前一天第二天登录的数量--ADDDATE(MIN(date),INTERVAL 1 DAY)
之前有求过留存率,使用之前的就好(区别为本体是每个时间)-- count(ADDDATE(MIN(date),INTERVAL 1 DAY)) group by date
/ count(min(date)) group by date求出来新用户的每一天的留存率--利用login表的distinct(date),IFNULL(留存率,0) left join 留存率表
结果即为所求,只不过SQL稍微youd
模板化
--新用户的每一天的留存率 SELECT DISTINCT(te.date),IFNULL(k.number,0) FROM login te LEFT JOIN ( --留存率 SELECT c.date,ROUND(d.number/c.number,3) AS number FROM ( -- 新用户登陆前一天第二天登录的数量 SELECT l.date,COUNT(1) AS number FROM login l , ( SELECT user_id,MIN(date) AS date FROM login GROUP BY user_id )a WHERE l.user_id = a.user_id AND l.date = ADDDATE(a.date,INTERVAL 1 DAY) GROUP BY l.date )d , ( -- 获取新用户登陆--MIN(date) SELECT b.date,COUNT(1) AS number FROM ( -- 获取新用户登陆diyitia SELECT user_id,MIN(date) AS date FROM login GROUP BY user_id )b GROUP BY b.date )c WHERE d.date = ADDDATE(c.date,INTERVAL 1 DAY) )k ON te.date = k.date