题解 | #第二快/慢用时之差大于试卷时长一半的试卷#

select distinct exam_id
        ,duration
        ,release_time
from(
    select er.exam_id
    /*作答时间timestampdiff(second,start_time,submit_time)第二快*/
            ,nth_value(timestampdiff(second,start_time,submit_time),2) over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) AS fast_2 
            ,nth_value(timestampdiff(second,start_time,submit_time),2) over(partition by exam_id order by timestampdiff(second,start_time,submit_time) asc) AS last_2
            ,duration
            ,release_time
    from exam_record er
    inner join examination_info ei on er.exam_id = ei.exam_id
)t1
where fast_2 - last_2 > duration*30
order by exam_id desc

1、timestampdiff(second, start_time, submit_time)计算出作答时间，单位秒

2、nth_value(timestampdiff(secon, start_time, submit_time),2) over(partion by exam_id .....desc/asc)取出每类试卷exam_id作答时间第二快和第二慢的值，是一个开窗函数。每一行答题记录都添加last_2和fast_2，形成表t1

3、从t1中取出满足条件的exam_id