sql - 如何过滤掉表中的不同对？

Question

跟进上一个问题: How to remove duplicates so that only pairs exist in a table?

所以我试图为 ticket_events 表中的每个 ticket_uuid 过滤掉不同的 IN/OUT

例如，如果我有

ticket_uuid                             timestamp               type
5d6faf4c-c76f-48ed-9780-dbdad1ed06c6    2018-08-20 13:53:13 IN
5d6faf4c-c76f-48ed-9780-dbdad1ed06c6    2018-08-20 13:59:25 OUT
5d6faf4c-c76f-48ed-9780-dbdad1ed06c6    2018-08-20 13:59:31 OUT
5d6faf4c-c76f-48ed-9780-dbdad1ed06c6    2018-08-20 17:15:06 OUT
5d6faf4c-c76f-48ed-9780-dbdad1ed06c6    2018-08-20 19:35:43 IN
5d6faf4c-c76f-48ed-9780-dbdad1ed06c6    2018-08-20 19:54:59 OUT
84373463-48c3-3764-74cd-shfhfsfuey12    2018-08-20 19:57:23 OUT

我要

ticket_uuid                             timestamp               time
5d6faf4c-c76f-48ed-9780-dbdad1ed06c6    2018-08-20 13:53:13 IN
5d6faf4c-c76f-48ed-9780-dbdad1ed06c6    2018-08-20 13:59:25 OUT 
5d6faf4c-c76f-48ed-9780-dbdad1ed06c6    2018-08-20 19:35:43 IN
5d6faf4c-c76f-48ed-9780-dbdad1ed06c6    2018-08-20 19:54:59 OUT
84373463-48c3-3764-74cd-shfhfsfuey12    2018-08-20 19:57:23 OUT

但如果当前的 ticket_uuid 与前一个不同，则允许连续 IN 或 OUT，如上所示 84373463-48c3-3764-74cd-shfhfsfuey12。

现在我有:

select ticket_uuid, timestamp, type
from (select t.*,
             lag(type) over (order by timestamp) as prev_type,
             lag(ticket_uuid) over (order by timestamp) as prev_ticket
      from ticket_events t
     ) t
where prev_type is distinct from type or prev_ticket=ticket_uuid

但它给我的结果似乎比我要求的要多。我应该如何修复我的查询以便它给出以上内容？谢谢!

Answer 1

考虑添加一个 PARTITION BY 子句(在分组中省略 Type)。然后在外部查询 WHERE 子句中考虑 NULL:

SELECT ticket_uuid, "timestamp", "type"
FROM (SELECT t.ticket_uuid, t."timestamp", t."type",
             LAG("type") OVER (PARTITION BY ticket_uuid ORDER BY "timestamp") as prev_type
      FROM ticket_events t
     ) t
WHERE "type" <> prev_type OR prev_type IS NULL

Rextester demo