题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
http://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 (不跳过排名，可以理解为对类别进行计数)
- sum()...group by...
3)求时间差函数
- timestampdiff(时间格式，开始时间，结束时间)
Tips：解题小技巧
- 一般涉及到同一类之间的对比求和等等操作的时候，常常会用到group by 函数来匹配聚类函数使用；
- 如果涉及判断的操作，常常会用到条件语句只有1个判断可直接用if，如果大于1个判断选择用case when语句。
二、解题步骤拆分
1、题目解读
题目：找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序。
题目中隐藏的坑
坑1：第一快和第二慢：通过用户完成试卷的时间对比来定义快慢，首先需要求每张试卷的完成时间=（submit_time - start_time）命名为 time_diff
坑2：第二快和第二慢的用时之差=某科卷子的第二慢用时-该科卷子的第二快用时（这里我花了比较长的时间理解这句话的定义）
2、解题步骤拆分
STEP1:求各个试卷的用时之差，并进行正逆序排序
STEP2:求第二快和第二慢的用时之差，并和试卷规定时长（duration）进行对比STEP3：试卷ID降序排序
三、步骤代码
1）求各个试卷的用时之差timestampdiff(minute , start_time ,submit_time ) AS time_diff
2)进行正逆序排序
ROW_NUMBER()OVER(PARTITION BY a.exam_id ORDER BY time_diff DESC ) rk_desc -- 逆序 这里直接放time_diff 是为了便于理解，别名不能在同一层查询中直接使用 ROW_NUMBER()OVER(PARTITION BY a.exam_id ORDER BY time_diff ASC) -- 正序正确代码：不用time_diff别名，直接用原函数timestampdiff(minute , start_time ,submit_time )
ROW_NUMBER()OVER(PARTITION BY a.exam_id ORDER BY timestampdiff(minute,a.start_time,a.submit_time) DESC ) rk_desc -- 逆序 ROW_NUMBER()OVER(PARTITION BY a.exam_id ORDER BY timestampdiff(minute,a.start_time,a.submit_time) ASC ) rk_asc -- 正序
第一层子查询完整代码
SELECT a.exam_id,timestampdiff(minute,a.start_time,a.submit_time) time_diff ,b.duration,b.release_time, ROW_NUMBER()OVER(PARTITION BY a.exam_id ORDER BY timestampdiff(minute,a.start_time,a.submit_time) DESC ) rk_desc, ROW_NUMBER()OVER(PARTITION BY a.exam_id ORDER BY timestampdiff(minute,a.start_time,a.submit_time) ASC ) rk_asc FROM exam_record a LEFT JOIN examination_info b ON a.exam_id=b.exam_id WHERE a.submit_time IS NOT NULL; /* 该子查询命名为t1*/rk_desc 对用时进行逆序排列，排名前用时长
rk_ace 对用时进行正序排列，排名前用时少
3）求第二快和第二慢的用时之差
代入场景理解下需求：exam_id=9001，当rk_desc=2时time_diff=50 ，当rk_asc=2时time_diff=11，两个time_diff的差为39
SUM(CASE WHEN rk_desc=2 THEN time_diff WHEN rk_asc=2 THEN -time_diff ELSE 0 END) /*使用case when 进行赋值，取用时第二慢为正，第二快为负，其他未0，求和赋值后的9001试卷ID的所有用时*/
这里有两个判断所以选择用case when 语句。同时涉及到类数据之间的对比（exam_id）所以需要用到 group by 函数。
SELECT *, SUM(CASE WHEN rk_desc=2 THEN time_diff WHEN rk_asc=2 THEN -time_diff ELSE 0 END) sum_time FROM t1 GROUP BY exam_id /*该子查询命名为t2*/
SELECT exam_id,duration,release_time FROM t2 WHERE sum_time*2>=duration ORDER BY exam_id DESC ;
四、完整代码组装
SELECT exam_id,duration,release_time FROM(SELECT exam_id,duration,release_time, SUM(CASE WHEN rk_desc=2 THEN time_diff WHEN rk_asc=2 THEN -time_diff ELSE 0 END) sum_time FROM (SELECT a.exam_id,timestampdiff(minute,a.start_time,a.submit_time) time_diff ,b.duration,b.release_time, ROW_NUMBER()OVER(PARTITION BY a.exam_id ORDER BY timestampdiff(minute,a.start_time,a.submit_time) DESC ) rk_desc, ROW_NUMBER()OVER(PARTITION BY a.exam_id ORDER BY timestampdiff(minute,a.start_time,a.submit_time) ASC ) rk_asc FROM exam_record a LEFT JOIN examination_info b ON a.exam_id=b.exam_id WHERE a.submit_time IS NOT NULL) t1 GROUP BY exam_id) t2 WHERE sum_time*2>=duration ORDER BY exam_id DESC ;
SQL解题集 文章被收录于专栏
这是牛客SQL相关的解题集
