「SQL面试题库」 No_82 报告系统状态的连续日期

今日真题

题目介绍： 报告系统状态的连续日期 report-contiguous-dates

难度困难

SQL架构

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"。 ```

开窗函数

sql
select type  period_state, min(date) start_date, max(date) as end_date
from
(
    select type, date, subdate(date,row_number()over(partition by type order by date)) as diff
    from
    (
        select 'failed' as type, fail_date as date from Failed
        where fail_date between '2019-01-01' and '2019-12-31'
        union all
        select 'succeeded' as type, success_date as date from Succeeded
        where success_date between '2019-01-01' and '2019-12-31'
    ) a
)b
group by type,diff
order by start_date

• 已经有灵感了？在评论区写下你的思路吧！