时间序列数据的自联接

本文介绍了时间序列数据的自联接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要一些我认为应该相当简单的自我联接查询的帮助.它只需要将两个记录中匹配的开始时间和结束时间组合到一个记录中

I need some help with what I think should be a fairly simple self join query. It just needs to combine the matching start time and end times from two records into one record

说我在表格中有以下内容

Say I have the following in a table

Time Event 08:00 Start 09:00 Stop 10:30 Start 10:45 Stop 11:00 Start 11:15 Start 12:00 Stop 12:30 Stop

我想要这样的视图

StartTime Stoptime 08:00 09:00 10:30 10:45 11:00 11:15 12:00 12:30

请注意，它应该找到最匹配的开始或结束时间.如果由于某种原因没有匹配事件，则应将其留空.

Notice it should find the closest matching start or end time. If for some reason there is not a matching event it should leave it blank.

谢谢

推荐答案

免责声明:我会认真考虑使用其他表格设计.加入日期和时间从来都不是一个好主意.如果您有一个即将开始的事件的ID，则可以轻松地通过LEFT JOIN尝试找到该事件的匹配结尾.

disclaimer: I would seriously consider a different table design. Joining on dates and or times is never a good idea. If you had an ID of the event that is starting, you could easily LEFT JOIN to try to find the matching end of that event.

对于SQL Server，请尝试:

For SQL Server try:

DECLARE @Events table (EventTime char(5), EventType varchar(5)) INSERT INTO @Events VALUES ('08:00','Start') INSERT INTO @Events VALUES ('09:00','Stop') INSERT INTO @Events VALUES ('10:30','Start') INSERT INTO @Events VALUES ('10:45','Stop') INSERT INTO @Events VALUES ('11:00','Start') INSERT INTO @Events VALUES ('11:15','Start') INSERT INTO @Events VALUES ('12:00','Stop') INSERT INTO @Events VALUES ('12:30','Stop') SELECT dt.StartTime, dt.StopTime FROM (SELECT p.EventTime AS StartTime,CASE WHEN c.EventType!='Stop' THEN NULL ELSE c.EventTime END AS StopTime ,p.EventTime AS SortBy FROM @Events p INNER JOIN @Events c ON p.EventTime<c.EventTime WHERE p.EventType='Start' AND c.EventTime=(SELECT MIN(EventTime) FROM @Events WHERE EventTime>p.EventTime) UNION SELECT NULL AS StartTime,p.EventTime ,p.EventTime AS SortBy FROM @Events p INNER JOIN @Events c ON p.EventTime>c.EventTime WHERE p.EventType='STOP' AND c.EventTime=(SELECT MAX(EventTime) FROM @Events WHERE EventTime<p.EventTime) AND c.EventType='Stop' ) dt ORDER BY dt.SortBy

输出:

StartTime StopTime --------- -------- 08:00 09:00 10:30 10:45 11:00 NULL 11:15 12:00 NULL 12:30 (5 row(s) affected)