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

SELECT uid, days_window,
    ROUND((total/max_diff) * days_window, 2) AS avg_exam_cnt
FROM (
    SELECT uid,
        COUNT(start_time) total,
        DATEDIFF(MAX(start_time), MIN(start_time))+1 max_diff,
        MAX(DATEDIFF(next_time, start_time)+1) AS 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
        ) a
    GROUP BY uid
    ) b
WHERE days_window > 1
ORDER BY days_window DESC, avg_exam_cnt DESC;

新知识点：

1.LEAD(column, n, c) OVER() 窗口函数从空间上把column列往上移动n行，并把移动后出现的空值填充为c。从取值上来说，就是取到往后n行的值。因为现在还是站在未移动的角度。

2.LAG() 窗口函数从空间上把column列往下移动（拉）n行，并把移动后出现的空值填充为c。从取值上来说，就是取到往前n行的值。

3.DATEDIFF（date1, date2）函数: DATEDIFF() 函数的行为如下：

• 如果第一个日期晚于第二个日期，结果是正数。单位是天。
• 如果第一个日期早于第二个日期，结果是负数。
• 如果两个日期相同，结果是零。

4.timestampdiff(时间单位，开始时间，结束时间):两个日期的时间差，返回的时间差形式由时间单位决定(日，周，月，年)

5.对于某些情况不能直接在查询中分组、过滤等，可以先将该查询作为一个单独子查询，在外面进行。

根据题意解题：

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

在2021年至少有两天作答过试卷的人：也就作答记录的时间跨度要大于等于2（大于1），时间也得是2021年

连续两次作答试卷的最大时间窗days_window：也就是同一个人按时间顺序连续两次作答的时间差

根据该年的历史规律他在days_window天里平均会做多少套试卷：根据例子得知试卷数=（历史做答数/历史作答时间间隔）*days_window

最关键的是怎么计算days_window，这里就要用到LEAD()函数，将作答时间start_time的取值往下一行（新增到一个字段next_time），这样对于每个用户都知道了连续的时间间隔。LEAD(start_time, 1) OVER(PARTITION BY uid ORDER BY start_time) AS next_time

再计算每个用户的连续最大时间窗口days_window：MAX(DATEDIFF(next_time, start_time)+1) AS days_window

计算每个用户的历史作答数：COUNT(start_time) total

计算每个用户的历史作答时间间隔（从最开始到最后，忽略中间的）：DATEDIFF(MAX(start_time), MIN(start_time))+1 max_diff

再对用户分组：GROUP BY uid

到最外层，查询指定字段，还有计算字段avg_exam_cnt，使用ROUND()函数保留两位小数点

限制至少有两天作答过的：WHERE days_window > 1

最终排序：ORDER BY days_window DESC, avg_exam_cnt DESC;

窗口函数有很多新知识，慢慢练