CODEWITHSUNDEEP
sqlpostgresql
Emil Ahlbäck
Emil Ahlbäck

Reputation: 6206

Grouping together logical events

My table is filled with events from an external source, such as:

SELECT target_type, timer_type, event_happened_at
FROM tracked_events 
ORDER BY event_happened_at ASC;


TARGET_TYPE  TIMER_TYPE    EVENT_HAPPENED_AT
"JOB",       "START",      "2018-11-06 06:00:00+00"
"JOB",       "STOP",       "2018-11-06 10:30:00+00"
"PAUSE",     "START",      "2018-11-06 10:30:00+00"
"PAUSE",     "STOP",       "2018-11-06 11:00:00+00"
"JOB",       "START",      "2018-11-06 11:00:00+00"
"JOB",       "STOP",       "2018-11-06 15:00:00+00"

We can see a logical grouping into three rows:

TYPE   START                   END
JOB,   2018-11-06 06:00:00+00, 2018-11-06 10:00:00+00
PAUSE, 2018-11-06 10:00:00+00, 2018-11-06 11:00:00+00
JOB,   2018-11-06 11:00:00+00, 2018-11-06 15:00:00+00

I'm trying to figure out a nice way to perform this grouping in SQL. The event types are predefined and guaranteed to be sent in a "logical way" (i.e. PAUSE/START will not happen before JOB/END., and start/ends are guaranteed to exist for all events.)

So essentially if I see JOB/START I need to find the next JOB/END, and equally for PAUSE/START to PAUSE/END.

I can see a query where I only look for START events and do a subquery to find its corresponding END:

WITH starts AS (
    SELECT session_id, target_type, timer_type, event_happened_at
    FROM received_session_events 
    WHERE session_id = 266
    AND TIMER_TYPE = 'START'
    ORDER BY event_happened_at ASC
)

SELECT target_type, event_happened_at AS started_at,
       (
         SELECT event_happened_at 
         FROM received_session_events end_event
         WHERE session_id = starts.session_id 
         AND   timer_type = 'STOP' AND end_event.target_type = starts.target_type
         AND   end_event.event_happened_at > starts.event_happened_at
         ORDER BY event_happened_at ASC
         LIMIT 1
       ) AS ended_at
FROM starts

This works, giving the correct result as well, but something seems inefficient and off about it. Adding indexes is an option that I haven't explored (nor do I know where to start except the obvious ones appearing in the WHERE clauses.)

Upvotes: 3

Views: 49

Answers (1)

Parfait
Parfait

Reputation: 107567

Assuming data adheres to "logical order" of event targets and time, consider adding a row number and running a shifted self join:

WITH s2 AS
 (SELECT *, ROW_NUMBER() OVER() As ROW_NUM
  FROM received_session_events)         

SELECT CASE 
            WHEN s1.TARGET_TYPE = 'DEAL' 
            THEN 'JOB' 
            ELSE s1.TARGET_TYPE
       END AS "TYPE", s1.EVENT_HAPPENED_AT AS "START", s2.EVENT_HAPPENED_AT AS "END"
FROM s2 AS s1
JOIN 
   s2 
 ON s1.TARGET_TYPE = s2.TARGET_TYPE AND s1.ROW_NUM = s2.ROW_NUM - 1
ORDER BY s1.EVENT_HAPPENED_AT;

--   TYPE                    START                     END
--    JOB   2018-11-06 06:00:00+00  2018-11-06 10:30:00+00
--  PAUSE   2018-11-06 10:30:00+00  2018-11-06 11:00:00+00
--    JOB   2018-11-06 11:00:00+00  2018-11-06 15:00:00+00

Rextester Demo

Upvotes: 1

Related Questions