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

SELECT uid, MAX(DATEDIFF(next_st, start_time))+1 days_window, 
ROUND((MAX(DATEDIFF(next_st, start_time))+1)*
(COUNT(start_time)/(DATEDIFF(MAX(start_time),MIN(start_time))+1)),2)
avg_exam_window
FROM (SELECT uid, start_time, lead(start_time,1) over(partition by uid order by start_time) next_st
      FROM exam_record
     ) t1
WHERE YEAR(start_time)=2021
GROUP BY uid 
HAVING MAX(DATEDIFF(next_st, start_time))>0
ORDER BY days_window DESC, avg_exam_window DESC;

这里学的是lead函数。MySQL中的lead()和lag()函数用于获取其分区内任何行的前后值。这些功能称为非聚合窗口功能。lag和lead函数可以在同一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列。实际应用当中，相比于 left join 等自连接方式更加简洁。

我遇到了timestampdiff的坑。 一开始的代码（答案不对）：

SELECT uid, MAX(TIMESTAMPDIFF(DAY, start_time, next_st))+1 days_window, 
ROUND((MAX(TIMESTAMPDIFF(DAY, start_time, next_st))+1)*
(COUNT(start_time)/(TIMESTAMPDIFF(DAY,MIN(start_time),MAX(start_time))+1)),2)
avg_exam_window
FROM (SELECT uid, start_time, lead(start_time,1) over(partition by uid order by start_time) next_st
      FROM exam_record
     ) t1
WHERE YEAR(start_time)=2021
GROUP BY uid 
HAVING MAX(TIMESTAMPDIFF(DAY, start_time, next_st))>0
ORDER BY days_window DESC, avg_exam_window DESC;

看了题解区的回答。2021-09-03 12:01:01 到2021-09-07 12:01:01 经过timestampdiff(DAY,...)+1算得5天，而2021-09-02 12:11:01 到2021-09-06 10:01:01 经过timestampdiff(DAY,...)+1算得4天。导致算出来的答案不对啊！（电脑上没有MYSQL，没法验证；而SQL server没有timestampdiff这个函数，用的是datediff，用法和timestampdiff类似，然后时间算出来的结果是MYSQL的datediff函数一样的。）