网站的视频怎么下载,广州网站建设联系信科海珠,海阳市住房和城乡建设局官方网站,淘宝cms建站官网链接#xff1a;
第二快慢用时之差大于试卷时长一半的试卷_牛客题霸_牛客网现有试卷信息表examination_info#xff08;exam_id试卷ID, tag试卷类别,。题目来自【牛客题霸】https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166?tpId240
0 问题描述 试… 官网链接
第二快慢用时之差大于试卷时长一半的试卷_牛客题霸_牛客网现有试卷信息表examination_infoexam_id试卷ID, tag试卷类别,。题目来自【牛客题霸】https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166?tpId240
0 问题描述 试卷信息表examination_infoexam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间试卷作答记录表exam_recorduid用户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 小结 上述案例用到的知识点
1timestampdiff函数 timestampdiff MySQL 中用来计算两个日期或时间之间的差值的函数 语法timestampdiff(unit, start_date, end_date) 参数说明 unit差值的单位可以是second秒、minute分、hour小时、day天、week周、month月、quarter季度或 year年。 start_date表示时间段的起始时间 end_date表示时间段的结束时间 2row_number() over(partition by ..order by ..desc)窗口函数
3sum case when 条件聚合
