题解 | #未完成试卷数大于1的有效用户#

偏移函数nth_value

通过nth_value()定位到指定行，并取出改行数据进行使用

参考代码

select c.exam_id,duration,release_time from
(select a.exam_id,
nth_value(timestampdiff(minute,start_time,submit_time),2) over(partition by a.exam_id order by timestampdiff(minute,start_time,submit_time) desc) as kuai_2,
nth_value(TIMESTAMPDIFF(minute,start_time,submit_time),2) over(partition by a.exam_id order by timestampdiff(minute,start_time,submit_time) asc) as man_2,
duration,release_time FROM
exam_record a join examination_info b on a.exam_id=b.exam_id) c
where kuai_2-man_2>0.5*duration
group by c.exam_id
order by c.exam_id desc