题解 | 计算每日次留率

MySQL窗口函数解法

select date,
    round(ifnull(lead(sum(next_day),1)over(order by date)/sum(new),0),3)
from (select date,
          if(date=(min(date)over(partition by user_id)),1,0) as new,
          if(date=date_add(min(date)over(partition by user_id),interval 1 day),1,0) as next_day
      from login) t
group by date 
order by date

其中，from后面的子查询中的new是指如果一条记录在这天是第一次登录，则记为1，否则记为0。next_day是指一条如果在第一天登陆后的第二天也登陆了，则在第二天处记为1，否则记为0，之后在外侧嵌套一层查询用于汇总计算。
需要注意的是，假设用户A在日期a登陆，那么对new求和sum(new)的结果会在日期a上，而对next_day求和sum(next_day)会记录在日期a+1上，即日期是错开的，所以计算留存率时要借助lead(expr,n)窗口函数，作用为返回之前n条记录的expr值，在这里n取1。
两者汇总后相除，再使用round和ifnull，如此即可计算出每一天的留存率。