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

题目：

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

条件

1）2021年

2）至少有两天作答：必须是有两天，即便是同一天做了两张试卷，也视为只有一天

3）连续两次作答间的时间差最大

4）历史规律：全周期内（非两次连续作答周期内），平均每天做多少张试卷

5）days_window天里平均会做多少套试卷: 历史规律得出的平均数 * 最大时间差

6）按最大时间窗和平均做答试卷套数倒序排序

题目示例：用户1006分别在20210901、20210906、20210907作答过3次试卷，连续两次作答最大时间窗为6天（1号到6号），他1号到7号这7天里共做了3张试卷，平均每天3/7=0.428571张，那么6天里平均会做0.428571*6=2.57张试卷（保留两位小数）；用户1005在20210905做了两张试卷，但是只有一天的作答记录，过滤掉。

窗口函数的用法

1）LEAD函数、LAG函数的用法（这里也照搬了CSDN中的解读）

LEAD() OVER() 与 LAG() OVER() 函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前 N 行的数据 (lag) 和后 N 行的数据 (lead) 作为独立的列, 从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且 LAG 和 LEAD 有更高的效率。

（原文链接：https://blog.csdn.net/sinat_26811377/article/details/107188400 ）

LEAD函数：LEAD(field, N, defaultvalue)，“field“代表查询字段，“N”代表【往后N行】查找，“defaultvalue”代表当不符合条件时（e.g. 空值，NULL等），默认返还的新数值
LAG函数：LAG(field, N, defaultvalue)，同上，只是“N”代表【往前N行】查找

2）日期函数（这里直接使用了@盐咸咸的blog笔记）

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

解题思路：

步骤1：找出exam_record表中，2021年中，每个用户的每次作答间的时间差，这里就直接使用LEAD函数与开窗函数的结合

SELECT uid,
       exam_id, 
       start_time,
       /* LEAD()求的就是每一次作答之后的下一次作答时间 */
       LEAD(start_time, 1) OVER (PARTITION BY uid ORDER BY start_time) AS next_time,
       /* 得出下一次作答时间之后，再使用DATEDIFF计算每两次作答间的时间差 */
       DATEDIFF(
       			LEAD(start_time, 1) OVER (PARTITION BY uid ORDER BY start_time), /* 下次作答时间 */
       			DATE(start_time)  /* 本次作答时间 */         
                ) + 1 AS df /* 最终DATEDIFF得出的天数差还需要 +1，
                			   因为本次作答时间也算为1天，比如说1号～6号的差值为5天，
                               但是本题目认为实际差值为6天 */
FROM exam_record
WHERE YEAR(start_time) = 2021 /* 只要2021年的作答记录 */

步骤2：找出2021年中，每个用户的最大时间差，对步骤1的结果表使用嵌套子查询和GROUP BY即可

SELECT uid, 
       MAX(df) AS df
FROM

	(
     SELECT uid,
       		exam_id, 
       		start_time,
       		LEAD(start_time, 1) OVER (PARTITION BY uid ORDER BY start_time) AS next_time,
       		DATEDIFF(
       			LEAD(start_time, 1) OVER (PARTITION BY uid ORDER BY start_time), 
       			DATE(start_time)
                ) + 1 AS df 
     FROM exam_record
     WHERE YEAR(start_time) = 2021
	) AS t1
    
GROUP BY uid

步骤3：找出2021年中，每个用户（不管是否有两天以上作答）在全作答周期中的平均作答次数 —— 需要先计算出每个用户第一次作答和最后一次作答的时间差，以及作答的试卷数量

PS：这里的试卷数量，题目没讲的很清楚，一张试卷如果被做过3次，那也算是3次作答，而非一次作答，因此下面的查询语句中未使用“COUNT(DISTINCT exam_id)” —— 也正因如此，在这一步中：

使用COUNT(exam_id)和COUNT(start_time)还是其他非空字段都是等价的

