我有一长串从不同机器发送的事件(数百万)。
我需要获取在同一台计算机中,在事件类型“1”之前发生的最后n个“2”类型的事件
我找到了n=1的解,但我找不到n个事件的解
这是我对n=1的解:
DECLARE @T AS TABLE
(
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[CodeEvent] [int] NOT NULL,
[DateTime] [datetime2](7) NULL,
[Mach] [varchar](max) NULL
)
INSERT @T VALUES
(2,'12:35:28','M2'),
(1,'12:35:21','M3'),
(4,'12:34:54','M3'),
(8,'12:34:35','M2'),
(2,'12:33:28','M2'),
(2,'12:33:21','M3'),
(1,'12:32:54','M1'),
(8,'12:32:35','M2'),
(1,'12:32:28','M2'),
(2,'12:32:25','M1'),
(1,'12:32:23','M3'),
(3,'12:32:21','M2'),
(2,'12:31:28','M2'),
(2,'12:31:21','M1'),
(4,'12:30:54','M3'),
(8,'12:30:35','M2'),
(1,'12:30:28','M2'),
(2,'12:30:25','M1'),
(2,'12:30:23','M3'),
(2,'12:30:21','M2'),
(1,'12:30:20','M3')
SELECT * FROM @T ORDER BY DateTime Desc;
WITH
---- T1 All Events
T1 AS (SELECT TOP 100 *
FROM @T
ORDER BY DateTime desc)
,
---- T2 Events '1'
T2 AS (SELECT TOP 100 *
FROM T1 AS T
WHERE (T.[CodeEvent] = 1)
ORDER BY DateTime desc )
,
---- T4 Last Events type '2' Before type '1' same Mach
T4 AS
(select
T.ID,
T.DateTime,
T.[CodeEvent],
T.[Mach],
(SELECT Top 1 T1.ID
FROM T1
WHERE ((T1.Datetime < T.Datetime)
AND (T1.[Mach]=T.[Mach])
AND (T1.CodeEvent=2))
ORDER BY T1.DateTime desc) AS LAST_EVENT_ID
FROM T2 AS T)
,
--- T6 get Event from ID
T6 AS (SELECT
T.[ID]
,T.[CodeEvent]
,T.[DateTime]
,T.[Mach]
FROM T4 JOIN T1 AS T
ON (T4.LAST_EVENT_ID = T.ID))
SELECT * FROM T2
UNION ALL
SELECT * FROM T6
ORDER BY [Mach],DateTime Desc
这就是我得到的n=1
1 1900-01-01 12:32:54.0000000 M1
2 1900-01-01 12:32:25.0000000 M1
1 1900-01-01 12:32:28.0000000 M2
2 1900-01-01 12:31:28.0000000 M2
1 1900-01-01 12:30:28.0000000 M2
2 1900-01-01 12:30:21.0000000 M2
1 1900-01-01 12:35:21.0000000 M3
2 1900-01-01 12:33:21.0000000 M3
1 1900-01-01 12:32:23.0000000 M3
2 1900-01-01 12:30:23.0000000 M3
这就是我想要得到的n=2
1 1900-01-01 12:32:54.0000000 M1
2 1900-01-01 12:32:25.0000000 M1
2 1900-01-01 12:31:21.0000000 M1
1 1900-01-01 12:32:28.0000000 M2
2 1900-01-01 12:31:28.0000000 M2
2 1900-01-01 12:30:33.0000000 M2
1 1900-01-01 12:35:21.0000000 M3
2 1900-01-01 12:33:21.0000000 M3
2 1900-01-01 12:30:23.0000000 M3
1条答案
按热度按时间uz75evzq1#
我理解为一个缺口和岛屿的问题。对于每台机器,您希望保留所有“1”事件和“2”事件之前的n个事件。
一种方法是定义组,每个“1”事件的窗口和递增。然后可以枚举组内的记录
row_number()
,和过滤器。这就回答了n=2的问题。
你可以通过调节最后的过滤器来控制n
rn
.