sql – 展平相交时间盘

发布时间：2020-12-12 16:11:03 所属栏目：MsSql教程来源：网络整理

导读：我有大量数据,具有给定ID的开始和停止时间,我需要将所有相交和相邻的时间段平坦化为一个组合的时间段.下面发布的示例数据都是相同的ID,所以我没有列出. 为了使事情更清晰,请查看03.06.2009的示例数据：以下时间盘是重叠的或有争议的,需要合并成一次 05:54:48

我有大量数据,具有给定ID的开始和停止时间,我需要将所有相交和相邻的时间段平坦化为一个组合的时间段.下面发布的示例数据都是相同的ID,所以我没有列出.

为了使事情更清晰,请查看03.06.2009的示例数据：

以下时间盘是重叠的或有争议的,需要合并成一次

> 05:54:48 – 10:00:13
> 09:26:45 – 09:59:40

最后的时间是05:54:48到10:00:13.由于在10:00:13和10:12:50之间有差距,我们也有以下时间段：

> 10:12:50 – 10:27:25
> 10:13:12 – 11:14:56
> 10:27:25 – 10:27:31
> 10:27:39 – 13:53:38
> 11:14:56 – 11:15:03
> 11:15:30 – 14:02:14
> 13:53:38 – 13:53:43
> 14:02:14 – 14:02:31

这导致从10:12:50到14:02:31的一个合并时间,因为它们是重叠的或相邻的.

下面您将会找到我需要的示例数据和扁平化数据.持续时间栏仅供参考.

任何解决方案 – 无论是SQL还是不支持 – 都不胜感激.

编辑：由于有很多不同而有趣的解决方案,我正在通过添加约束来改进我的原始问题,以查看“最好的”(如果有的话)解决方案泡沫：

>我从另一个系统通过ODBC获取数据.没有办法更改我的表布局或添加索引
>数据仅由日期列索引(时间部分不是)
>每天大约有2.5k行
>数据的估计使用模式大致如下：

大多数时间(让我们说90％)用户只会查询一两天(2.5k – 5k行)
>有时(9％)的范围将最多一个月(约75k行)
>极少(1％)范围将达一年(约900k行)

>对于典型情况,查询应该是快速的,而对于罕见的情况,查询不能“永远”.
>查询一年的数据需要大约5分钟(无需连接的简单选择)

在这些限制之内,什么是最好的解决方案？恐怕大多数解决方案将会非常缓慢,因为他们加入了日期和时间的组合,这在我的情况下不是一个索引领域.

你会在客户端还是服务器端进行所有的合并？您是否首先创建一个优化的临时表,并使用该表中的一个提出的解决方案？到现在为止,我没有时间去测试解决方案,但我会告诉你什么对我最有效.

样品数据：

Date       | Start    | Stop
-----------+----------+---------
02.06.2009 | 05:55:28 | 09:58:27
02.06.2009 | 10:15:19 | 13:58:24
02.06.2009 | 13:58:24 | 13:58:43
03.06.2009 | 05:54:48 | 10:00:13
03.06.2009 | 09:26:45 | 09:59:40
03.06.2009 | 10:12:50 | 10:27:25
03.06.2009 | 10:13:12 | 11:14:56
03.06.2009 | 10:27:25 | 10:27:31
03.06.2009 | 10:27:39 | 13:53:38
03.06.2009 | 11:14:56 | 11:15:03
03.06.2009 | 11:15:30 | 14:02:14
03.06.2009 | 13:53:38 | 13:53:43
03.06.2009 | 14:02:14 | 14:02:31
04.06.2009 | 05:48:27 | 09:58:59
04.06.2009 | 06:00:00 | 09:59:07
04.06.2009 | 10:15:52 | 13:54:52
04.06.2009 | 10:16:01 | 13:24:20
04.06.2009 | 13:24:20 | 13:24:24
04.06.2009 | 13:24:32 | 14:00:39
04.06.2009 | 13:54:52 | 13:54:58
04.06.2009 | 14:00:39 | 14:00:49
05.06.2009 | 05:53:58 | 09:59:12
05.06.2009 | 10:16:05 | 13:59:08
05.06.2009 | 13:59:08 | 13:59:16
06.06.2009 | 06:04:00 | 10:00:00
06.06.2009 | 10:16:54 | 10:18:40
06.06.2009 | 10:18:40 | 10:18:45
06.06.2009 | 10:23:00 | 13:57:00
06.06.2009 | 10:23:48 | 13:57:54
06.06.2009 | 13:57:21 | 13:57:38
06.06.2009 | 13:57:54 | 13:57:58
07.06.2009 | 21:59:30 | 01:58:49
07.06.2009 | 22:12:16 | 01:58:39
07.06.2009 | 22:12:25 | 01:58:28
08.06.2009 | 02:10:33 | 05:56:11
08.06.2009 | 02:10:43 | 05:56:23
08.06.2009 | 02:10:49 | 05:55:59
08.06.2009 | 05:55:59 | 05:56:01
08.06.2009 | 05:56:11 | 05:56:14
08.06.2009 | 05:56:23 | 05:56:27

