主页 / dba / 问题 / 176575
Accepted
BanksySan
Asked: 2017-06-19 04:18:10 +0800 CST

获取没有“结束”事件的行

  • 772

我有一系列行代表开始 ( S) 或 ( E) 是一个thing

+----+-------+-------+
| Id | Event | Thing |
+----+-------+-------+
|  1 | S     | A     |
|  2 | E     | A     |
|  3 | S     | B     |
|  4 | E     | B     |
|  5 | S     | C     |
|  6 | S     | D     |
|  7 | S     | E     |
+----+-------+-------+

我正在尝试获取没有结束事件的行。

SELECT [a].[Id] AS [ID a],
       [a].[Event] AS [Event a],
       [b].[Id] AS [ID b],
       [b].[Event] AS [Event b]
  FROM [dbo].[TimeSeries] AS [a]
  LEFT OUTER JOIN [dbo].[TimeSeries] AS [b]
    ON [b].[Thing] = [a].[Thing]
   WHERE [a].[Event] = 'S'
   AND [b].[Event] = 'E'

这返回

+------+---------+------+---------+
| ID a | Event a | ID b | Event b |
+------+---------+------+---------+
|    1 | S       |    2 | E       |
|    3 | S       |    4 | E       |
+------+---------+------+---------+

这就是我被困的地方。

我想要所有既不在ID a列中也不在ID b列中的 ID。然后我猜想LEFT JOIN找到所有没有结束事件的行。

注意：真实的表是一个包含数百万行的 SSIS 日志。

t-sql

1 个回答

  1. Best Answer

    这里有几种方法。

    1. 用于NOT EXISTS 反半连接
    SELECT Id,
       Event,
       Thing
FROM   dbo.TimeSeries t1
WHERE  NOT EXISTS(SELECT *
                  FROM   dbo.TimeSeries t2
                  WHERE  t1.Thing = t2.Thing
                         AND t2.Event = 'E');
    1. 按事物分区并在每个分区中找到最小（按字母顺序排列）的事件。如果是S而不是E这个Thing则没有结束事件。
    WITH T
     AS (SELECT *,
                MIN(Event) OVER (PARTITION BY Thing) AS Evt
         FROM   dbo.TimeSeries
         WHERE  Event IN ( 'E', 'S' ))
SELECT Id,
       Event,
       Thing
FROM   T
WHERE  Evt = 'S';
    1. 按事物分区并用于LAG在按事件字母顺序排序时查看当前行的前一行。如果当前行是S并且没有前一行Evt将是NULL。只需保留任何此类行。这是 2012+ 语法。
    WITH T
     AS (SELECT *,
                LAG(Event) OVER (PARTITION BY Thing ORDER BY Event) AS Evt
         FROM   dbo.TimeSeries
         WHERE  Event IN ( 'E', 'S' ))
SELECT Id,
       Event,
       Thing
FROM   T
WHERE  Event = 'S' AND Evt IS NULL;
    • 5

相关问题