SELECT uid,
		/* 全周期内的总作答次数，可以使用COUNT(start_time)、COUNT(uid)来代替  */
       COUNT(exam_id) AS cnt,
		/* DATEDIFF、MAX、MIN函数的组合来计算最大时间差  */
       DATEDIFF(MAX(DATE(start_time)), MIN(DATE(start_time))) + 1 AS max_df,
		/* 上述两者相除，则是历史规律的平均作答次数了  */       
       COUNT(exam_id) / (DATEDIFF(MAX(DATE(start_time)), MIN(DATE(start_time))) + 1 ) AS avg_cnt
FROM 
	/* 还是对步骤1的结果表进行嵌套查询 */
	(
     SELECT uid,
            exam_id,
            start_time,
            LEAD(start_time, 1) OVER (PARTITION BY uid ORDER BY start_time) AS next_time,
            DATEDIFF(
                LEAD(start_time, 1) OVER (PARTITION BY uid ORDER BY start_time),
                DATE(start_time)
                ) + 1 AS df
     FROM exam_record
     WHERE YEAR(start_time) = 2021
    ) AS t1

GROUP BY uid /* 与步骤2一样都需要GROUP BY 聚合分组 */

步骤4：不难发现，其实步骤2和3，都是直接对步骤1的结果表进行嵌套子查询，因此，步骤2和3是完全可以合并的！这一点很简单也很重要，能省不少时间和代码！

另外，我个人会在步骤4直接剔除每次作答都是同一天的用户

PS：因此步骤中还使用了HAVING子句，把最大时间差，即，MAX(df) 为1的用户剔除，因为时间差为1实际上代表：该用户每次作答都是在同一天，这不符合题目的要求

SELECT uid,
       MAX(df) AS df,
       COUNT(exam_id) AS cnt,
       DATEDIFF(MAX(DATE(start_time)), MIN(DATE(start_time))) + 1 AS max_df,
       COUNT(exam_id) / (DATEDIFF(MAX(DATE(start_time)), MIN(DATE(start_time))) + 1) AS avg_cnt
FROM
    (SELECT uid,
           exam_id,
           start_time,
           LEAD(start_time, 1) OVER (PARTITION BY uid ORDER BY start_time) AS next_time,
           DATEDIFF(LEAD(start_time, 1) OVER (PARTITION BY uid ORDER BY start_time), DATE(start_time)) + 1 AS df
    FROM exam_record
    WHERE YEAR(start_time) = 2021) AS t1
GROUP BY uid
HAVING MAX(df) > 1 /* 剔除作答时间都在同一天的用户 */

步骤5：对步骤4的结果表再进行一次简单的嵌套子查询，计算最终的：按照历史规律，连续两次作答试卷的最大时间窗内，平均能做多少套试卷


SELECT t2.uid, 
        t2.df AS days_window, 
        ROUND(t2.df * t2.avg_cnt, 2) AS avg_exam_cnt
FROM
  (
    SELECT uid, 
           MAX(df) AS df,
           DATEDIFF(MAX(DATE(start_time)), MIN(DATE(start_time))) + 1 AS max_df, 
           COUNT(exam_id) AS cnt,
           COUNT(exam_id) / (DATEDIFF(MAX(DATE(start_time)), MIN(DATE(start_time))) + 1) AS avg_cnt
    FROM
    
        (
         SELECT uid,
                exam_id, 
                start_time,
                LEAD(start_time, 1) OVER (PARTITION BY uid ORDER BY start_time) AS next_time,
                DATEDIFF(LEAD(start_time, 1) OVER (PARTITION BY uid ORDER BY start_time), DATE(start_time)) + 1 AS df
        FROM exam_record
        WHERE YEAR(start_time) = 2021
        ) AS t1
        
    GROUP BY uid
    HAVING MAX(df) > 1
  ) AS t2

ORDER BY days_window DESC, avg_exam_cnt DESC;
/* 按最大时间窗和平均做答试卷套数倒序排序 */