题目：

找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序

难点：开窗函数、CASE WHEN的行列转换

  • 第二快和第二慢：这里就需要用到开窗函数来进行排序（我使用的是DENSE_RANK()，原因下面会仔细阐述）
  • 要将每个exam_id的第二快和第二慢相减，必须使两者处于同一行，这里就考察CASE WHEN的高频用法：行列转换
  • 使用TIMESTAMPDIFF的时候，单位参数需要使用SECOND才是最准确的​​​
  • 开窗函数的用法：NTH_VALUE 函数（ps：本次解题没用）
  • 在看别人的解答时，发现MySQL的开窗函数中还有提供NTH_VALUE(expression, N) —— 该函数允许从有序行集中的第N行获取值
  • NTH_VALUE()函数返回expression窗口框架第N行的值。如果第N行不存在，则函数返回NULL。N必须是正整数，例如1,2和3。

（具体链接请参考：https://www.begtut.com/mysql/mysql-nth_value-function.html）

解题思路

步骤1：找出每一记录行中的作答时间，因为时间戳是精确到秒的，因此TIMESTAMPDIFF函数也应该使用“秒”为单位才能返还最准确的结果

SELECT  exam_id, 
        TIMESTAMPDIFF(SECOND, start_time, submit_time) AS time_sec,
        /* 按照完成时间最快来排序，排序2代表第二快 */    
        DENSE_RANK() OVER 
                    (PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(SECOND, start_time, submit_time) ASC) AS fast_rank,
        /* 按照完成时间最慢来排序，排序2代表第二慢 */
        DENSE_RANK() OVER 
                    (PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(SECOND, start_time, submit_time) DESC) AS slow_rank
FROM exam_record
WHERE submit_time IS NOT NULL  /* submit为空的作答都不作数，也没必要对其进行时间差计算 */

步骤2：因为上一步使用的是DENSE_RANK()，同一exam_id下可能存在：共用同一个ranking的多个记录行（即，时间差相同），因此需要使用DISTINCT去重

  • 这里DISTINCT的用法非常直接，直接加入到上述SELECT语句中，对（exam_id, time_sec, fast_rank, slow_rank）这一组合进行去重
  • 有同学可能会疑惑，为什么可以直接使用DISTINCT，为什么敢保证去重后的结果是正确的，原因很简单 —— 如果有两行（或多行）的time_sec是相同值，那么它们的fast_rank一定是相等的，slow_rank也一定是相等的
  • 而这一步是否使用DISTINCT，在现实中（之所以说现实中也是有原因的，因为在本题目及其用例中，其实不存在重复时间差这一情况...已考察了...Anyway，不能局限于代码能跑就行的地步，对吧）也会直接影响到下一步的结果
SELECT DISTINCT exam_id, 
        TIMESTAMPDIFF(SECOND, start_time, submit_time) AS time_sec,
        DENSE_RANK() OVER 
                    (PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(SECOND, start_time, submit_time) ASC) AS fast_rank,
        DENSE_RANK() OVER 
                    (PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(SECOND, start_time, submit_time) DESC) AS slow_rank                    
FROM exam_record 
WHERE submit_time IS NOT NULL

步骤3：基于步骤2的结果表，将其与examination_info表联结，并找出来每一个exam_id的第二快与第二慢的时间差，以及判断是否大于exam本身的试卷时长的一半

  1. 这一步最大的理解阻碍在于，CASE WHEN的行列转换，以及与GROUP BY的组合用法
  • 首先需要知道，如何找出每一个exam_Id对应的第二快和第二慢，这里要用到CASE WHEN的行列转换，请看代码：
SELECT t1.exam_id,
       /* 只有当第二快时才返还对应的作答时间，其余时间皆为0，不予考虑 */
       SUM(CASE WHEN fast_rank = 2 THEN time_sec ELSE 0 END) AS fast_2,
       /* 同理，只有当第二慢时才返还对应的作答时间 */
       SUM(CASE WHEN slow_rank = 2 THEN time_sec ELSE 0 END) AS slow_2,
       /* 这一行最重要，前两行是为了便于理解；
          找出了exam_id的第二快和第二慢之后，就可以将两者进行相减了，注意，此时它们是两列、同一行相减的状态，而非同列、上下两行相减 */
       (SUM(CASE WHEN slow_rank = 2 THEN time_sec ELSE 0 END) - SUM(CASE WHEN fast_rank = 2 THEN time_sec ELSE 0 END)) AS time_df
FROM (
      SELECT DISTINCT exam_id, 
             TIMESTAMPDIFF(SECOND, start_time, submit_time) AS time_sec,
             DENSE_RANK() OVER (PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(SECOND, start_time, submit_time) ASC) AS fast_rank,
             DENSE_RANK() OVER (PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(SECOND, start_time, submit_time) DESC) AS slow_rank                    
      FROM exam_record 
      WHERE submit_time IS NOT NULL
     ) AS t1
GROUP BY t1.exam_id /* 必须使用GROUP BY，这才能按照exam_id分别求出，它们各自的第二快、第二慢、以及两者时间之差 */
  1. 在得到每个exam的第二快/第二慢时间之差之后，我们需要判断时间差是否比exam本身的限制时长的一半要大，这里就需要使用联结，很简单，难点在于HAVING的理解
  • HAVING条件其实也很明确了，就是时间差要比试卷时长的一半要大，因此代码为：
/* 转换成秒再比较 */
HAVING (SUM(CASE WHEN slow_rank = 2 THEN time_sec ELSE 0 END) - SUM(CASE WHEN fast_rank = 2 THEN time_sec ELSE 0 END))
		> examniation_info.duration * 60 / 2
  • 因此，下一步要怎么做也就很明确了，只是联结和HAVING需要用上，请看代码：
SELECT t1.exam_id,

       /* 这三列已经不需要提取了，只是为了方便理解 */
#       SUM(CASE WHEN fast_rank = 2 THEN time_sec ELSE 0 END) AS fast_2,
#       SUM(CASE WHEN slow_rank = 2 THEN time_sec ELSE 0 END) AS slow_2,
#       (SUM(CASE WHEN slow_rank = 2 THEN time_sec ELSE 0 END) - SUM(CASE WHEN fast_rank = 2 THEN time_sec ELSE 0 END)) AS time_df,

       /* 新增两列examination_info的字段 */
        i.duration,
        i.release_time

FROM (
      SELECT DISTINCT exam_id, 
             TIMESTAMPDIFF(SECOND, start_time, submit_time) AS time_sec,
             DENSE_RANK() OVER (PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(SECOND, start_time, submit_time) ASC) AS fast_rank,
             DENSE_RANK() OVER (PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(SECOND, start_time, submit_time) DESC) AS slow_rank                    
      FROM exam_record 
      WHERE submit_time IS NOT NULL
     ) AS t1

/* 以exam_id为联结字段 */
INNER JOIN
examination_info AS i
ON t1.exam_id = i.exam_id

GROUP BY t1.exam_id
/* HAVING找出第二快与第二慢用时之差大于试卷时长一半的试卷 */
HAVING (SUM(CASE WHEN slow_rank = 2 THEN time_sec ELSE 0 END) - SUM(CASE WHEN fast_rank = 2 THEN time_sec ELSE 0 END))
        > i.duration * 30

/* 最终结果要按照试卷ID降序排序 */
ORDER BY exam_id DESC;

以上为全部的解题步骤，如有问题请随时讨论～