SQL连续问题思路
SQL处理连续问题
我们在刷SQL题时,有时候会遇到求连续天数或者连续座位等问题,其实连续的问题没有想象中那么困难,这里我总结了一下通用的连续问题解法
- 构造一列自增列
- 通过自增列与目标列进行差值运算
- 将差值分组,每一组都是单独的一组连续数据
- 根据需求对分组后的数据进行处理
- 常见的有group_concat函数和find_in_set函数结合使用拿到所有连续数据的完整信息
- 使用max等聚合函数对数据进行处理
以leetcode 1225.报告系统状态的连续日期为例
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"。
这里无论是失败还是成功的日期,我们都需要拿到连续的日期,所以我们可以分开拿到两种状态下的连续日期后,将两种结果合并在一起,然后对整合结果进行排序。
按照总结的通用步骤进行
-
首先我们构造一列自增列(注意!这里的目标列为date类型,所以自增列也必须为date类型才能进行差值比较),填充题中给出的日期范围条件
#这里使用了date_format函数保证自增列为date类型,然后使用date_add函数对日期模拟自增操作 select s.*,@tmp:=date_add(@tmp,interval 1 day) from Succeeded s, (select @tmp:=date_format('2019-01-01')) t where success_date >= '2019-01-01' and success_date <= '2019-12-31';
-
构建好自增列后,我们对自增列和目标列进行差值运算(日期的差值运算使用datediff函数),然后分组
select * from (select s.*,@tmp:=date_add(@tmp,interval 1 day) as tmp_date from Succeeded s, (select @tmp:=date_format('2019-01-01')) t where success_date >= '2019-01-01' and success_date <= '2019-12-31') a group by datediff(tmp_date,s.success_date);
-
分组后我们对按照结果集的要求对数据进行处理,这里需要拿到的是每一个连续段的起止日期,那么对应的就是每个连续段的max和min
select 'succeeded' as period_state,min(s.success_date) as start_date,max(s.success_date) as end_date from (select s.*,@tmp:=date_add(@tmp,interval 1 day) as tmp_date from Succeeded s, (select @tmp:=date_format('2019-01-01')) t where success_date >= '2019-01-01' and success_date <= '2019-12-31') a group by datediff(tmp_date,s.success_date);
-
到这里,Succeeded表就已经处理完毕,Failed同理
select 'failed' as period_state,min(s.fail_date) as start_date,max(s.fail_date) as end_date from (select s.*,@tmp:=date_add(@tmp,interval 1 day) as tmp_date from Failed s,(select @tmp:=date_format('2019-01-01')) t where fail_date >= '2019-01-01' and fail_date <= '2019-12-31') a group by datediff(tmp_date,s.fail_date);
-
整合两个结果后按照start_date进行排序
select * from ( select 'succeeded' as period_state,min(s.success_date) as start_date,max(s.success_date) as end_date from (select s.*,@tmp:=date_add(@tmp,interval 1 day) as tmp_date from Succeeded s, (select @tmp:=date_format('2019-01-01')) t where success_date >= '2019-01-01' and success_date <= '2019-12-31') a group by datediff(tmp_date,s.success_date) union all select 'failed' as period_state,min(s.fail_date) as start_date,max(s.fail_date) as end_date from (select s.*,@tmp:=date_add(@tmp,interval 1 day) as tmp_date from Failed s,(select @tmp:=date_format('2019-01-01')) t where fail_date >= '2019-01-01' and fail_date <= '2019-12-31') a group by datediff(tmp_date,s.fail_date) ) c order by start_date;
以上就是连续问题的通用思路展示,例子为日期类型,构造自增列更为复杂(涉及的函数较多),如果是数字的话sql会更简洁一些。