select a.dt, round(count(b.uid)/count(a.uid),2) as uv_rate from (select uid, min(date(in_time)) as dt from tb_user_log group by uid) a left join (select uid, date(in_time) as dt from tb_user_log union select uid, date(out_time) as dt from tb_user_log) b on a.uid = b.uid and a.dt = date_sub(b.dt, interval 1 day) where date_format(a.dt,'%Y-%m') = '2021-11' group by a.dt order by a.dt

  1. 表1，每个用户首次登陆的时间
  2. 表2，每个用户的登陆信息表，union去除in_time 与 out_time的重复记录
  3. left join，uid相同且表1与表2的日期差为1，表示第二天依旧登陆的用户记录，如果没有登陆则表2数据为空
  4. group by 首次登陆时间a.dt，计算留存率