时间序列数据的自联接 [英] Self join of time series data

问题描述

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

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)

这篇关于时间序列数据的自联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！