开窗sql137 第二快/慢用时之差大于试卷时长一半的试卷

#需求：找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息
#输出：exam_id、duration、release_time
#要求：按exam_id降序；时差为，
#拆解：用时之差：每个试卷ID下timestampdiff(second,start_time,submit_time)第二快和第二慢的；求第二快/慢，做两个开窗排名，对时差order by asc/desc+where ck=2
#大于试卷时长的一半：以second为单位计算，试卷时长的一半=duration*30；对作答时差赋值，耗时长排名为2的为正值，耗时短排名为2的为负值，二者求和>duration*30的即为输出范围，因此这一步写在where或having中
select exam_id,duration,release_time
from(
    select exam_id,duration,release_time,
    timestampdiff(second,start_time,submit_time) e1,
    #子查询中做时差字段，是为了后续case when赋值，筛选大于试卷时长一半的数据
    row_number()over(partition by exam_id order by timestampdiff(second,start_time,submit_time) asc) ck1,
    row_number()over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) ck2
    from exam_record join examination_info using(exam_id)
    where submit_time is not null
    #材料中可以看到，有submit_time为null的数据，这种的不能加到开窗中做排名，影响结果
) w1
group by exam_id
having sum(case
    when ck1=2 then -e1
    when ck2=2 then e1
    else 0
    end
)>duration*30
order by exam_id desc