一、知识点总结


把有用的知识写在前面，以方便自个儿复习观看😊

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 (不跳过排名，可以理解为对类别进行计数)

2）分类聚合函数
  • 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*/
4）将用时之差的两倍和试卷时长进行对比，选取需求字段，并按照试卷ID进行降序排序
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
;