I’m using Postgres 11 and trying to figure out if I can do this in a single query.
Suppose I have two tables:
Event1 ------ id uuid timestamp
Event2 ------ uuid timestamp
When I join these tables by uuid, Event2 rows should be attributed to a particular Event1.id up to when a newer Event1.id is active.
For example, if I have these rows:
Event1:
id | uuid | timestamp --------------------- 10 | abc | 2020-10-01 01:00:00 20 | abc | 2020-10-01 02:11:00 30 | abc | 2020-10-01 03:00:00
Event2:
uuid | timestamp ---------------- abc | 2020-10-01 01:10:00 abc | 2020-10-01 01:15:00 abc | 2020-10-01 02:30:00 abc | 2020-10-01 03:00:00 abc | 2020-10-01 03:30:00
I would get:
id | uuid | timestamp --------------------- 10 | abc | 2020-10-01 01:10:00 10 | abc | 2020-10-01 01:15:00 20 | abc | 2020-10-01 02:30:00 30 | abc | 2020-10-01 03:00:00 30 | abc | 2020-10-01 03:30:00
Event1.id == 10 would get attributed Event2 items from timestamp 01:00:00 until 02:10:59. Event1.id == 20 would get attributed Event2 items from timestamp 02:11:00 until 02:59:59. Event1.id == 20 would get attributed everything after 03:00:00.
Is this possible in SQL?
Advertisement
Answer
One approach is to use window functions to get the “next” event for each row in event1, and then it’s a simple join from there.
WITH time_window AS (
SELECT id, uuid, timestamp as start,
lead(timestamp) OVER (PARTITION BY uuid ORDER BY timestamp) as end
FROM event1
)
SELECT time_window.id, time_window.uuid, event2.timestamp
FROM time_window, event2
WHERE time_window.uuid = event2.uuid
AND event2.timestamp >= time_window.start
AND (time_window.end IS NULL OR event2.timestamp < time_window.end);