WITH min_dt AS
(SELECT min(date(in_time))dt,uid
FROM tb_user_log
GROUP BY uid)#查找出每天的新用户

SELECT
    b.dt,round(b.uid_cnt/c.uid_cnt,2)uv_left_rate
FROM
    (SELECT subdate(dt_add,interval 1 day)dt,count(distinct tb.uid)uid_cnt
    FROM (SELECT adddate(dt,interval 1 day)dt_add,uid FROM min_dt)a
         LEFT JOIN tb_user_log tb 
         ON ((date(in_time)=dt_add OR date(out_time)=dt_add) AND a.uid=tb.uid)
    GROUP BY dt
    )b RIGHT JOIN (SELECT dt,count(uid)uid_cnt FROM min_dt GROUP BY dt)c
    USING(dt)
WHERE
    year(b.dt)=2021 AND month(b.dt)=11
ORDER BY
    dt;

     

运行结果