SQL-Server-2017:使用时间戳标准自加入表

Question

我有一个名为 events的表，看起来像这样：

            timestamp      | intvalue | hostname | attributes
    2019-03-13 14:43:05.437|    257   |  room04  | Success 000
    2019-03-13 14:43:05.317|    257   |  room03  | Success 000
    2019-03-13 14:43:03.450|   2049   |  room05  | Error 108
    2019-03-13 14:43:03.393|     0    |  room05  | TicketNumber=3
    2019-03-13 14:43:02.347|     0    |  room04  | TicketNumber=2
    2019-03-13 14:43:02.257|     0    |  room03  | TicketNumber=1

上面是包含数千行类似的表的表的样本。我将用几句话解释您在此表中看到的内容。timestamp列给出了每个事件发生的时间的日期和时间。在intvalue列中，257表示成功进入，2049表示错误，0表示票证提出请求。hostname给出了读取每张票的卡/票证读取器的名称，attributes列提供了一些详细信息，例如票证（1、2、3等）或错误类型（即108或109），以及如果事件成功。

在这种情况下，有一种模式说，如果票务请求输入并且在14:43:02.257之类的时间是有效的，那么成功条目的消息将写在数据库中（作为新事件）在6秒内最多最多（这意味着在票证阅读票证后，最多在14：49：02.257）。

。

如果票证未能输入，则在100毫秒的时间差后，错误消息将写在数据库中。

因此，在此示例中，我想做的就是创建一个像下面的表格

        timestamp      | intvalue | hostname |   result    |  ticketnumber
2019-03-13 14:43:05.437|    257   |  room04  | Success 000 | TicketNumber=2
2019-03-13 14:43:05.317|    257   |  room03  | Success 000 | TicketNumber=1
2019-03-13 14:43:03.450|   2049   |  room05  |  Error 108  | TicketNumber=3

您可以看到带有TicketNumber=3的票与结果Error 108匹配，因为如果您查看初始表，它们的时间差值小于100ms，其他两张票与各自的1-1匹配。结果，因为时间边距小于6秒（超过100ms）。您还可以注意到，主机名可以帮助匹配，与TicketNumber=3属性的行具有room05的hostname，就像下一行具有Error 108的属性。

。

我一直在尝试自我加入此表或与CTE一起加入。我已经使用了Cross Apply，并且还尝试了使用datediff的方法，但是我失败了，我被卡住了。是否有人可以帮助我并向我展示一种正确的结果的正确方法？非常感谢您的时间。

Answer 1

时间滞后似乎并没有真正有所不同，除非某种程度上可以与成功和失败消息交织在一起。假设没有中间事件的两个请求不会连续发生，则可以使用lag()：

select e.*
from (select timestamp, intvalue, hostname, attributes,
             lag(attributes) over (partition by hostname order by timestamp) as ticketnumber
      from event
     ) e
where intvalue > 0
order by timestamp

Answer 2

好吧...这是您根据提供的数据要求的结果。这只是如何编写自我联接以获取示例中结果的一个示例。我希望这能朝着正确的方向推动。

IF OBJECT_ID('tempdb..#t') IS NOT NULL
BEGIN
    DROP TABLE #t
END
CREATE TABLE #t
(
    [timestamp] DATETIME,
    intValue INT,
    hostName VARCHAR(50),
    attributes VARCHAR(50)
)
INSERT INTO #t([timestamp], intValue, hostName, attributes)
VALUES  ('2019-03-13 14:43:05.437', 257, 'room04', 'Success 000'),
        ('2019-03-13 14:43:05.317',257, 'room03','Success 000'),
        ('2019-03-13 14:43:03.450',2049, 'room05','Error 108'),
        ('2019-03-13 14:43:03.393',0, 'room05','TicketNumber=3'),
        ('2019-03-13 14:43:02.347',0, 'room04','TicketNumber=2'),
        ('2019-03-13 14:43:02.257',0, 'room03','TicketNumber=1')
SELECT x.[timestamp], x.intValue, x.hostName, x.attributes result, y.attributes 
ticketnumber
FROM (SELECT * FROM #t WHERE intValue > 0) AS x 
INNER JOIN #t y
ON x.hostName = y.hostName AND y.intValue = 0
GROUP BY x.[timestamp], x.intValue, x.hostName, x.attributes, y.attributes
ORDER BY x.[timestamp] DESC

我不会尝试将其复制到您的项目中并使用它，这只是如何使用加入的一个示例。在发布完整的解决方案之前，我将需要更多有关您要完成的信息的更多信息，因为有很多更好的方法来制作大型数据集的报告。 - 比尔

Answer 3

由于您使用的是SQL 2017，因此可以使用铅/滞后。

with evt(timestamp,intvalue,hostname,attributes) as 
(
    select cast('2019-03-13 14:43:05.437' as datetime),   257 , 'room04','Success 000' union all
    select cast('2019-03-13 14:43:05.317' as datetime),   257 , 'room03','Success 000' union all
    select cast('2019-03-13 14:43:03.450' as datetime),  2049 , 'room05','Error 108' union all
    select cast('2019-03-13 14:43:03.393' as datetime),    0  , 'room05','TicketNumber=3' union all
    select cast('2019-03-13 14:43:02.347' as datetime),    0  , 'room04','TicketNumber=2' union all
    select cast('2019-03-13 14:43:02.257' as datetime),    0  , 'room03','TicketNumber=1'
    )
select [timestamp], intvalue, hostname, attributes, lag(attributes) over (partition by hostname order by timestamp) ticketnumber, datediff(ss,lag([timestamp]) over (partition by hostname order by timestamp), [timestamp]) lapse
from evt
order by timestamp