官网链接:
第二快慢用时之差大于试卷时长一半的试卷_牛客题霸_牛客网现有试卷信息表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 :条件+聚合