# 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. 先把两张表合成一张表

```sql
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区分开

```sql
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

```sql
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;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://zqt0.gitbook.io/leetcode/sql/1225.report-contiguous-dates.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.