题目描述
(通过次数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;
本站所有内容均为原创,本站保留所有权利。仅允许非商业用途的转载,但必须注明来源网站、作者、来源链接!否则,由此造成的一切后果,由转载方承担!
干货分享、技术提升、面试笔试、学习交流,欢迎关注公众号:xuesql。QQ学习交流群:209942678。