SELECT l.date, round(ifnull(sum((m.user_id,date_add(m.min_date,interval 1 day)) in (select user_id,date from login))/count(m.user_id),0),3) p # 计算第2天登录的次数/第一天登录次数。 FROM login l LEFT JOIN ( SELECT user_id, min(date) min_date FROM login GROUP BY user_id ) m ON l.user_id=m.user_id and l.date=min_date GROUP BY l.date order by l.date ; #------------------------------------------------------------------------------- # 利用窗口函数计算比较简单 select date, round(ifnull(sum(datediff(l_date,date)=1)/sum(date=f_date),0),3) p from( select *, FIRST_VALUE(date)over(partition by user_id order by date) f_date, lead(date,1)over(partition by user_id order by date) l_date from login ) f group by date ;