题解 | #连续两次作答试卷的最大时间窗#
连续两次作答试卷的最大时间窗
https://www.nowcoder.com/practice/9dcc0eebb8394e79ada1d4d4e979d73c
分享一下自己的思路,因为自己是菜b,所以用的都是菜b都能看懂的思路,可能不是最快捷,但力求最清晰。
第1步:找出2021年作答过试卷的人中,连续两次作答试卷的时间窗的表t1
select uid,datediff(start_time,lag(start_time,1)over(partition by uid order by start_time))+1 days_window from exam_record
where year(start_time) = 2021
第2步:找出2021年至少有两天作答过试卷的人中,连续两次作答试卷的最大时间窗的表a
select uid,max(days_window) days_window from
(这里只可以用内连接或者左连接,不可以用右连接,不然就会出现做了两张试卷,但是只有一天的作答记录的uid)
(select uid,datediff(start_time,lag(start_time,1)over(partition by uid order by start_time))+1 days_window from exam_record
where year(start_time) = 2021) t1
where days_window is not null
group by uid
having max(days_window) != 1
(where days_window is not null是为了过滤某人在2021年第一次作答时间减去之前不存在的作答时间所遗留下的null,同时可以过滤某人在2021年内只在某一天作答了一次的情况所遗留下的nul;
having max(days_window) != 1是为了过滤做了两张试卷,但是只有一天的作答记录的情况)
第3步:找出2021年作答过试卷的人,每个人平均每天做了多少张试卷的表b
select uid,count(start_time)/(datediff(max(start_time),min(start_time))+1) avg_exam_cnt from exam_record
where year(start_time) = 2021
group by uid
(在datediff函数后还需要加1,因为如“解释”所示用户1号到7号这7天里共做了3张试卷,平均每天3/7=0.428571张。如果不加1,那么就变成平均每天3/6了!)
最后一步:将表a的uid及表b的uid作为连接键,将表a连接表b,并从中提取出我们最后想要的输出结果
select a.uid uid,days_window,round(days_window*avg_exam_cnt,2) avg_exam_cnt from
(select uid,max(days_window) days_window from
(select uid,datediff(start_time,lag(start_time,1)over(partition by uid order by start_time))+1 days_window from exam_record
where year(start_time) = 2021) t1
where days_window is not null
group by uid
having max(days_window) != 1) a
join
(select uid,count(start_time)/(datediff(max(start_time),min(start_time))+1) avg_exam_cnt from exam_record
where year(start_time) = 2021
group by uid) b
on a.uid = b.uid
order by days_window desc,avg_exam_cnt desc
写在最后,这题涉及到时间的字段名得用start_time,不用能submit_time,因为题目的要求是作答的人,而不仅仅指交卷的人,所以submit_time是会出现null的,我第一次做就是用的submit_time结果报错,接着把所有的submit_time换成start_time就好了。