题解 | #牛客每个人最近的登录日期(五)#

我的方法：

select login.date,round(if(count(t1.user_id)=0,0,count(t2.user_id)/count(t1.user_id)),3)
from login 
left join (select user_id,min(date)md from login group by user_id) t1 
on t1.user_id=login.user_id and t1.md=login.date 
left join login t2 
on t2.user_id=t1.user_id and t2.date=date_add(t1.md,interval 1 day)
group by login.date
order by login.date

• 其中判断分母为0可以用ifnull(round(count(distinct t2.user_id)/(count(t1.user_id)),3),0)。
• 对于没有新用户登录的日期，除了用原表左连接，还可以用当前日期不存在的union

其他方法：没有用表连接+count，用的in+sum，我感觉表连接效率会更高？

select date
    ,ifnull(round((sum(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)
        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;