select t2.uid,days_window,round(exams*days,2) avg_exam_cnt from( select uid, max(diff)+1 days_window, 1/(DATEDIFF(max(start_days),min(start_days))+1) *(max(diff)+1) days -- uid,count(start_days),max(start_days),min(start_days),DATEDIFF(max(start_days),min(start_days)) days_window,max(diff) diff2 from ( select uid,start_days,lead(start_days,1) over(PARTITION by uid order by uid) start_days2,DATEDIFF(lead(start_days,1) over(PARTITION by uid order by uid,start_days asc), start_days) diff from( select distinct uid,DATE_FORMAT(start_time,'%Y-%m-%d') start_days from exam_record where left(start_time,4)='2021' group by uid,start_days order by 1,2 ) t1 ) t group by uid having DATEDIFF(max(start_days),min(start_days))>0 ) t2 left join ( select uid,count( exam_id) exams from exam_record where left(start_time,4)='2021' group by uid ) c on t2.uid=c.uid where c.uid is not null order by 2 desc,3 desc 1、时间因素 需要year=2021年 2、排序问题,出来两个条件降序 3、开窗函数 往上lead lead(字段,2) over(partition by uid order by uid) rn