sql - 如何删除重复项以便表中只存在对？

Question

我有一个 ticket_events 表，它显示 checkin 和 checkout 时间戳。

timestamp               type
2018-12-26 02:46:01 IN
2018-12-26 02:46:13 IN
2018-12-26 03:17:57 OUT
2018-12-26 04:12:13 IN
2018-12-26 04:12:25 IN
2018-12-26 04:35:41 OUT
2018-12-26 04:35:53 OUT
2018-12-26 05:11:05 IN
2018-12-26 06:03:42 OUT
2018-12-26 06:03:55 OUT
2018-12-26 06:04:43 IN
2018-12-26 06:04:50 IN
2018-12-26 06:28:07 OUT
2018-12-26 06:28:19 OUT
2018-12-26 07:00:11 IN
2018-12-26 07:00:22 IN
2018-12-26 07:19:13 OUT
2018-12-26 07:59:21 IN
2018-12-26 08:24:57 OUT

我只希望 IN OUT 对出现在表中，没有连续的 IN 或 OUT。所以例如我想要:

timestamp               type
2018-12-26 02:46:01 IN
2018-12-26 03:17:57 OUT
2018-12-26 04:12:13 IN
2018-12-26 04:35:41 OUT
2018-12-26 05:11:05 IN
2018-12-26 06:03:42 OUT
2018-12-26 06:04:43 IN
2018-12-26 06:28:07 OUT
2018-12-26 07:00:11 IN
2018-12-26 07:19:13 OUT
2018-12-26 07:59:21 IN
2018-12-26 08:24:57 OUT

有人知道执行此操作的 sql 查询吗？

我现在的查询是

select timestamp, type from ticket_events where ticket_uuid='value'

Answer 1

您想删除前一行具有相同类型的任何行。所以:

select timestamp, type
from (select t.*,
             lag(type) over (order by timestamp) as prev_type
      from ticket_events t
     ) t
where prev_type <> type or prev_type is null;

where 子句也可以表述为:

where prev_type is distinct from type

如果您想删除“违规”行，您可以执行以下操作——假设 timestamp 是唯一的:

delete from ticket_events
    using (select t.*,
                  lag(type) over (order by timestamp) as prev_type
           from ticket_events t
          ) tt
    where tt.timestamp = t.timestamp and
          tt.prev_type = t.type;