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

今日真题

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

难度困难

SQL架构

Table:

代码语言：javascript复制

Failed

代码语言：javascript复制

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

Table:

代码语言：javascript复制

Succeeded

代码语言：javascript复制

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

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

编写一个 SQL 查询 2019-01-01 到 2019-12-31 期间任务连续同状态

代码语言：javascript复制

period_state

的起止日期（

代码语言：javascript复制

start_date

和

代码语言：javascript复制

end_date

）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。

最后结果按照起始日期

代码语言：javascript复制

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

开窗函数

代码语言：javascript复制

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

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

sql date 架构 排序 系统

0 人点赞