域名申请哪个网站好,北京做网站推广seo,万网注册域名查询,wordpress弹幕播放器力扣题
1、题目地址
1225. 报告系统状态的连续日期
2、模拟表
表#xff1a;Failed
Column NameTypefail_datedate
该表主键为 fail_date (具有唯一值的列)。该表包含失败任务的天数.
表#xff1a; Succeeded
Column NameTypesuccess_datedate
该表主键为 success_…力扣题
1、题目地址
1225. 报告系统状态的连续日期
2、模拟表
表Failed
Column NameTypefail_datedate
该表主键为 fail_date (具有唯一值的列)。该表包含失败任务的天数.
表 Succeeded
Column NameTypesuccess_datedate
该表主键为 success_date (具有唯一值的列)。该表包含成功任务的天数.
3、要求
系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。
编写解决方案找出 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期start_date 和 end_date。
即如果任务失败了就是失败状态的起止日期如果任务成功了就是成功状态的起止日期。
最后结果按照起始日期 start_date 排序。
4、示例
输入 Failed 表
fail_date2018-12-282018-12-292019-01-042019-01-05
Succeeded 表
success_date2018-12-302018-12-312019-01-012019-01-022019-01-032019-01-06
输出
period_statestart_dateend_datesucceeded2019-01-012019-01-03failed2019-01-042019-01-05succeeded2019-01-062019-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”。
5、代码编写
网友写法
SELECT state AS period_state, MIN(date) AS start_date, MAX(date) AS end_date
FROM (SELECT *,row_number() over (partition by state order by date asc) AS rk1,row_number() over (order by date asc) AS rk2FROM (SELECT fail_date AS date, failed AS state FROM failedUNION ALLSELECT success_date, succeeded FROM succeeded) t
) t2
WHERE date BETWEEN 2019-01-01 AND 2019-12-31
GROUP BY state, rk2-rk1代码分析
第一步将两个表数据查询出来并标上状态方便后面用窗口函数处理
SELECT fail_date AS date, failed AS state FROM failed
UNION ALL
SELECT success_date, succeeded FROM succeededdatestate2018-12-28failed2018-12-29failed2019-01-04failed2019-01-05failed2018-12-30succeeded2018-12-31succeeded2019-01-01succeeded2019-01-02succeeded2019-01-03succeeded2019-01-06succeeded
第二步第一个 row_number 根据状态的不同分组后按日期正序进行排序处理第二个 row_number 很明显是按日期正序后面使用
SELECT *,row_number() over (partition by state order by date asc) AS rk1,row_number() over (order by date asc) AS rk2
FROM (SELECT fail_date AS date, failed AS state FROM failedUNION ALLSELECT success_date, succeeded FROM succeeded
) tdatestaterk1rk22018-12-28failed112018-12-29failed222018-12-30succeeded132018-12-31succeeded242019-01-01succeeded352019-01-02succeeded462019-01-03succeeded572019-01-04failed382019-01-05failed492019-01-06succeeded610
第三步首先根据状态分组能做状态区分第二点是关键rk2-rk1因为rk2是连续的所以很清楚就能知道 rk2-rk1 如果是相等的那必定是连续的但是可能会碰到 state 不同rk2-rk1 相同的情况前面先 group by state 就是首先排除这种情况
SELECT state AS period_state, MIN(date) AS start_date, MAX(date) AS end_date
FROM (SELECT *,row_number() over (partition by state order by date asc) AS rk1,row_number() over (order by date asc) AS rk2FROM (SELECT fail_date AS date, failed AS state FROM failedUNION ALLSELECT success_date, succeeded FROM succeeded) t
) t2
WHERE date BETWEEN 2019-01-01 AND 2019-12-31
GROUP BY state, rk2-rk1period_statestart_dateend_datesucceeded2019-01-012019-01-03failed2019-01-042019-01-05succeeded2019-01-062019-01-06
