SQL_20230414

SQL136 每类试卷得分前3名

找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。

examination_info表

id	exam_id	tag	difficulty	duration	release_time

exam_record表

id	uid	exam_id	start_time	submit_time	score

解题思路

首先要对连接后的表进行排名处理

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

窗口函数

<窗口函数> over (partition by 用于分组的列名 order by 用于排序的列名

<窗口函数>的位置，可以放以下两种函数：

1. 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2. 聚合函数，如sum. avg, count, max, min等

partition by用来对表分组。order by子句的功能是对分组后的结果进行排序。因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

故在本题中，在SELECT处选出tag, exam_record.uid, MAX(exam_record.score), MIN(exam_record.score)字段后，要在此基础用row_number() over()进行排名

ROW_NUMBER() over (partition by tag ORDER BY MAX(exam_record.score) DESC, MIN(exam_record.score) DESC, exam_record.uid DESC) AS ranking

获取了ranking字段后，取ranking <= 3则筛出前三名

SQL137 第二快/慢用时之差大于试卷时长一半的试卷

examination_info表

id	exam_id	tag	difficulty	duration	release_time

exam_record表

id	uid	exam_id	start_time	submit_time	score

找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序。

解题思路

首先对问题进行拆分

1. 找到第二快和第二慢的用时之差去和duration进行比较
2. 找到第二快和第二慢的用时计算得出差值并排序

对于第二个问题，可以通过ROW_NUMBER()进行排序，用时之差可以采用TIMEDIFF函数。第二快和第二慢可以通过两个排序来实现，DESC实现倒数第二ASC实现正数第二，但在此前我们先对其进行排位，无序进行排序

row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) desc) rank1,
row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) asc) rank2

对于第一个问题，通过SUM CASE组合实现筛选出第二和倒数第二，相减获得差值sub

SUM(CASE WHEN r1 = 2 THEN costtime WHEN r2 = 2 THEN -costtime ELSE 0 END) AS sub

最后sub去和duration进行比较即可

SQL138 连续两次作答试卷的最大时间窗

exam_record表

id	uid	exam_id	start_time	submit_time	score

请计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序。

lead的窗口函数用法

• lead(字段名，n) over () :取值向后偏移n行（空间的理解就是直接将一列数据往前推n个位置，后面的位置就空出来了）;

• lag(字段名，n) over () :取值向前偏移n行（空间的理解就是直接将一列数据往前后n个位置，前面的位置就空出来了）;

• lag(字段名，n，x) over () :取值向前偏移n行，并将空值填充为数字x（空间的理解就是直接将一列数据往前后n个位置，前面的空出来的位置用X填充上） 。

datediff和timestampdiff

• datediff(时间1，时间2)：计算两个日期之间间隔的天数，单位为日
• timestampdiff(时间单位，开始时间，结束时间):两个日期的时间差，返回的时间差形式由时间单位决定(日，周，月，年)
• date_add(日期，INTERVAL n 时间单位) ：返回加上n个时间单位后的日期
• date_sub(日期，INTERVAL n 时间单位 )：返回减去n个时间单位后的日期

datediff和timestampdiff函数的区别

• datediff()函数的作用是求日期差，也就是把一个时间的日期部分取出来求差。例如：'2021-09-05 12:00:00'和'2021-09-04 11:00:00'这两个日期，datediff只取2021-09-05和2021-09-04求日期差，并不会管后面的时间部分。
• timestampdiff()函数的作用则是求时间戳的差，例如：'2021-09-05 12:00:00'和'2021-09-04 11:00:00'这两个日期，datediff只会先求出这个日期的时分秒差，之后再转换成天数来求日期差。

所以日期差和时间差是有区别的！要结合具体的情况来看

解题思路

将问题进行拆分

• 在2021年至少有两天作答过试卷的人：此处一定要用datediff()函数，因为是两天，至于有没有相差24小时不重要！
• 在2021年连续两次作答试卷的最大时间窗：用LEAD(start_time,1)OVER(PARTITION BY uid ORDER BY start_time)来获取排序后的下一个答题时间，之后通过MAX(DATEDIFF(next_time,start_time))+1来获取最大的时间窗
• 用户在2021年days_window天里平均会做多少套试卷：获取2021年总共写的试卷数量total，获取写试卷这段时间的时间间隔，然后再去和时间窗相乘即可total * days_window / diff_time

SQL139 近三个月未完成试卷数为0的用户完成情况

exam_record表

id	uid	exam_id	start_time	submit_time	score

见这种近几、连续、每类前几、各个前几直接考虑窗口函数

解题思路

• dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)，见这种近几、连续、每类前几、各个前几直接考虑窗口函数。将时间按date_format取出年月之后进行dense_rank排序dense_rank() over(partition by uid order by DATE_FORMAT(start_time, '%Y%m') DESC) AS recent，通过该字段小于等于3来获取近三个月的值