1

I have a table called SessionEvents.

SessionEvents

I'm trying to create a column that represents the firmware that is on the machine to later be able to filter messages for only certain software.

Currently my SQL query looks like this. (Based on answer by adamlamar in How do I efficiently select the previous non-null value? with slight modifications.)

I'm using JOIN in since machine_id is in a related table called Sessions.

SELECT *,
    first_value(message_info) over (partition by value_partition order by event_time) as ColumnFirmware

FROM(        
SELECT SessionEvents.event_time, SessionEvents.message_type, SessionEvents.message_info, Sessions.machine_id,
    sum(case when message_type <> 1 then 0 else 1 end) over (partition by machine_id order by event_time) as value_partition

FROM SessionEvents
JOIN Sessions ON SessionEvents.SessionId=Sessions.Id) t
-- WHERE machine_id = 1

This works for a single machine but i cant find a way to implement this properly for several machines. How should i modify the query to adopt if for several machines?

(Data is fetched from Azure SQL database)

Adam Andersson
  • 113
  • 11

1 Answers1

1

This is a gaps-and-islands problem. First define the islands, then spread the firmware:

SELECT s.*,
       MAX(CASE WHEN message_type = 1 THEN message_info
           END) OVER (PARTITION BY machine_id, seqnum - sequm_m
                     ) as firmware
FROM (SELECT se.event_time, se.message_type, se.message_info, s.machine_id,
             ROW_NUMBER() OVER (PARTITION BY machine_id ORDER BY event_time) as seqnum_m,
             ROW_NUMBER() OVER (ORDER BY event_time) as seqnum
      FROM SessionEvents se JOIN
           Sessions s
           ON se.SessionId = s.Id
      ) s
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Seems like this is the way to go, changed sequm_2->seqnum_m. Im having some issues where where firmware ends up as null for some kind of reasion.(message_info sometimes being NULL might have something to do with it?) – Adam Andersson Mar 11 '20 at 12:23
  • @AdamAndersson . . . Look at the groups being created -- `seqnum - seqnum_m`. If you get a `NULL` value, then there are no appropriate messages for that range of values. – Gordon Linoff Mar 11 '20 at 12:33
  • I used (PARTITION BY s.Id ORDER BY DateTime) to define seqnum and get the firmware for each session and it now works as intended. – Adam Andersson Mar 11 '20 at 12:46