1225.(Hard)报告系统状态的连续日期

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     |
+--------------+--------------+--------------+
| succeeded    | 2019-01-01   | 2019-01-03   |
| failed       | 2019-01-04   | 2019-01-05   |
| succeeded    | 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

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

Solution

1. 先把两张表合成一张表

select fail_date date,'failed' state from Failed
union all
select success_date date,'succeeded' state from Succeeded

2018-12-28    failed
2018-12-29    failed
2019-01-04    failed
2019-01-05    failed
2018-12-30    succeeded
2018-12-31    succeeded
2019-01-01    succeeded
2019-01-02    succeeded
2019-01-03    succeeded
2019-01-06    succeeded

1. 通过 partition rank和date的时间差 把每一轮fail和success区分开

SELECT *, 
    SUBDATE(date,INTERVAL RANK() over (PARTITION by state ORDER BY date) day) ref
from 
    (select fail_date date,'failed' state from Failed
    union all
    select success_date date,'succeeded' state from Succeeded) a
WHERE date BETWEEN  '2019-01-01' and '2019-12-31')

2019-01-04    failed    2019-01-03
2019-01-05    failed    2019-01-03
2019-01-01    succeeded    2018-12-31
2019-01-02    succeeded    2018-12-31
2019-01-03    succeeded    2018-12-31
2019-01-06    succeeded    2019-01-02

1. 最后用state和ref分组，组内时间最小和最大分别是start，end

SELECT state period_state, 
    min(date) start_date, 
    max(date) end_date
from
    (SELECT *, SUBDATE(date,INTERVAL RANK() over (PARTITION by state ORDER BY date) day) ref
    from 
        (select fail_date date,'failed' state from Failed
        union all
        select success_date date,'succeeded' state from Succeeded
        ORDER BY date) a
    WHERE date BETWEEN  '2019-01-01' and '2019-12-31') b
GROUP BY state, ref
ORDER BY start_date;