查询以查找紧跟在事件类型“1”之前发生的最后n个事件类型“2”

mmvthczy 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(314)

我有一长串从不同机器发送的事件（数百万）。
我需要获取在同一台计算机中，在事件类型“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

sql window-functions Date gaps-and-islands

来源：https://stackoverflow.com/questions/62414778/query-to-find-last-n-events-type-2-occurred-immediately-before-events-type-1

1条答案

按热度按时间

uz75evzq1#

我理解为一个缺口和岛屿的问题。对于每台机器，您希望保留所有“1”事件和“2”事件之前的n个事件。
一种方法是定义组，每个“1”事件的窗口和递增。然后可以枚举组内的记录 row_number() ，和过滤器。
这就回答了n=2的问题。

select CodeEvent, DateTime, Mech
from (
    select
        t.*,
        row_number() over(partition by Mach, grp order by DateTime desc) rn
    from (
        select 
            t.*,
            sum(case when CodeEvent = 1 then 1 else 0 end) over(partition by Mach order by DateTime desc) grp
        from @T t
        where CodeEvent in (1, 2)   
    ) t
) t
where rn <= 3
order by DateTime

你可以通过调节最后的过滤器来控制n rn .

赞(0）回复(0）举报 2021-07-26

我来回答

查询以查找紧跟在事件类型“1”之前发生的最后n个事件类型“2”

1条答案

相关问题

热门标签

最新问答