MySQL1225-报告系统状态的连续日期

目录

题目

准备数据

分析数据

---

题目

系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。

编写解决方案找出 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')

failed表

 

succeeded表

分析数据

1- 使用union all实现列转行

select 'failed' as type, fail_date as date from Failed
union all
select 'succeeded' as type, success_date as date from Succeeded;

2-过滤出2019年的数据，求连续同状态

with t1 as (
    select 'failed' as type, fail_date as date from Failed
    union all
    select 'succeeded' as type, success_date as date from Succeeded
),t2 as (
    select type,date,
           row_number() over (partition by  type order by date) rn,
           subdate(date,row_number() over(partition by type order by date)) as diff
    from t1
    where substr(date,1,4) = '2019'
)select  *
from t2;

3-求start_date 和 end_date

with t1 as (
    select 'failed' as type, fail_date as date from Failed
    union all
    select 'succeeded' as type, success_date as date from Succeeded
),t2 as (
    select type,date,
           row_number() over (partition by  type order by date) rn,
           subdate(date,row_number() over(partition by type order by date)) as diff
    from t1
    where substr(date,1,4) = '2019'
)select  type as period_state, min(date) as start_date, max(date) as end_date
        from t2
group by type,diff
order by start_date;