统计每个日期新用户的次日留存率

太强了太强了

大神666,我之前没想到还可以这么写！用表格做一下减1的新表就很容易理解了。分子有两个条件，条件1：当前日期是最小登录日期(新用户)；条件2：当前日期+1天也有登录记录(次留用户)。用户id(2)在12号和13都有登录记录，select user_id,date_add(date,interval -1 day) from login这个表达式查出来的新表里会有一条4|2|2|2020-10-12,用user_id和date能匹配上原表里的第一条记录1|2|1|2020-10-12,说明12号这个日期的后一天这个用户也登录了(条件2)，加一个判断当前日期是最小登录日期(新用户)，就能判断这个是次留用户了。 我之前的思路是先找出有新用户的(id+日期)表，用user_id左连接原表，再group by nwe.date,新用户数(分母)就是count(new.user_id),次留新用户数是count(login.user_id),再union没有新用户的日期的值。

次留 第二天还存在的昨天的新用户/昨天的新用户总数 因此 分母 select user_id,min(date)from login group by user_id) 若与之匹配,那就是当前日期新用户 分母:必须是查到这条记录第二天还在登录(select user_id,date_add(date,interval -1 day) from login),而且当前日期还是第一次登录(select user_id,min(date)from login group by user_id),才可以算出来 你的问题在于这句话 select user_id,date_add(date,interval -1 day) from login没看懂 他的意思是这样的 举个例子,2号第一次登录,若把日子提前一天interval -1 day,那么明天就是今天,如果有user_id,date(date实际为3号,表示为2号)与之匹配,那么就可以说明是第二天登陆了,并且,2号得是首次登录日期,在这个表里面select user_id,min(date)from login group by user_id,如此你可以听明白吗

请问是不是先独立进行这个查询(select user_id,date_add(date,interval -1 day) from login group by user_id) 那不是会包含很多10-11的日期吗 怎么匹配正确的呢，想不明白：(

case when (user_id,date)in (select user_id,date_add(date,interval -1 day) from login) and (user_id,date)in (select user_id,min(date)from login group by user_id) 此处的(user_id,date)应该是属于第一次登陆的ID对吧,但是如果(user_id,date)in (select user_id,date_add(date,interval -1 day) from login 那岂不是说明它不是第一次登陆吗?有点搞不明白.

case前面为什么要加sum啊

牛啊牛啊 真牛逼啊 大神

我这个也通过了~其实原答主的用的-1day换成（user_id, date_add(date, interval 1 day) in (select...from)就好理解了。其实就是in之前的和in之后的差一天就行。 代码如下： SELECT date, IFNULL(ROUND(( SUM(CASE WHEN (user_id, DATE_ADD(date, INTERVAL 1 DAY)) IN (SELECT user_id, date FROM login) AND (user_id, date) IN (SELECT user_id, MIN(date) FROM login GROUP BY user_id) THEN 1 ELSE 0 END))/ (SUM(CASE WHEN (user_id, date) IN (SELECT user_id, MIN(date) FROM login GROUP BY user_id) THEN 1 ELSE 0 END)), 3),0) AS p FROM login GROUP BY date ORDER BY date;

请教下，分子可以写成 sum(case when (user_id,date)in(select user_id,date_add(min(date),interval 1 day)from login group by user_id) then 1 else 0 end) 吗？我怎么觉得逻辑是对的，但是怎么也通不过

没一个能举例解释清楚为什么-1的而不是+1

很厉害的答案！ 上面问到的为什么是-1不是+1： select user_id,date_add(date,interval -1 day) from login 表示用户登录时间的前一天， case when (user_id,date)in (select user_id,date_add(date,interval -1 day) from login) 则表示存在一个登录时间的前一天是当前日期，也就是说当前日期的第二天用户也登录了。比如user_id 为 1 的用户2021-01-02和2021-01-03都登录了，那么select user_id,date_add(date,interval -1 day)会返回 2021-01-01和2021-01-02， 因为2021-01-02在里面，且2021-01-02是用户的min_date，所以用户留存了

为什么date是最早的登陆日期，同时她又在前一天登陆过，这是问什么，我没太懂这的逻辑，请大神赐教

select date ,ifnull(round((sum(case when (user_id,date_add(date,interval 1 day))in (select user_id,date from login) and (user_id,date)in (select user_id,min(date)from login group by user_id) then 1 else 0 end))/ (sum(case when (user_id,date)in (select user_id,min(date)from login group by user_id) then 1 else 0 end)),3),0)as p from login group by date order by date; 这种更容易理解。

大佬，太牛了！！！

我觉得没有考虑每天重复登陆的情况

a请教各位大神，分子中出现的(user_id,date)in (select user_id,min(date)from login group by user_id)，这里为何不是date_add(date,interval -1 day)in (select user_id,min(date)from login group by user_id)呢？用当前日期的前一天作为首次登录日期。。。绕不出来了，盼回复o(╥﹏╥)o

select l.date, ifnull(cast(lead(cnt1) over(order by date)as float)/l.cnt2,0.000) from (select date, sum(case when (login.user_id, login.date) in (select user_id, date(min(date), '+1 day') from login group by user_id) then 1 else 0 end)as cnt1,sum(case when (login.user_id, login.date) in (select user_id, min(date) from login group by user_id) then 1 else 0 end) as cnt2 from login group by date order by date) l

先献上膝盖，大神的句子真的太美了，看的时候在 ① (user_id, date) in (select ② user_id, date_add(date,interval -1 day) from login) 里纠结了许久才看懂，现在评论一下做个记录：首先需要明确①的date和②中的date不是对应的，而是①date对应②date - 1；其次，因为②句中的user_id和date都是从login里搜出来的，所以表示②句中的(user_id, date) in login成立，对应到①中就是（user_id, date + 1）in login，因此！ (user_id, date) in (select user_id, date_add(date,interval -1 day) from login)的存在才保证了①的user_id在第二天也登陆了。 这个思路真的是神级，膜拜！

select user_id,date_add(date,interval -1 day) from login 表示用户登录时间的前一天， case when (user_id,date)in (select user_id,date_add(date,interval -1 day) from login) 则表示存在一个登录时间的前一天是当前日期，也就是说当前日期的第二天用户也登录了。

SELECT DISTINCT a.date, IFNULL(ROUND(new_cust.new_count/new_stay.new_stay_count,3),0) as p from login a left JOIN( select date,count(1) as new_stay_count from ( select date,user_id from ( SELECT a.user_id,a.date,b.user_id as bid,b.date as bdate from login a left JOIN login b on b.user_id=a.user_id and a.date>b.date )a where a.bid is NULL )a GROUP BY date )new_stay on a.date=new_stay.date left join ( SELECT new_cust.date,count(new_cust.user_id) as new_count from ( select date,user_id from ( SELECT a.user_id,a.date,b.user_id as bid,b.date as bdate from login a left JOIN login b on b.user_id=a.user_id and a.date>b.date )a where a.bid is NULL )new_cust INNER JOIN login b on DATE_ADD(new_cust.date ,INTERVAL 1 DAY)=b.date and new_cust.user_id=b.user_id GROUP BY new_cust.date )new_cust on new_cust.date=new_stay.date ORDER BY date