How to filter out distinct pairs in a table?

user_11009484 0
Viewed 39

Followup to a previous question: How to remove duplicates so that only pairs exist in a table?

So I'm trying to filter out distinct IN/OUTs for each ticket_uuid in a ticket_events table

For example, if I have

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

I want

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

But consecutive IN or OUT is allowed if the current ticket_uuid is distinct from the previous one, as shown above with 84373463-48c3-3764-74cd-shfhfsfuey12.

Right now I have:

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

but it seems to be giving me more results than I ask for. How should I fix my query so it gives the above? Thank you!

postgresql sql
1 Answers

Consider adding a PARTITION BY clause (leaving out Type in grouping). Then account for NULL in outer query WHERE clause:

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

user_14224510
Related