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***) 注意 加重的地方 不要忘记写