压扁相交时间跨度跨度、压扁、时间
我有很多与启动数据和停止时间对于给定的ID，我需要所有的交叉和邻近的时间跨度扁平合并为一个时间跨度。下面张贴的样本数据全部为相同的ID，所以我没有列出来。
为了让事情更清楚一点，看看为2009年3月6日的样本数据：
下面的时间跨度是重叠或contiunous，需要合并为一个时间跨度
在5时54分48秒 - 10时○○分13秒 在九点26分45秒 - 9点59分40秒由此产生的时间跨度是从五点54分48秒至10时00分十三秒。由于有十点00分13秒和10时12分50秒之间的差距，我们也有以下的时间跨度：
在10时12分50秒 - 10时27分25秒 在10时13分12秒 - 11点14分56秒 在10时27分25秒 - 十时27分31秒 在10点27分39秒 - 13时53分38秒 在11点14分56秒 - 11点十五分03秒 在11时15分三十秒 - 14时02分14秒 在13时53分38秒 - 13时53分43秒 在十四时02分14秒 - 十四时02分31秒这导致一个合并的时间跨度从10点十二分50秒至14时02分31秒，因为他们是重叠或邻近。
下面你会发现，样本数据和扁平的数据，因为我需要它。持续时间列只是信息。
任何解决方案 - 无论是SQL与否 - 是AP preciated
修改：由于有很多不同的，有趣的解决方案，我炼我原来的问题通过添加约束看到了最佳（如果存在的话）的解决方案泡了：
在我通过ODBC从其他系统获取数据。有没有办法改变表格的布局，我还是添加索引 的数据仅由日期列索引（部分时间是不是） 有大约2.5K行的每一天 中的数据的估计的使用模式是大致如下： 大多数时候（可以说90％）的用户将查询一个或两个天（2.5K - 5K行） 有时（9％）的范围内将长达一个月（〜75K行） 在很少（1％）的范围内将长达一年（〜900K行） 查询应为典型案例要快，而不是天长地久为罕见的情况。 查询一年有价值的数据大约需要5分钟（不加入纯选）在这些限制下，这将是最好的解决办法？恐怕大部分的解决方案将是自他们加入日期和时间的组合，这是不是在我的情况下，索引字段异常缓慢。
你会做客户端或服务器端上的所有合并？你会首先创建一个优化的临时表，并使用与该表所提出的解决方案之一？我没有足够的时间来测试解决方案，到现在为止，但我会及时通知您什么最适合我。
的样本数据：
日期|开始|停止
----------- + ---------- + ---------
02.06.2009 | 5时55分28秒| 9点58分27秒
02.06.2009 | 10时15分19秒| 13时58分24秒
02.06.2009 | 13时58分24秒| 13时58分43秒
2009年3月6日| 5时54分48秒| 10时00分13秒
2009年3月6日| 9时26分45秒|九点59分四十秒
2009年3月6日| 10时12分50秒| 10点27分25秒
2009年3月6日| 10时13分十二秒| 11时14分56秒
2009年3月6日| 10点27分25秒| 10时27分31秒
2009年3月6日| 10时27分39秒| 13时53分38秒
2009年3月6日| 11时14分56秒| 11点15分03秒
2009年3月6日| 11点15分三十秒| 14点02分14秒
2009年3月6日| 13时53分38秒| 13时53分43秒
2009年3月6日| 14点02分14秒|十四时02分31秒
04.06.2009 | 5时48分27秒| 9时58分59秒
04.06.2009 | 06:00:00 |九点59分07秒
04.06.2009 |十点十五分52秒| 13时54分52秒
04.06.2009 | 10时16分01秒| 13时24分二十○秒
04.06.2009 | 13时24分二十○秒| 13时24分24秒
04.06.2009 | 13时24分32秒| 14点零零分39秒
04.06.2009 | 13时54分52秒| 13点54分58秒
04.06.2009 | 14点零零分39秒| 14点00分49秒
2009.05.06 | 5时53分58秒| 9时59分12秒
2009.05.06 | 22:16:05 |十三时59分08秒
2009.05.06 |十三时59分08秒| 13点59分16秒
2009年6月6日|六时04分零零秒| 10:00:00
2009年6月6日| 10点16分54秒| 10时18分40秒
2009年6月6日| 10时18分40秒| 10时18分45秒
2009年6月6日| 10:23:00 | 13:57:00
2009年6月6日| 10点23分48秒| 13时57分54秒
2009年6月6日| 13时57分21秒| 13时57分38秒
2009年6月6日| 13时57分54秒| 13时57分58秒
2009年7月6日| 21点59分30秒| 1点58分49秒
2009年7月6日| 22点12分16秒|一点58分39秒
2009年7月6日| 22时12分25秒| 1时58分28秒
08.06.2009 |二点10分33秒| 5时56分11秒
08.06.2009 | 2时10分43秒|五时56分23秒
08.06.2009 | 2点10分49秒| 5时55分59秒
08.06.2009 | 5时55分59秒| 5点56分零一秒
08.06.2009 | 5时56分11秒| 5时56分14秒
08.06.2009 |五时56分23秒| 5点56分27秒
扁平化的结果：
日期|开始|停止|持续时间
----------- + ---------- + ---------- + ---------
02.06.2009 | 5时55分28秒| 9点58分27秒| 4时02分59秒
02.06.2009 | 10时15分19秒| 13时58分43秒| 3时43分24秒
2009年3月6日| 5时54分48秒| 10时00分13秒| 4时05分25秒
2009年3月6日| 10时12分50秒|十四时02分31秒| 3时49分41秒
04.06.2009 | 5时48分27秒|九点59分07秒| 4时10分40秒
04.06.2009 |十点十五分52秒| 14点00分49秒| 3时44分58秒
2009.05.06 | 5时53分58秒| 9时59分12秒| 4时05分十四秒
2009.05.06 | 22:16:05 | 13点59分16秒|三时43分11秒
2009年6月6日|六时04分零零秒| 10:00:00 | 3时56分00秒
2009年6月6日| 10点16分54秒| 10时18分45秒|零点01分51秒
2009年6月6日| 10:23:00 | 13时57分58秒| 3时34分58秒
2009年7月6日| 21点59分30秒| 1点58分49秒| 3点59分十九秒
08.06.2009 |二点10分33秒| 5点56分27秒| 3点45分54秒
解决方案
下面是一个SQL唯一的解决办法。我用DATETIME的列。存放时间单独在我看来是一个错误，因为你将有问题的时候时间走过去的午夜。您可以调整该但如果你需要处理这种情况。该解决方案还假定开始和结束时间是NOT NULL。同样，你可以根据需要，如果事实并非如此调整。
溶液的一般要旨是让所有的开始时间不与任何其它跨距重叠，得到所有的结束时间不与任何跨距重叠的，则匹配两个在一起。
结果符合您的预期效果，除了在一种情况下，该检查由专人看起来你有你的期望的输出是一个错误。 6日应该是在2009-06-06 10结束的跨度：18：45.000
SELECT
ST.start_time，
ET.end_time
从
（
选择
T1.start_time
从
dbo.Test_Time_Spans T1
LEFT OUTER JOIN dbo.Test_Time_Spans T2 ON
T2.start_time＆LT; T1.start_time和
T2.end_time＆GT; = T1.start_time
哪里
T2.start_time IS NULL
）AS ST
内部联接
（
选择
T3.end_time
从
dbo.Test_Time_Spans T3
LEFT OUTER JOIN dbo.Test_Time_Spans T4开
T4.end_time＆GT; T3.end_time和
T4.start_time＆LT; = T3.end_time
哪里
T4.start_time IS NULL
）AS ET开
ET.end_time＆GT; ST.start_time
LEFT OUTER JOIN
（
选择
T5.end_time
从
dbo.Test_Time_Spans T5
LEFT OUTER JOIN dbo.Test_Time_Spans T6 ON
T6.end_time＆GT; T5.end_time和
T6.start_time＆LT; = T5.end_time
哪里
T6.start_time IS NULL
）AS ET2 ON
ET2.end_time＆GT; ST.start_time和
ET2.end_time＆LT; ET.end_time
哪里
ET2.end_time IS NULL
I have lots of data with start and stop times for a given ID and I need to flatten all intersecting and adjacent timespans into one combined timespan. The sample data posted below is all for the same ID so I didn't list it.
To make things a bit clearer, take a look at the sample data for 03.06.2009:
The following timespans are overlapping or contiunous and need to merge into one timespan
05:54:48 - 10:00:13 09:26:45 - 09:59:40The resulting timespan would be from 05:54:48 to 10:00:13. Since there's a gap between 10:00:13 and 10:12:50 we also have the following timespans:
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:31which result in one merged timespan from 10:12:50 to 14:02:31, since they're overlapping or adjacent.
Below you will find the sample data and the flattened data as I would need it. The duration column is just informative.
Any solution - be it SQL or not - is appreciated.
EDIT: Since there are lots of different and interesting solutions I'm refining my original question by adding constraints to see the "best" (if there is one) solution bubble up:
I'm getting the data via ODBC from another system. There's no way to change the table layout for me or adding indexes The data is indexed only by the date column (the time part isn't) There are about 2.5k rows for every day The estimated usage pattern of the data is roughly as follows: Most of the time (lets say 90%) the user will query just one or two days (2.5k - 5k rows) Sometimes (9%) the range will be up to a month (~75k rows) Rarely (1%) the range will be up to a year (~900k rows) The query should be fast for the typical case and not "last forever" for the rare case. Querying a year worth of data takes about 5 minutes (plain select without joins)Within these constraints, what would be the best solution? I'm afraid that most of the solutions will be horribly slow since they join on the combination of date and time, which is not an index field in my case.
Would you do all the merging on the client or the server side? Would you first create an optimized temp table and use one of the proposed solutions with that table? I didn't have the time to test the solutions until now but I will keep you informed what works best for me.
Sample data:
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
Flattened result:
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
解决方案
Here is a SQL only solution. I used DATETIME for the columns. Storing the time separate is a mistake in my opinion, as you will have problems when the times go past midnight. You can adjust this to handle that situation though if you need to. The solution also assumes that the start and end times are NOT NULL. Again, you can adjust as needed if that's not the case.
The general gist of the solution is to get all of the start times that don't overlap with any other spans, get all of the end times that don't overlap with any spans, then match the two together.
The results match your expected results except in one case, which checking by hand looks like you have a mistake in your expected output. On the 6th there should be a span that ends at 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
