- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
目录
一,原题力扣链接
二,题干
表:
UserVisits
+-------------+------+ | Column Name | Type | +-------------+------+ | user_id | int | | visit_date | date | +-------------+------+ 该表没有主键,它可能有重复的行 该表包含用户访问某特定零售商的日期日志。假设今天的日期是
'2021-1-1'
。编写解决方案,对于每个
user_id
,求出每次访问及其下一个访问(若该次访问是最后一次,则为今天)之间最大的空档期天数window
。返回结果表,按用户编号
user_id
排序。结果格式如下示例所示:
示例 1:
输入: 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 天。
三,建表语句
四,分析
题解:
表:用户登录表
字段: 用户id,登录日期
求用户登录日期中最大的空窗期 如果是最后一次登录就距离2021-01-01的日期
第一步:去重
第二步:新增加一列,并且用lag开窗 最后一次登录的日期
第三步:if判断 如果lo是空 则用 2021-01-01 减去登录日期 反之则用最后一次登录日期-登录日期
计算时间差
第四步:分组求top1
最后一步:拿到rn=1的行 并且映射指定的列
五,SQL解答
with t0 as (
select distinct * from uservisits #去重
),t1 as (
select
user_id, visit_date,
'2021-01-01' as date1,
lag(visit_date) over(partition by user_id order by visit_date desc) lo
from t0
),t2 as (
select user_id, visit_date, date1, lo,
if(lo is null,TIMESTAMPDIFF(DAY,visit_date,date1),TIMESTAMPDIFF(DAY,visit_date,lo)) diff
from t1
),t3 as (
select user_id, visit_date, date1, lo, diff,
row_number() over (partition by user_id order by diff desc ) ro
from t2
),t4 as (
select user_id,diff as biggest_window from t3 where ro = 1
)
select * from t4;
六,验证
七,知识点总结
- if判断函数的运用
- 时间差函数的运用
- 分组求top1的运用
- 原来网站上一次登录 多少天没回来 是这么算出来呀
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用