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

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

(select
'failed' period_state,min(fail_date) start_date,max(fail_date) end_date
 
from
 
(
select
fail_date,date_sub(fail_date,interval row_number() over(order by fail_date) day) ds
from
Failed
where
fail_date >='2019-01-01' and fail_date <='2019-12-31'
) s1
group by
s1.ds
 
union all
 
select
'succeeded' period_state,min(success_date) start_date,max(success_date) end_date
 
from
 
(
select
success_date,date_sub(success_date,interval row_number() over(order by success_date) day) ds
from
Succeeded
where
success_date >='2019-01-01' and success_date <='2019-12-31'
) s1
group by
s1.ds
)
order by start_date

笔记:

order by 对 select 中的 内容 排序

1. from (这里也包括from中的子语句)
2. join
3. on
4. where
5. group by(开始使用select中的别名，后面的语句中都可以使用；但是不能使用ROW_NUMBER()等窗口函数的别名)
6. avg,sum.... 等聚合函数
7. having
8. select
9. distinct
10. order by
11. limit

if 嵌套 例如
if(host_goals<guest_goals  ,0  , if(host_goals>guest_goals,3,1))

标号 作差 值 一样 则为 连续

union all 拼接完 上下 两个表 加括号以后 可以直接在最后 用 order by 进行排序

date_sub(fail_date,***interval*** row_number() over(order by fail_date) ***day***)   注意 加重的地方 不要忘记写