第二种窗口函数方法中的第11行sql中：where限定的有问题，因为这样会把11月份之前的老用户当成新用户充当分母计算留存率；第15行sql中的datediff(next_dt,dt)=1限定也有点小问题，因为存在持续超过2天一直挂着的用户-个人觉得这种情况应该考虑的，但是介于已经把out_time和in_time都转成一行了，这种情况目前看了用这个sql解决不了的。sql改一下可以通过： WITH t1 AS( select uid,dt,new_dt,next_dt from( SELECT uid,dt, MIN(dt) OVER (PARTITION BY uid) AS new_dt, LEAD(dt,1) OVER (PARTITION BY uid ORDER BY dt) AS next_dt FROM( SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log UNION SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log ) act_table #WHERE DATE_FORMAT(dt,'%Y-%m')='2021-11' ) t1 where datediff(new_dt,'2021-11-01')>=0 ) SELECT dt, ROUND(SUM(CASE WHEN dt=new_dt AND datediff(next_dt,dt)=1 THEN 1 ELSE 0 END) / SUM(CASE WHEN dt=new_dt THEN 1 ELSE 0 END),2) uv_left_rate FROM t1 GROUP BY dt HAVING uv_left_rate IS NOT NULL ORDER BY dt;