MYSQL练习题：访问日期之间最大的空档期

题目

假设今天的日期是 ‘2021-1-1’ 。编写 SQL 语句，对于每个 user_id ，求出每次访问及其下一个访问（若该次访问是最后一次，则为今天）之间最大的空档期天数 window 。返回结果表，按用户编号 user_id 排序。
查询格式如下示例所示：
UserVisits 表：

user_id	visit_date
1	2020-11-28
1	2020-10-20
1	2020-12-3
2	2020-10-5
2	2020-12-9
3	2020-11-11

结果表：

user_id	biggest_window
1	39
2	65
3	51

---

问题拆解+ 解法

求出每个用户的最大空档期： 首先先求出用户的每一次空档期, 最后找出每个用户的最大值

一、求每个用户的每次空档期：

1. 对于每个 user_id求空档期， 首先想到要对用户分组后计算，所以使用窗口函数，在窗口函数语句中的分组为partation by user_id
2. 注意到题目中的时间顺序时乱的， 所以首先将他的访问日期按升序排序，order by visit_date asc
3. 然后求出每个日期之间的空档期, 使用datediff求日期之间的差异： datediff(下一个访问日，访问日）
   datediff 用法复习 MYSQL时间日期函数
4. 下一个访问日用lead函数提取:
   lead(visit_date,1) over(partation by user_id order by visit_date asc)
   lead(x,y,z) over(), 其中x代表要寻找的列，y代表往后走几个位置，z代表找不到记录时的默认值。
   具体函数用法见《MYSQL lag() 和lead()函数使用介绍)》
5. 题目中还有一个条件 :”如果访问是最后一次，则位今天“ 即，没有后续日期时，设定最后一个日期为’2021-1-1’ 、
   lead(visit_date, 1, "2021-1-1") over（partation by user_id order by visit_date asc)

select user_id, 
datediff(lead(visit_date, 1, "2021-1-1") over（partation by user_id order by visit_date asc),
visit_date) as windows
from UserVisits

二、找出每个用户的最大值

在1的结果上，使用 group 和max， 根据user_id 找出最大值

select user_id, max(windows) as biggest_window
from (select user_id, 
      datediff(
          lead(visit_date, 1, "2021-01-01") over (partition by user_id order by visit_date asc)
          , visit_date
          ) as windows
from UserVisits) as temp
group by user_id;

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/biggest-window-between-visits