对于一个给定的ID,我有很多具有开始和停止时间的数据,我需要将所有相交和相邻的时间跨度展平为一个组合的时间跨度。下面发布的示例数据都是同一ID的,所以我没有列出它。
为了让事情更清楚一点,请看一下2009年6月3日的样本数据:
以下时间跨度重叠或连续,需要合并为一个时间跨度
-
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.5公里的路程
-
数据的估计使用模式大致如下:
-
大多数时候(假设90%)用户只查询一到两天(2.5K-5K行)
-
有时(9%)范围最长可达一个月(~75K行)
-
很少(1%)的范围将长达一年(约90万行)
-
对于典型的情况,查询应该很快,而对于罕见的情况,查询不应该“永远持续”。
-
查询一年的数据大约需要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
