再第一种方法基础上改了一下： select a.date, round( count(case when date_sub(b.date, interval 1 day) = a.date then a.user_id end) / count(distinct a.user_id),2 ) p from (select user_id,min(date) as date from login group by user_id) AS a join (select user_id,date from login) b on a.user_id = b.user_id group by a.date union select date ,0.00 from login where date not in (select min(date) from login group by user_id) order by date