select date, sum(case when pass_days=1 then 1 else 0)/count(distinct user_id) as 后1日留存率, sum(case when pass_days=2 then 1 else 0)/count(distinct user_id) as 后2日留存率, sum(case when pass_days=3 then 1 else 0)/count(distinct user_id) as 后3日留存率 from( select t1.date,t1.user_id, datediff(t2.date,t1.date) as pass_days from ( select date,user_id from log group by date,user_id ) t1 left join ( select date,user_id from log group by date,user_id ) t2 on t1.user_id=t2.user_id where t2.date>t1.date ) t3 group by date