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

# 计算时差
# 按时差排两个序，正序、倒序
# 链接分组查询

WITH TD AS
(
    SELECT exam_id, 
            TIMESTAMPDIFF(MINUTE, start_time, submit_time) AS cost,
            duration, release_time,
            ROW_NUMBER() OVER(PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(SECOND, start_time, submit_time) DESC) AS slow,
            ROW_NUMBER() OVER(PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(SECOND, start_time, submit_time) ASC) AS fast
    FROM exam_record a JOIN examination_info b USING(exam_id)
    WHERE a.submit_time is not null
)


SELECT exam_id,duration, release_time
FROM (# 求差用累加，只有当最快的第二个才是正数，最慢的第二个是负数，其他为0
    SELECT exam_id, duration, release_time,
            SUM(CASE WHEN slow = 2 THEN cost WHEN fast = 2 THEN -cost ELSE 0 END) AS cost_diff
    FROM TD
    GROUP BY exam_id
    ) b
WHERE cost_diff >= duration/2
ORDER BY exam_id DESC;

一道题能做一天。

题意：找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息

理解：目标：试卷信息：exam_id,duration, release_time三个字段

限制：完成该试卷用时第二快和第二慢的差大于试卷时长的二分之一

细化：变成了求完成该试卷用时第二快和第二慢的差

首先要知道每个试卷作答第二快合慢的作答记录：

ROW_NUMBER() OVER(PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(SECOND, start_time, submit_time) DESC) AS slow,

ROW_NUMBER() OVER(PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(SECOND, start_time, submit_time) ASC) AS fast

使用窗口函数以试卷id为分组依据，以时间差为排序依据，为每个作答记录赋值——用时正向和负向排序

当然，也要联结两个表，这样把所有要用到的字段全都放在一张表上，FROM exam_record a JOIN examination_info b USING(exam_id)

最后限制提交时间不为空：WHERE a.submit_time is not null

以上就作为一张临时表。

针对这个临时表，再求用时第二快和第二慢的差，上面知道了每次作答记录的用时和排序，下面用累加来计算这个差，并且新设为一个字段。具体是如果是正序第二（第二快）就设为-cost，第二慢就设为cost即可，其他设为0，这样就能计算到差值了：SUM(CASE WHEN slow = 2 THEN cost WHEN fast = 2 THEN -cost ELSE 0 END) AS cost_diff

同时这个也是在同一个试卷下进行的，所以要分组：GROUP BY exam_id

这样就得到了完成该试卷用时第二快和第二慢的差，最后再和试卷时长的一半比较，排序。

踩的坑：1.没有过滤掉没有提交的记录，这样是none，是不对的。

2.求差时还在想把同一试卷的第二快和第二慢的用时分别放在两个字段，最后再相减，但是失败了，而且很繁琐。

ROW_NUMBER() OVER() 每条记录有唯一的序号

RANK() OVER() 序号重叠且跳过 1,2,2,4

DENSE_RANK() OVER() 序号重叠且不跳过 1,2,2,3