搜索事件和紧接在前面的事件

Question

以下是表格的布局方式(仅显示相关行(：

id      timestamp                     eventno
A       2020-01-21 13:02:00.000       300010
B       2020-01-21 13:02:45.000       300010
E       2020-01-21 13:02:50.000       300010
B       2020-01-21 13:02:90.000       300020
M       2020-01-21 13:03:56.000       300010

我想提取事件 id 300010 的所有出现次数，紧跟在事件 id 300020 之后 - 问题是我的表有 800 个不同的 id，我需要它来查找同一 id 的前一个事件。

所以我的结果看起来像这样：

B       2020-01-21 13:02:45.000       300010
B       2020-01-21 13:02:90.000       300020

我非常感谢任何帮助。

Answer 1

SELECT id, timestamp, eventno
FROM (SELECT id, 
timestamp, 
eventno,
LAG(eventno) OVER (
PARTITION BY id 
ORDER BY timestamp) previous_eventno,
LEAD(eventno) OVER (
PARTITION BY id 
ORDER BY timestamp) next_eventno
FROM table
) AS x
WHERE (x.eventno = 300020 and x.previous_eventno = 300010)
OR (x.eventno = 300010 and x.next_eventno = 300020);

Answer 2

你可以这样处理它： 对于相同的 id，给我事件 300010，事件 300020 之后，但两者之间没有事件

WITH CTE AS
(                          
SELECT 'A'   AS id,    '2020-01-21 13:02:00.000'  AS timestamp,     300010 AS eventno
UNION SELECT'B',       '2020-01-21 13:02:45.000',       300010
UNION SELECT'E',       '2020-01-21 13:02:50.000',       300010
UNION SELECT'B',       '2020-01-21 13:02:90.000',       300020
UNION SELECT 'M',      '2020-01-21 13:03:56.000',      300010
)

select *
from CTE a
inner join CTE b
on a.eventno = 300010 and b.eventno = 300020 and a.timestamp < b.timestamp and a.id = b.id                   
and not exists (
select 1
from CTE c
where c.timestamp > a.timestamp
and c.timestamp < b.timestamp and c.id = b.id
)

这个返回

id  timestamp   eventno id  timestamp   eventno
B   2020-01-21 13:02:45.000 300010  B   2020-01-21 13:02:90.000 300020

如果您希望将一行放在另一行下面：

WITH CTE AS
(                          
SELECT 'A'   AS id,    '2020-01-21 13:02:00.000'  AS timestamp,     300010 AS eventno
UNION SELECT'B',       '2020-01-21 13:02:45.000',       300010
UNION SELECT'E',       '2020-01-21 13:02:50.000',       300010
UNION SELECT'B',       '2020-01-21 13:02:90.000',       300020
UNION SELECT 'M',      '2020-01-21 13:03:56.000',      300010
)

select a.*
from CTE a
inner join CTE b
on a.eventno = 300010 and b.eventno = 300020 and a.timestamp < b.timestamp and a.id = b.id                   
and not exists (
select 1
from CTE c
where c.timestamp > a.timestamp
and c.timestamp < b.timestamp and c.id = b.id
)
union
select b.*
from CTE a
inner join CTE b
on a.eventno = 300010 and b.eventno = 300020 and a.timestamp < b.timestamp and a.id = b.id                   
and not exists (
select 1
from CTE c
where c.timestamp > a.timestamp
and c.timestamp < b.timestamp and c.id = b.id
)
order by id, eventno

结果是：

id  timestamp   eventno
B   2020-01-21 13:02:45.000 300010
B   2020-01-21 13:02:90.000 300020