报告系统状态的连续日期

将连续日期的状态特性数值化

with log as (
    select row_number() over(order by log_date) as level,log_date,period_state,base_diff
    from (
    select  fail_date as log_date,'failed' as period_state,50 as base_diff from failed         
    union all
    select success_date as log_date,'succeeded' as period_state,100 as base_diff from Succeeded         
    ) b where log_date>='2019-01-01' and log_date<='2019-12-31'
),
res as (
    
        select a.level,a.period_state ,a.log_date 
        ,case when b.log_date is not null or c.log_date is not null then 100 + a.base_diff
              else a.base_diff end as diff
        from log a
        left join  log b on b.level = a.level-1 and b.period_state = a.period_state  
        left join  log c on c.level = a.level+1 and c.period_state = a.period_state  
),
bx as (
    select *,row_number() over(partition by diff order by level)-level as flag    
    from res
)
select period_state,min(log_date) as [start_date], max(log_date) as [end_date] 
from bx 
group by diff,flag,period_state 
order by [start_date];

　　

Table: Failed

+--------------+---------+
| Column Name | Type |
+--------------+---------+
| fail_date | date |
+--------------+---------+
该表主键为 fail_date。
该表包含失败任务的天数.
Table: Succeeded

+--------------+---------+
| Column Name | Type |
+--------------+---------+
| success_date | date |
+--------------+---------+
该表主键为 success_date。
该表包含成功任务的天数.
 

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

编写一个 SQL 查询 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。

最后结果按照起始日期 start_date 排序

查询结果样例如下所示:

Failed table:
+-------------------+
| fail_date |
+-------------------+
| 2018-12-28 |
| 2018-12-29 |
| 2019-01-04 |
| 2019-01-05 |
+-------------------+

Succeeded table:
+-------------------+
| success_date |
+-------------------+
| 2018-12-30 |
| 2018-12-31 |
| 2019-01-01 |
| 2019-01-02 |
| 2019-01-03 |
| 2019-01-06 |
+-------------------+

Result table:
+--------------+--------------+--------------+
| period_state | start date | end date |
+--------------+--------------+--------------+
| present | 2019-01-01 | 2019-01-03 |
| missing | 2019-01-04 | 2019-01-05 |
| present | 2019-01-06 | 2019-01-06 |
+--------------+--------------+--------------+

结果忽略了 2018 年的记录，因为我们只关心从 2019-01-01 到 2019-12-31 的记录
从 2019-01-01 到 2019-01-03 所有任务成功，系统状态为 "succeeded"。
从 2019-01-04 到 2019-01-05 所有任务失败，系统状态为 "failed"。
从 2019-01-06 到 2019-01-06 所有任务成功，系统状态为 "succeeded"。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/report-contiguous-dates
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。