从时序数据中筛选与特定条件匹配的记录的 SQL

Question

有一个表格，其中存储了如下视频观看日志的数据。

|user_id| status |     time         |
-------------------------------------
|user_a |start   |2019-06-18 00:00:00|
|user_a |progress|2019-06-18 00:00:05|
|user_a |progress|2019-06-18 00:00:10|
|user_a |complete|2019-06-18 00:00:15|
|user_a |start   |2019-06-18 00:10:00|
|user_a |complete|2019-06-18 00:10:05|
|user_b |start   |2019-06-18 00:20:00|
|user_b |progress|2019-06-18 00:20:05|
|user_b |progress|2019-06-18 00:20:10|

"开始"是一个视频观看开始标志，"进度"是一个视频观看标志，并且"完成"是视频观看完成标志。

并不总是设置完整的标志，因为可能无法观看视频。

但是，在某些情况下，可能会设置完整的标志而没有进展。

我想从上述数据记录中排除只有开始和完成(没有进度(的数据记录。

具体来说，我想排除以下记录。

|user_a |start   |2019-06-18 00:10:00|
|user_a |complete|2019-06-18 00:10:05|

有没有办法用sql做到这一点？

Answer 1

您可以使用

lead()和lag()：

select *
from (select t.*,
             lag(status) over (partition by user_id order by time) as prev_status,
             lead(status) over (partition by user_id order by time) as next_status
      from t
     ) t
where not ((status = 'start' and next_status = 'complete') or
           (status = 'complete' and prev_status = 'start')
          );

或者，给定示例数据，您可以使用：

where 'progress' in (prev_status, status, next_status);

Answer 2

您可以使用

lead()和lag()窗口分析函数：

select user_id, status, time 
  from
  (
   select lag(status,1,'x') over (order by time) lg,
          lead(status,1,'x') over (order by time) ld,
          t.*
     from t
   ) t2
 where not ( lg = ld and status in ('start','complete')) 

我在小提琴中展示了演示SQL Server DB