平整结果：

Date       | Start    | Stop     | Duration
-----------+----------+----------+---------
02.06.2009 | 05:55:28 | 09:58:27 | 04:02:59
02.06.2009 | 10:15:19 | 13:58:43 | 03:43:24
03.06.2009 | 05:54:48 | 10:00:13 | 04:05:25
03.06.2009 | 10:12:50 | 14:02:31 | 03:49:41
04.06.2009 | 05:48:27 | 09:59:07 | 04:10:40
04.06.2009 | 10:15:52 | 14:00:49 | 03:44:58
05.06.2009 | 05:53:58 | 09:59:12 | 04:05:14
05.06.2009 | 10:16:05 | 13:59:16 | 03:43:11
06.06.2009 | 06:04:00 | 10:00:00 | 03:56:00
06.06.2009 | 10:16:54 | 10:18:45 | 00:01:51
06.06.2009 | 10:23:00 | 13:57:58 | 03:34:58
07.06.2009 | 21:59:30 | 01:58:49 | 03:59:19
08.06.2009 | 02:10:33 | 05:56:27 | 03:45:54

解决方法

这是一个仅SQL解决方案.我使用DATETIME列.分开存储时间是我的看法中的一个错误,因为当时间到了午夜的时候你会有问题.你可以调整这个来处理这种情况,如果你需要的话.该解决方案还假设开始和结束时间不为NULL.再次,如果不是这样,您可以根据需要进行调整.

解决方案的一般要点是获得与任何其他跨度不重叠的所有开始时间,获得与任何跨度不重叠的所有结束时间,然后将两者匹配在一起.

结果符合您的预期结果,除了一种情况,手工检查看起来像您在预期输出中有错误. 6日应该在2009-06-06 10：18：45.000结束.

SELECT
     ST.start_time,ET.end_time
FROM
(
     SELECT
          T1.start_time
     FROM
          dbo.Test_Time_Spans T1
     LEFT OUTER JOIN dbo.Test_Time_Spans T2 ON
          T2.start_time < T1.start_time AND
          T2.end_time >= T1.start_time
     WHERE
          T2.start_time IS NULL
) AS ST
INNER JOIN
(
     SELECT
          T3.end_time
     FROM
          dbo.Test_Time_Spans T3
     LEFT OUTER JOIN dbo.Test_Time_Spans T4 ON
          T4.end_time > T3.end_time AND
          T4.start_time <= T3.end_time
     WHERE
          T4.start_time IS NULL
) AS ET ON
     ET.end_time > ST.start_time
LEFT OUTER JOIN
(
     SELECT
          T5.end_time
     FROM
          dbo.Test_Time_Spans T5
     LEFT OUTER JOIN dbo.Test_Time_Spans T6 ON
          T6.end_time > T5.end_time AND
          T6.start_time <= T5.end_time
     WHERE
          T6.start_time IS NULL
) AS ET2 ON
     ET2.end_time > ST.start_time AND
     ET2.end_time < ET.end_time
WHERE
     ET2.end_time IS NULL

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!