一、力扣链接
LeetCode_1225
二、题目描述
表:Failed
+--------------+---------+ | Column Name | Type | +--------------+---------+ | fail_date | date | +--------------+---------+ 该表主键为 fail_date (具有唯一值的列)。 该表包含失败任务的天数.
表: Succeeded
+--------------+---------+ | Column Name | Type | +--------------+---------+ | success_date | date | +--------------+---------+ 该表主键为 success_date (具有唯一值的列)。 该表包含成功任务的天数.
系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。
编写解决方案找出 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state
的起止日期(start_date
和 end_date
)。即如果任务失败了,就是失败状态的起止日期,如果任务成功了,就是成功状态的起止日期。
最后结果按照起始日期 start_date
排序
三、目标拆解
四、建表语句
Create table If Not Exists Failed (fail_date date)
Create table If Not Exists Succeeded (success_date date)
Truncate table Failed
insert into Failed (fail_date) values ('2018-12-28')
insert into Failed (fail_date) values ('2018-12-29')
insert into Failed (fail_date) values ('2019-01-04')
insert into Failed (fail_date) values ('2019-01-05')
Truncate table Succeeded
insert into Succeeded (success_date) values ('2018-12-30')
insert into Succeeded (success_date) values ('2018-12-31')
insert into Succeeded (success_date) values ('2019-01-01')
insert into Succeeded (success_date) values ('2019-01-02')
insert into Succeeded (success_date) values ('2019-01-03')
insert into Succeeded (success_date) values ('2019-01-06')
五、过程分析
1、过滤日期
2、拼接两个状态的数据
3、差值相等的即为连续
4、计算最小/最大值即为起始/结束日期
六、代码实现
with t1 as(
select fail_date date, 'failed' period_state
from Failed
where fail_date between '2019-01-01' and '2019-12-31'
union all
select success_date, 'succeeded'
from Succeeded
where success_date between '2019-01-01' and '2019-12-31'
)
,t2 as (
select period_state,
date,
row_number() over (order by date) rn1,
row_number() over (partition by period_state order by date) rn2,
row_number() over (order by date) - row_number() over (partition by period_state order by date) diff
-- 用date - rn2求diff,会造成一年被分成12个月的连续区间
from t1
)
# select * from t2;
select period_state,
min(date) start_date,
max(date) end_date
from t2
group by period_state, diff
order by start_date;
七、结果验证
八、小结
1、与1285 为同一类型题目
2、过滤 -> 新增一列 -> 拼接 -> 排名函数 -> 分组 -> 求最小/大值