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

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

https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166

看了高赞回答，自己记录一下解题过程和高赞回答的解题过程便于复习

自己的过程

函数

1)排序窗口函数

rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

2）时间差函数

timestampdiff(unit, begin, end)

unit ：单位
begin ：一般是小的时间
end : 一般是大的时间

思路分析

1）用开窗函数按照exam_id分区，按照用时timestampdiff(minute, start_time, submit_time)正序，并筛选出第二快的数据：ranking=2的数据【注意筛选需要另外再select】，这个表为t2表

select
      *
    from
      (
        select
          uid,
          exam_id,
          timestampdiff(minute, start_time, submit_time) as answer_time,
          row_number() over(
            partition by exam_id
            order by
              timestampdiff(minute, start_time, submit_time) desc
          ) as time_rank
        from
          exam_record
        where
          submit_time is not null
      ) t1
    where
      time_rank = 2

2）用开窗函数按照exam_id分区，按照用时timestampdiff(minute, start_time, submit_time)倒序，并筛选出第二蛮的数据：ranking=2的数据【这张表为t4】

    select
      *
    from
      (
        select
          uid,
          exam_id,
          timestampdiff(minute, start_time, submit_time) as answer_time,
          row_number() over(
            partition by exam_id
            order by
              timestampdiff(minute, start_time, submit_time)
          ) as time_rank
        from
          exam_record
        where
          submit_time is not null
      ) t3
    where
      time_rank = 2

3）按exam_id连接这两张表以及examination_info，需要duration和release_time字段，按条件筛选即可

select
  t2.exam_id,
  info.duration,
  info.release_time
from
  (
    select
      *
    from
      (
        select
          uid,
          exam_id,
          timestampdiff(minute, start_time, submit_time) as answer_time,
          row_number() over(
            partition by exam_id
            order by
              timestampdiff(minute, start_time, submit_time) desc
          ) as time_rank
        from
          exam_record
        where
          submit_time is not null
      ) t1
    where
      time_rank = 2
  ) t2

高赞过程

主要是将第二快和第二慢的数据放在一张表中，然后用case when then else end将第二快和第二慢的时间分出来聚合，可以减少连接表的数量

1）将第二快和第二慢的数据放在一张表

SELECT
  uid,
  exam_id,
  TIMESTAMPDIFF(MINUTE, start_time, submit_time) AS answer_time,
  ROW_NUMBER() OVER(
    PARTITION BY exam_id
    ORDER BY
      TIMESTAMPDIFF(MINUTE, start_time, submit_time)
  ) ascrank,
  ROW_NUMBER() OVER(
    PARTITION BY exam_id
    ORDER BY
      TIMESTAMPDIFF(MINUTE, start_time, submit_time) DESC
  ) descrank
FROM
  exam_record
WHERE
  submit_time IS NOT NULL

2）计算第二快和第二慢的时间差值

SELECT
  exam_id,
  SUM(
    CASE
      WHEN ascrank = 2 THEN - answer_time
      WHEN descrank = 2 THEN answer_time
      ELSE 0
    END
  ) AS answer_time_diff
FROM
(
    SELECT
      uid,
      exam_id,
      TIMESTAMPDIFF(MINUTE, start_time, submit_time) AS answer_time,
      ROW_NUMBER() OVER(
        PARTITION BY exam_id
        ORDER BY
          TIMESTAMPDIFF(MINUTE, start_time, submit_time)
      ) ascrank,
      ROW_NUMBER() OVER(
        PARTITION BY exam_id
        ORDER BY
          TIMESTAMPDIFF(MINUTE, start_time, submit_time) DESC
      ) descrank
    FROM
      exam_record
    WHERE
      submit_time IS NOT NULL
  ) t
GROUP BY
  exam_id