牛客SQL137——第二快/慢用时之差大于试卷时长一半的试卷

题目描述
https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166

思路：找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序。由示例数据结果输出如下：

先按照试卷进行排序，分别升序和降序取出第二块和第二慢

select r.exam_id,
nth_value(timestampdiff(second,r.start_time,r.submit_time),2) over (partition by r.exam_id order by timestampdiff(second,r.start_time,r.submit_time) desc) as slow_2,
nth_value(timestampdiff(second,r.start_time,r.submit_time),2) over (partition by r.exam_id order by timestampdiff(second,r.start_time,r.submit_time) asc) as fast_2,
e.duration,e.release_time
from exam_record as r join examination_info as e on r.exam_id=e.exam_id

运行结果

exam_id	slow_2	fast_2	duration	release_time
9001	3000	None	60	2021-09-01 06:00:00
9001	3000	600	60	2021-09-01 06:00:00
9001	3000	600	60	2021-09-01 06:00:00
9001	3000	600	60	2021-09-01 06:00:00
9001	3000	600	60	2021-09-01 06:00:00
9001	None	600	60	2021-09-01 06:00:00
9002	1800	None	60	2021-09-01 06:00:00
9002	1800	1740	60	2021-09-01 06:00:00
9002	1800	1740	60	2021-09-01 06:00:00
9002	1800	1740	60	2021-09-01 06:00:00
9002	None	1740	60	2021-09-01 06:00:00
9003	None	None	80	2021-09-01 10:00:00

---

在对时间之差进行计算，记得要distinct

# 先按照试卷进行排序，分别升序和降序取出第二块和第二慢
select distinct exam_id,duration,release_time from(
    # 先按照试卷进行排序，分别升序和降序取出第二块和第二慢
    select r.exam_id,
    nth_value(timestampdiff(second,r.start_time,r.submit_time),2) over (partition by r.exam_id order by timestampdiff(second,r.start_time,r.submit_time) desc) as slow_2,
    nth_value(timestampdiff(second,r.start_time,r.submit_time),2) over (partition by r.exam_id order by timestampdiff(second,r.start_time,r.submit_time) asc) as fast_2,
    e.duration,e.release_time
    from exam_record as r join examination_info as e on r.exam_id=e.exam_id
) as t
where slow_2-fast_2>duration*30
order by exam_id desc