SQL连续问题思路

最新推荐文章于 2024-05-28 21:51:56 发布

芮芮子天下第一

最新推荐文章于 2024-05-28 21:51:56 发布

阅读量1.2k

点赞数 1

分类专栏： DBA的自我修养文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/t384061961/article/details/128782421

版权

DBA的自我修养专栏收录该内容

8 篇文章 0 订阅

订阅专栏

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会更简洁一些。