牛客网SQL进阶137：第二快/慢用时之差大于试卷时长一半的试卷

  官网链接：

第二快慢用时之差大于试卷时长一半的试卷_牛客题霸_牛客网现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别,。题目来自【牛客题霸】https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166?tpId=240

0 问题描述

• 试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）
• 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）
• 试卷信息表examination_info和试卷作答记录表exam_record, 找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序

1 数据准备

drop table if exists examination_info,exam_record;
CREATE TABLE examination_info (id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',exam_id int UNIQUE NOT NULL COMMENT '试卷ID',tag varchar(32) COMMENT '类别标签',difficulty varchar(8) COMMENT '难度',duration int NOT NULL COMMENT '时长',release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;CREATE TABLE exam_record (id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',uid int NOT NULL COMMENT '用户ID',exam_id int NOT NULL COMMENT '试卷ID',start_time datetime NOT NULL COMMENT '开始时间',submit_time datetime COMMENT '提交时间',score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES(9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),(9002, 'C++', 'hard', 60, '2021-09-01 06:00:00'),(9003, '算法', 'medium', 80, '2021-09-01 10:00:00');INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:51:01', 78),
(1001, 9002, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:59:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9002, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:12:01', 84),
(1003, 9001, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9002, '2021-09-01 14:01:01', null, null),
(1005, 9001, '2021-09-01 14:01:01', null, null),
(1003, 9003, '2021-09-08 15:01:01', null, null);

2 数据分析

完整的代码如下：

select distinct exam_id,duration,release_time
from(select exam_id,duration,release_time,sum(case when rn1 =2 then difftimewhen rn2 =2 then -difftimeelse 0end ) as subfrom(selectexam_id,duration,release_time,difftime,row_number() over(partition  by exam_id order by difftime desc ) as rn1,row_number() over(partition  by exam_id order by difftime ) as rn2from (selecter.exam_id,ei.duration,ei.release_time,timestampdiff(minute,er.start_time,er.submit_time) as difftimefrom exam_record er  join examination_info eion  er.exam_id = ei.exam_idwhere submit_time is not null)tmp1)tmp2group by exam_id)tmp3where sub * 2 >= durationorder by exam_id desc;

上述的解题步骤拆分

step1:求出各试卷的用时之差，并进行正序、逆序排序
step2:求出第二快和第二慢的用时之差，并和试卷规定时长（duration）进行比对

step3:试卷ID降序排序

步骤代码

step1:

selectexam_id,duration,release_time,difftime,--进行正序、逆序排序row_number() over(partition  by exam_id order by difftime desc ) as rn1,row_number() over(partition  by exam_id order by difftime ) as rn2
from (selecter.exam_id,ei.duration,ei.release_time,--step1:求出各试卷的用时之差timestampdiff，并进行正序、逆序排序timestampdiff(minute,er.start_time,er.submit_time) as difftimefrom exam_record er  join examination_info eion  er.exam_id = ei.exam_idwhere submit_time is not null)tmp1;

step2: 使用 case when进行赋值，当rn1 =2 时，代表是第二快的difftime(取正值)；当rn2 =2 时，代表是第二慢的difftime(需要取负值)； 外层再嵌套sum聚合函数，即得到第二快和第二慢的用时之差sub

select exam_id,duration,release_time,sum(case when rn1 =2 then difftimewhen rn2 =2 then -difftimeelse 0end ) as sub
from(selectexam_id,duration,release_time,difftime,row_number() over(partition  by exam_id order by difftime desc ) as rn1,row_number() over(partition  by exam_id order by difftime ) as rn2from (selecter.exam_id,ei.duration,ei.release_time,timestampdiff(minute,er.start_time,er.submit_time) as difftimefrom exam_record er  join examination_info eion  er.exam_id = ei.exam_idwhere submit_time is not null)tmp1)tmp2group by exam_id;

step3: sub和试卷规定时长（duration）进行比对，要求：sub * 2 >= duration

select distinct exam_id,duration,release_time
from(select exam_id,duration,release_time,sum(case when rn1 =2 then difftimewhen rn2 =2 then -difftimeelse 0end ) as subfrom(selectexam_id,duration,release_time,difftime,row_number() over(partition  by exam_id order by difftime desc ) as rn1,row_number() over(partition  by exam_id order by difftime ) as rn2from (selecter.exam_id,ei.duration,ei.release_time,timestampdiff(minute,er.start_time,er.submit_time) as difftimefrom exam_record er  join examination_info eion  er.exam_id = ei.exam_idwhere submit_time is not null)tmp1)tmp2group by exam_id)tmp3where sub * 2 >= durationorder by exam_id desc;

3 小结

  上述案例用到的知识点：

（1）timestampdiff函数

timestampdiff： MySQL 中用来计算两个日期或时间之间的差值的函数；

语法：timestampdiff(unit, start_date, end_date)

参数说明：

   unit：差值的单位，可以是second（秒）、minute（分）、hour（小时）、day（天）、week（周）、month（月）、quarter（季度）或 year（年）。
  start_date：表示时间段的起始时间

  end_date：表示时间段的结束时间

（2）row_number() over(partition by ..order by ..desc)窗口函数

（3）sum +case when ：条件+聚合