根据条件提取记录

user 发表于 Dev

用户

我只想提取OrderofEvents1 后跟 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

戈登·利诺夫

您可以使用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) ;

这将返回两个事件。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。