目录
一、力扣原题链接
二、题目描述
用户访问量表：
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 天。
三、建表语句
drop table if exists UserVisits;
Create table If Not Exists UserVisits(user_id int, visit_date date);
Truncate table UserVisits;
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');四、题目分析
第一步：排名（根据用户id分组，访问日期倒序排序）
第二步：条件判断（排名1的结束日期为2021-01-01，其他则为上一个访问日期）
第三步：计算日期差（结束日期 - 访问日期）空档期
第四步：根据用户id分组找最大值
五、SQL解答
-- 1、排名
select
distinct
*,
dense_rank() over (partition by user_id order by visit_date desc) as dr
from uservisits
;
-- 2、根据排名的条件获得结束日期
with t1 as (
select
distinct
*,
dense_rank() over (partition by user_id order by visit_date desc) as dr
from uservisits
)
select
user_id,
visit_date,
case
when dr = 1 then '2021-01-01'
else lag(visit_date) over (partition by user_id order by visit_date desc)
end as end_date
from t1
;
-- 3、结束日期 - 开始日期 等于 日期差
with t1 as (
select
distinct
*,
dense_rank() over (partition by user_id order by visit_date desc) as dr
from uservisits
),t2 as (
select
user_id,
visit_date,
case
when dr = 1 then '2021-01-01'
else lag(visit_date) over (partition by user_id order by visit_date desc)
end as end_date
from t1
)
select
user_id,visit_date,end_date,
datediff(end_date,visit_date) as diff
from t2
;
-- 4、根据用户ID分组，取最大的差值
with t1 as (
select
distinct
*,
dense_rank() over (partition by user_id order by visit_date desc) as dr
from uservisits
),t2 as (
select
user_id,
visit_date,
case
when dr = 1 then '2021-01-01'
else lag(visit_date) over (partition by user_id order by visit_date desc)
end as end_date
from t1
),t3 as (
select
user_id,visit_date,end_date,
datediff(end_date,visit_date) as diff
from t2
)
select
user_id,
max(diff) as biggest_window
from t3
group by user_id
order by user_id
;
六、最终答案
with t1 as (
select
distinct
*,
-- 1、排名
dense_rank() over (partition by user_id order by visit_date desc) as dr
from uservisits
),t2 as (
select
user_id,
visit_date,
-- 2、条件判断得出结束日期
case
when dr = 1 then '2021-01-01'
-- 上一行的值
else lag(visit_date) over (partition by user_id order by visit_date desc)
end as end_date
from t1
),t3 as (
select
user_id,visit_date,end_date,
-- 3、计算日期差（空窗期）
datediff(end_date,visit_date) as diff
from t2
)
select
user_id,
-- 4、分组求最大
max(diff) as biggest_window
from t3
group by user_id
order by user_id
;七、验证
八、知识点
标签：1709,visit,力扣,2020,user,SQL,date,id,select From： https://blog.csdn.net/qq_30900519/article/details/143179174
- distinct 去重
- dense_rank 排名
- case..when 条件判断
- lag 上一行的值
- max 最大值