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

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

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

一.涉及知识点

1.窗口函数 max，row_number

（1）max函数对数据中选定最大值，变为新列

（2）ROW_NUMBER()函数能够对数据中的序号进行顺序显示。

（3）RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3。

（4）DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号，比如序号为1、1、2。

窗口函数的语法结构是：

函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

2.timestampdiff

TIMESTAMPDIFF(unit,begin,end);

返回begin-end的结果，注意位置不要放反了

unit参数：SECOND，MINUTE，HOUR，DAY等，常用的就这几个

3.max，min

（1）max求最大值没啥好说的

（2）min求最小值没啥好说的

二.题目要求

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

1.由第二快和第二慢很容易想到进行排序，经常使用的row_number,rank,dense_rank

2.大于试卷时长的一半的试卷信息,很明显要进行两个表的连接

3.按试卷ID降序排序

三.解题步骤

1.求出所有试卷的时长，灰常简单，直接给出

select uid,exam_id,timestampdiff(minute,start_time,submit_time) diff_time
from exam_record
where score is not null

注意过滤为null的值

产生结果如下：

2.对试卷时长进行排序，用row_number

select uid,exam_id,diff_time,row_number() over(partition by exam_id order by diff_time) rk
from(
    select uid,exam_id,timestampdiff(minute,start_time,submit_time) diff_time
    from exam_record
    where score is not null
)t1

产生结果如下：

3.找到自己各自最大的序号(为啥找最大的从第4步比较好想)

select uid,exam_id,diff_time,rk,max(rk) over(partition by exam_id) max_rk
from (
      select uid,exam_id,diff_time,row_number() over(partition by exam_id order by diff_time) rk
      from(
            select uid,exam_id,timestampdiff(minute,start_time,submit_time) diff_time
            from exam_record
            where score is not null
      )t1
)t2

产生结果如下：

4.把最大值和最小值选出来，注意此时过滤掉rk=max(rk)和rk==1的，就是剩下的第二快和第二慢的

select exam_id,(max(diff_time)- min(diff_time)) diff 
from (
    select uid,exam_id,diff_time,rk,max(rk) over(partition by exam_id) max_rk
    from (
            select uid,exam_id,diff_time,row_number() over(partition by exam_id order by diff_time) rk
        from(
            select uid,exam_id,timestampdiff(minute,start_time,submit_time) diff_time
            from exam_record
            where score is not null
        )t1
    )t2
) t3
where rk != 1 && rk != max_rk
group by exam_id

产生结果如下：

三.组装答案，最后连接一下examination_info即可

with t4 as(
    select exam_id,(max(diff_time)- min(diff_time)) diff 
from (
    select uid,exam_id,diff_time,rk,max(rk) over(partition by exam_id) max_rk
    from (
            select uid,exam_id,diff_time,row_number() over(partition by exam_id order by diff_time) rk
        from(
            select uid,exam_id,timestampdiff(minute,start_time,submit_time) diff_time
            from exam_record
            where score is not null
        )t1
    )t2
) t3
where rk != 1 && rk != max_rk
group by exam_id
)
select e1.exam_id,duration,release_time
from  t4 join examination_info e1
on t4.exam_id = e1.exam_id
where diff >= duration/2
order by e1.exam_id desc