题解 | #连续两次作答试卷的最大时间窗#
连续两次作答试卷的最大时间窗
https://www.nowcoder.com/practice/9dcc0eebb8394e79ada1d4d4e979d73c
#1)筛选出至少2021年中至少有两天作答过试卷的人 #计算出至少两天打过试卷的uid SELECT uid FROM exam_record GROUP BY uid HAVING COUNT(DISTINCT DATE(start_time))>=2 #2)算该年连续两次作答试卷的最大时间窗days_window #1. 开窗,根据uid分区,start_time排序[正序),下一次做的时间lead另起一行lead(start_time,1,null), #下一次做卷子的时间;LEAD(start_time, 1, NULL) OVER(PARTITION BY uid ORDER BY start_time) #2021最后一次做的时间 max:目的是为了计算2021年以来第一次做卷子和最后一次做试卷的时间差 #两种方法: #第一种方法:datediff(max(date(start_time)), min(date(start_time))) #第二种方法: #(1)先开窗计算出最大时间[注意:这里有个坑,不能order by,不然最大就是自己 #MAX(start_time) OVER(PARTITION BY uid) as lastest_time #(2)再计算出最大的时间差 #MAX(DATEDIFF(lastest_time,start_time) + 1) #2. 计算连续两次作答试卷时间窗,以及2021年来第一次和最后一次的相隔时间,这段时间做的试卷总数count(*) #连续两次作答试卷的时间窗 #(1) 先开窗算出下一次的做题时间 #LEAD(start_time, 1, NULL) OVER(PARTITION BY uid ORDER BY start_time) AS next_time #(2) 再用datediff函数计算两次相隔时间 #DATEDIFF(next_time,start_time) + 1 #3)用户在2021年days_window天里平均会做多少套试卷 #最后就是按照uid分组取最大时间窗和这些天平均做多少套试卷 #avg_exam_cnt = 总作答次数/最大时间差*最大时间窗 #总作答次数:count(*) #最大时间窗:max(DATEDIFF(next_time,start_time) + 1) #最大时间差:MAX(DATEDIFF(lastest_time,start_time) + 1) SELECT uid, DATEDIFF(MAX(start_time), MIN(start_time)), MAX(DATEDIFF(next_time,start_time)) + 1 AS days_window, ROUND(COUNT(*)/MAX(DATEDIFF(lastest_time,start_time) + 1)* MAX(DATEDIFF(next_time,start_time) + 1),2) AS avg_exam_cnt FROM( SELECT uid, start_time, LEAD(start_time, 1, NULL) OVER(PARTITION BY uid ORDER BY start_time) AS next_time, MAX(start_time) OVER(PARTITION BY uid) AS lastest_time FROM exam_record WHERE uid IN (SELECT uid FROM exam_record GROUP BY uid HAVING COUNT(DISTINCT DATE(start_time))>=2) AND YEAR(start_time) = 2021) t GROUP BY uid