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

## lead窗口函数的用法：
## LEAD(字段名，n) OVER() 取值向后偏移n行，即向前推n位，后面有n位的位置空出
## LAG(字段名，n) OVER() 取值向前偏移n行，即向后推n位，前面有n位的空出
## LAG(字段名，n，x) OVER()取值向前偏移n行，并将空值填充为数字x
## 更简单的理解：LEAD是带领，现有的去前面带头；LAG是滞后，现有的去后面呆着

## LEAD/LAG函数可以对现有的列进行前移或后移后生成新列，有两个作用：
## 1.可以做向前或者向后差分：将前移或后移的作为新列，和原来的列进行相减即可
## 2.可以得出这一列中某个值重复出现次数的次数：分别后移1：n位，生成n-1列，如果原来这列某行和其他n-1列的值均相同，即可认为该值出现了n-1次


## 时间差函数
## DATEDIFF(结束时间，开始时间)：计算时间差，单位是日
## TIMESTAMPDIFF(时间单位，开始时间，结束时间)：按照时间单位极端时间差（YEAR,month,week,day,hour,minute,second）
## DATE_ADD(时间，INTERVAL n 时间单位)：返回加上n个时间单位后的日期
## DATE_SUB(时间，INTERVAL n 时间单位)：返回减去n个时间单位后的日期
## DATE_FORMAT(时间，%Y%m%d%)：强制转换时间为需要的格式 按照年-月-日的形式等等

## 题解：

## 第一层：需要得到next_time
## 至少有两天作答：DATE_DIFF(MAX(start_time), MIN(start_time))+1  diff_time
## 生成新的一列，将后面的时间向前移动1位，然后计算差值，即空出后面的位置，需要用LEAD函数
## LEAD(start_time,1) OVER(PARTITION BY uid ORDER BY start_time) AS next_time
## 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

## 第二层：需要计算days_window,以及至少两天作答的total_day > 2
## 计算最大days_window:
## MAX(DATE_DIFF(next_time,start_time)+1) AS days_window
## 需要满足的条件是至少有两天作答：DATE_DIFF(MAX(start_time), MIN(start_time))+1  total_day
## total_day > 2

## 第三层：组装同时计算答题规律和days_window内的答题数量：
## 答题规律 = 答题总数/答题天数 = COUNT(DISTINCT exam_id) / DATE_DIFF(MAX(start_time),MIN(start_time))+1 
## days_window内的答题数量 = 答题规律*days_window
## (days_window * COUNT(DISTINCT exam_id) / DATE_DIFF(MAX(start_time),MIN(start_time))+1) AS avg_exam_cnt

## 组装：
SELECT uid, days_window,ROUND(days_window*total_exam/total_day,2) AS avg_exam_cnt
FROM(
    SELECT
        uid,
        COUNT(start_time) AS total_exam, 
        DATEDIFF(MAX(start_time),MIN(start_time))+1 AS total_day,
        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
    )t1
    GROUP BY uid
)t2
WHERE total_day>2
ORDER BY days_window DESC, avg_exam_cnt DESC