根据条件提取记录

Question

我只想提取OrderofEvents后面跟2的行。例如，数据按EventTime排序，并且每当EventName改变时，OrderofEvents就改变。每当OrderofEvents发生变化时，我都要提取该行和前一行。以下是输入数据的样子-

EventName             EventTime       OrderofEvents
AddedtoCart       2020-02-22 11:40         1 
AddedtoCart       2020-02-23 12:40         1   
AddedtoCart       2020-02-24 01:40         1   
Billed            2020-02-24 01:42         2   
AddedtoCart       2020-03-01 02:40         1   
AddedtoCart       2020-03-02 03:40         1   
AddedtoCart       2020-03-02 04:40         1   
AddedtoCart       2020-03-03 05:40         1    

输出应该看起来像-

EventName             EventTime       OrderofEvents 
AddedtoCart       2020-02-24 01:40         1   
Billed            2020-02-24 01:42         2      

Answer 1

您可以使用lead()和lag():

select t.*
from (select t.*,
lead(OrderofEvents) over (order by eventtime) as next_ooe,
lag(OrderofEvents) over (order by eventtime) as prev_ooe
from t
) t
where (OrderofEvents = 1 and next_ooe = 2) or
(OrderofEvents = 2 and prev_ooe = 1) ;

这将返回两个事件。

Answer 2

使用lead函数

Select * from table
where 
OrderofEvents<> lead(OrderofEvents) over ()

或者，如果不支持以上内容，请尝试此

WITH CTE AS 
(Select *,lead(OrderofEvents) over (order by (SELECT NULL) 
)as lg
from table)
Select * from CTE where
OrderofEvents<> lg