select date, round(ifnull(count(if(datediff(next_day,date)=1 and num=1,1,null))/count(if(num=1,1,null)),0),3) as p from ( select user_id,date, lead(date,1) over(partition by user_id order by date asc) as next_day, rank() over(partition by user_id order by date asc) as num from login )A group by A.date 计算时 需要增加一个条件用于判断新用户