题目描述

(通过次数3,983 | 提交次数5,518,通过率72.18%)

表:UserVisits
+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| visit_date  | date |
+-------------+------+
该表没有主键。
该表包含用户访问某特定零售商的日期日志。

假设今天的日期是'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            |
+---------+---------------+
对于第一个用户,问题中的空档期在以下日期之间:
    - 2020-10-20 至 2020-11-28 ,共计 39 天。
    - 2020-11-28 至 2020-12-3 ,共计 5 天。
    - 2020-12-3 至 2021-1-1 ,共计 29 天。
由此得出,最大的空档期为 39 天。
对于第二个用户,问题中的空档期在以下日期之间:
    - 2020-10-5 至 2020-12-9 ,共计 65 天。
    - 2020-12-9 至 2021-1-1 ,共计 23 天。
由此得出,最大的空档期为 65 天。
对于第三个用户,问题中的唯一空档期在 2020-11-11 至 2021-1-1 之间,共计 51 天。

来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/biggest-window-between-visits
//测试数据
Create table If Not Exists UserVisits(user_id int, visit_date date);

insert into UserVisits (user_id, visit_date) values ('1', '2020-11-28');
insert into UserVisits (user_id, visit_date) values ('1', '2020-10-20');
insert into UserVisits (user_id, visit_date) values ('1', '2020-12-3');
insert into UserVisits (user_id, visit_date) values ('2', '2020-10-5');
insert into UserVisits (user_id, visit_date) values ('2', '2020-12-9');
insert into UserVisits (user_id, visit_date) values ('3', '2020-11-11');

解题思路

本题要求计算出一串日期中,间隔时间最大的天数。

那么,我们的思路可能是:

1、取出每一个日期的上一个日期或者下一个日期;

2、两个日期相减得出天数;

3、再取个最大值就可以了。

后两步都简单,难点在于第一步。

对于一个日期,它的下一个日期,必定是比它大的所有日期里的最小日期;

同样的,它的上一个日期,必定是比它小的所有日期里的最大日期;

比如,可以使用如下SQL取出每一个日期的下一个日期。

select
        a.user_id,
        a.visit_date,
        min(coalesce(b.visit_date,'2021-01-01')) next_visit_date
from UserVisits a
left join UserVisits b
on a.user_id = b.user_id
and a.visit_date < b.visit_date
group by a.user_id,a.visit_date;

不过,强哥今天介绍另一种方法,使用分析函数:lead。

**分析函数lead的作用是返回统计窗口内向下的第n个值**。如果第n个值不存在,还支持设置默认值。

使用语法为:**lead(column_name,n,default_value)**。

**column_name**:要取值的字段;

**n**:向下取的值的序号;

**default_value**:默认取值;

那么,对于取每个日期的下一个日期,可以使用如下SQL:

SELECT
    a.user_id,
    a.visit_date,
    lead(a.visit_date, 1, '2021-01-01') over (partition by a.user_id order by a.visit_date) as lead_visit_date
FROM UserVisits a;

参考SQL

未特别说明的情况下,参考SQL为基于MySQL8.0实现。
SELECT
    b.user_id,
    max(datediff(b.lead_visit_date, b.visit_date)) as biggest_window
FROM (
    SELECT
        a.user_id,
        a.visit_date,
        lead(a.visit_date, 1, '2021-01-01') over (partition by a.user_id order by a.visit_date) as lead_visit_date
    FROM UserVisits a
) b
GROUP BY b.user_id
ORDER BY b.user_id;
picture loss