自联接以从事件表中提取相关记录

Question

我有一个包含以下字段的表

Time                 |     Status
2016-01-21 16:11:00  |     Connected
2016-01-21 16:51:00  |     Disconnected
2016-01-21 17:01:00  |     Connected
2016-01-21 17:10:00  |     Status X
2016-01-21 17:20:00  |     Status Y
2016-01-21 17:25:00  |     Disconnected
2016-01-21 17:30:00  |     Connected
2016-01-21 17:32:00  |     Disconnected

我希望输出像这个

Disconnected                     Connected
2016-01-21 16:51:00     |         2016-01-21 17:01:00
2016-01-21 17:25:00     |         2016-01-21 17:30:00
2016-01-21 17:32:00     |

等等。也就是说，我想显示服务器断开连接时间的数据。

我写了一个类似的查询

select B.Time, A.Time
from ( select Time
       from table
       where Status = 'Connected') as A, table B
where B.Status = 'Disconnected'
and B.Time < A.Time;

我的输出是

Disconnected                     Connected
2016-01-21 16:51:00              2016-01-21 17:01:00
2016-01-21 16:51:00              2016-01-21 17:30:00     

也就是说，对于每个断开连接的事件，我得到的所有连接的事件都大于它。但是，我只希望每行的第一个连接的事件大于断开连接事件。我能做什么？

Answer 1

一个很好的方法是创建一个包含所有时间逻辑的更复杂的ON子句。(http://sqlfiddle.com/#！9/81f61/5/0）

select dis.Time as disconnected, con.Time as connected
  from ev dis
  join ev con  ON con.Status = 'connected' 
              AND dis.Status = 'disconnected'
              AND con.Time = (select MIN(Time) 
                                 from ev
                                 where Status = 'connected'
                                   and Time > dis.Time)

这限制了每个连接的行仅考虑晚于每个断开连接时间的第一个MIN()连接时间。

如果您有大量这样的事件记录，请考虑在(Status, Time)上放置索引以优化此查询。

Answer 2

您可以通过断开连接时间对输出进行分组，并使用min来获得最早的：

SELECT B.Time,
       min(A.Time)
FROM
    (SELECT TIME
     FROM TABLE
     WHERE Status = 'Connected') AS A,
     TABLE B
WHERE B.Status = 'Disconnected'
    AND B.Time < A.Time
GROUP BY 1
;

或不带子查询：

SELECT tab1.`time`,
       min(tab2.`time`)
FROM tab1 AS tab1
LEFT JOIN tab1 AS tab2 ON tab2.time > tab1.time
WHERE tab1.Status = 'Disconnected'
GROUP BY 1
;

Answer 3

我以前使用过这个查询，它似乎在中运行良好

SELECT B.Time, A.Time
FROM table
WHERE NOT EXISTS (
    SELECT time
    WHERE b.Time = a.Time
    AND b.Disconnected > a.Connected
)