题目
假设今天的日期是 ‘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 |
问题拆解+ 解法
求出每个用户的最大空档期: 首先先求出用户的每一次空档期, 最后找出每个用户的最大值
一、求每个用户的每次空档期:
- 对于每个 user_id求空档期, 首先想到要对用户分组后计算,所以使用窗口函数,在窗口函数语句中的分组为
partation by user_id
- 注意到题目中的时间顺序时乱的, 所以首先将他的访问日期按升序排序,
order by visit_date asc
- 然后求出每个日期之间的空档期, 使用datediff求日期之间的差异:
datediff(下一个访问日,访问日)
datediff 用法复习 MYSQL时间日期函数 - 下一个访问日用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()函数使用介绍)》 - 题目中还有一个条件 :”如果访问是最后一次,则位今天“ 即,没有后续日期时,设定最后一个日期为’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