题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
SELECT
exam_id,
duration,
release_time
FROM
(
SELECT
exam_id,
duration,
release_time,
fin_time,
TIMEDIFF(
fin_time,
lag( fin_time, 1 ) over ( PARTITION BY exam_id ORDER BY fin_time )) AS con,
0.5 * duration AS com
FROM
(
SELECT
info.exam_id,
info.duration,
info.release_time,
timediff( re.submit_time, re.start_time ) AS fin_time,
dense_rank() over ( PARTITION BY info.exam_id ORDER BY timediff( re.submit_time, re.start_time ) ) AS h_ranking,
dense_rank() over ( PARTITION BY info.exam_id ORDER BY timediff( re.submit_time, re.start_time ) DESC ) AS l_ranking
FROM
exam_record re
JOIN examination_info info ON re.exam_id = info.exam_id
WHERE
re.submit_time IS NOT NULL
) a
WHERE
h_ranking = 2
OR l_ranking = 2
ORDER BY
exam_id,
fin_time
) c
WHERE
MINUTE(con)+SECOND(con)/60 > com
ORDER BY
exam_id DESC;
#解题#
exam_id,
duration,
release_time
FROM
(
SELECT
exam_id,
duration,
release_time,
fin_time,
TIMEDIFF(
fin_time,
lag( fin_time, 1 ) over ( PARTITION BY exam_id ORDER BY fin_time )) AS con,
0.5 * duration AS com
FROM
(
SELECT
info.exam_id,
info.duration,
info.release_time,
timediff( re.submit_time, re.start_time ) AS fin_time,
dense_rank() over ( PARTITION BY info.exam_id ORDER BY timediff( re.submit_time, re.start_time ) ) AS h_ranking,
dense_rank() over ( PARTITION BY info.exam_id ORDER BY timediff( re.submit_time, re.start_time ) DESC ) AS l_ranking
FROM
exam_record re
JOIN examination_info info ON re.exam_id = info.exam_id
WHERE
re.submit_time IS NOT NULL
) a
WHERE
h_ranking = 2
OR l_ranking = 2
ORDER BY
exam_id,
fin_time
) c
WHERE
MINUTE(con)+SECOND(con)/60 > com
ORDER BY
exam_id DESC;
首先通过dense_rank 窗口函数，对所有记录的答题时间进行排序；
然后我们筛选排名第二（h_ranking / l_ranking）的所有记录, 以答题时间（fin_time）进行升序排序
计算第二快和第二慢之间的时间差（con）
最后筛选下满足条件的记录。
投递蚂蚁集团等公司10个岗位
