题解 | #连续两次作答试卷的最大时间窗#

一、知识点汇总

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

1）lead的窗口函数用法

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

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

SELECT id,score,Lead(score,2) over(order by id) lead_score,-- score数列向前推动2位，后面就腾空了2个位置
      Lag(score,2) over(order by id) lag_score, -- score数列向后推2位，腾空2个位置
      lag(score,2,666) over(order by id) lag_score_3 -- score数列向后推动2位，空值被填充为666
FROM exam_record;

2）datediff 函数

datediff(时间1，时间2)：计算两个日期之间间隔的天数，单位为日
timestampdiff(时间单位，开始时间，结束时间):两个日期的时间差，返回的时间差形式由时间单位决定(日，周，月，年)
date_add(日期，INTERVAL n 时间单位) ：返回加上n个时间单位后的日期
date_sub(日期，INTERVAL n 时间单位 )：返回减去n个时间单位后的日期
date_format(时间，‘%Y-%m-%d’)：强制转换时间为所需要的格式

3）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只会先求出这个日期的时分秒差，之后再转换成天数来求日期差。

直接说可能有点懵，看完差别后，具体来看下面这个例子：

#先看第一组时间差是23小时，日期差（9月5日-9月4日）是1天
select DATEDIFF('2021-09-05 12:00:00','2021-09-04 11:00:00') datediff_1,
TIMESTAMPDIFF(hour,'2021-09-04 12:00:00','2021-09-05 11:00:00') hourdiff_1,
TIMESTAMPDIFF(day,'2021-09-04 12:00:00','2021-09-05 11:00:00')timediff_1;

#再看第二组时间差是28小时，日期差（9月5日-9月4日）是1天
select DATEDIFF('2021-09-05 16:00:00','2021-09-04 11:00:00') datediff_2,
TIMESTAMPDIFF(hour,'2021-09-04 12:00:00','2021-09-05 16:00:00') hourdiff_2,
TIMESTAMPDIFF(day,'2021-09-04 12:00:00','2021-09-05 16:00:00')timediff_2;

看完这个是不是就很容易理解啦~😊😊😊

二、题目解读与解题步骤拆分

1、题目解读

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

题目中隐藏的坑
坑1：“解释”中有句话“连续两次作答最大时间窗为6天（1号到6号）”，时间窗的计算方法为：6号-1号+1。我第一次解题的时候没有+1。

坑2：用户在2021年days_window天里，平均会做多少套试卷这个理解起来有一点绕，根据“解释”部分的内容，“他1号到7号这7天里共做了3张试卷，平均每天3/7=0.428571张，那么6天里平均会做0.428571*6=2.57张试卷（保留两位小数）”

计算公式为：（这些用户总共作答的次数/头尾作答时间窗）* 最大间隔天数

这些用户作答的次数命名为 total
头尾作答时间窗：最早一天作答-最晚一天作答+1 命名为diff_time
最大间隔天数：days_window

（total / diff_time）* days_window 命名为avg_exam_cnt
同时要求小数点保留2位小数 round（，2）。

2、解题步骤

step1:在2021年至少有两天作答过试卷的人

也就是头尾作答时间窗>1的用户（根据“解释”的算法，日期间隔的计算需要用日期差+1）

step2:在2021年连续两次作答试卷的最大时间窗
    两次作答的时间进行相减错行排序，使用lead()over来进行排序。
😁 tips：连续的数据进行对比一般使用lead()over()窗口函数进行处理。

step3：用户在2021年days_window天里平均会做多少套试卷
由上个步骤可得：（total / diff_time）* days_window  命名为 avg_exam_cnt

step4:按最大时间窗和平均做答试卷套数倒序排序。

需求字段：uid，days_window，avg_exam_cnt

三、步骤代码

1）最大时间间隔计算方法

DATEDIFF(MAX(start_time),MIN(start_time))+1  diff_time

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

第一次作答时间：start_time
第二次作答时间：LEAD(start_time,1)OVER(PARTITION BY uid ORDER BY start_time) AS next_time

先处理出第一个表格t1

SELECT uid,start_time,
    LEAD(start_time,1)OVER(PARTITION BY uid ORDER BY start_time) AS next_time -- 第二次作答时间
FROM exam_record
    WHERE YEAR(start_time)=2021 -- 2021年的数据

最大连续时间间隔：MAX(DATEDIFF(next_time,start_time))+1

SELECT 
	uid,
	MAX(DATEDIFF(next_time,start_time))+1 days_window -- 最大连续时间窗
FROM (
	SELECT uid,start_time,
	LEAD(start_time,1)OVER(PARTITION BY uid ORDER BY start_time) AS next_time -- 第二次作答时间
	FROM exam_record
    WHERE YEAR(start_time)=2021 -- 2021年的数据
	) t1
GROUP BY uid

3)用户在2021年days_window天里平均会做多少套试卷
（这些用户总共作答的次数/（最后一天作答的日期-第一天作答的日期））*最大连续间隔天数

总作答次数: COUNT(start_time) total
头尾时间间隔: DATEDIFF(MAX(start_time),MIN(start_time))+1 diff_time
最大连续时间间隔：MAX(DATEDIFF(next_time,start_time))+1 days_window

SELECT 
    uid,
    COUNT(start_time) total, -- 用户2021年作答的次数
    DATEDIFF(MAX(start_time),MIN(start_time))+1  diff_time, -- 头尾作答时间窗 
    MAX(DATEDIFF(next_time,start_time))+1 days_window -- 最大间隔天数
FROM (
    SELECT uid,start_time,
    LEAD(start_time,1)OVER(PARTITION BY uid ORDER BY start_time) AS next_time -- 第二次作答时间
    FROM exam_record
    WHERE YEAR(start_time)=2021 -- 2021年的数据
    ) t1
GROUP BY uid;

保留2位小数点:ROUND(,2)

四、完整代码组装

WITH t2 AS (
SELECT 
	uid,
	COUNT(start_time) total, -- 用户2021年作答的次数
	DATEDIFF(MAX(start_time),MIN(start_time))+1  diff_time, -- 头尾作答时间窗 
	MAX(DATEDIFF(next_time,start_time))+1 days_window -- 最大间隔天数
FROM (
	SELECT uid,start_time,
	LEAD(start_time,1)OVER(PARTITION BY uid ORDER BY start_time) AS next_time -- 第二次作答时间
	FROM exam_record
    WHERE YEAR(start_time)=2021 -- 2021年的数据
	) t1
GROUP BY uid
)
SELECT uid,days_window,ROUND(total* days_window/diff_time,2) avg_exam_cnt
FROM t2
WHERE diff_time>1
ORDER BY days_window DESC,avg_exam_cnt DESC
;