为了使事情更清晰,请查看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的一个合并时间,因为它们是重叠的或相邻的.
下面您将会找到我需要的示例数据和扁平化数据.持续时间栏仅供参考.
编辑：由于有很多不同而有趣的解决方案,我正在通过添加约束来改进我的原始问题,以查看“最好的”(如果有的话)解决方案泡沫：
>我从另一个系统通过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
解决方法
解决方案的一般要点是获得与任何其他跨度不重叠的所有开始时间,获得与任何跨度不重叠的所有结束时间,然后将两者匹配在一起.
结果符合您的预期结果,除了一种情况,手工检查看起来像您在预期输出中有错误. 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
