1

I have a table called measurement_stat that takes the following form:

id     sensor_name              timestamp           value_cal 
1   measurement_status    2020-07-28 16:00:00    start_measurement
2   measurement_status    2020-07-28 17:00:00    stop_measurement
3   measurement_status    2020-07-28 18:00:00    start_measurement
4   measurement_status    2020-07-28 19:00:00    stop_measurement
5   measurement_status    2020-07-28 20:00:00    start_measurement
...

I want to create a VIEW that returns this:

id      start_measurement      stop_measurement
1      2020-07-28 16:00:00    2020-07-28 17:00:00
2      2020-07-28 18:00:00    2020-07-28 19:00:00
...

How do I do this? I have tried doing this:

CREATE VIEW start_times AS 
   SELECT timestamp AS start_measurements FROM measurement_stat
      WHERE sensor_name = 'measurement_stat' AND value_cal = 'start_measurement'

Then I do the same for stop_times. Finally I perform a LEFT OUTER JOIN on the two VIEWs that I just created.

This works but, it has a problem. Occasionally while taking data if something goes wrong, we have to stop the measurement and sometimes 'stop_measurement' doesn't log. So there is a start_measurement with no associated stop_measurement. This issue throws my VIEW off and the columns become out of sync. If this starts happening often, then problem just compounds and gets even worse.

Is there a way of handling this in SQL? It would be great if I could throw in NULLs in place of 'stop_measurement' if there are 2 consecutive 'starts' without a 'stop' in between

GMB
  • 216,147
  • 25
  • 84
  • 135
pmoh
  • 81
  • 8
  • What do you want if there are two consecutive `stop_measurement` records? – Mike Organek Aug 17 '20 at 22:16
  • This is an unlikely situation because we can only stop a measurement if it was started in the first place. However, if this were to happen then, I want to fill in a NULL for start_measurement in between the 2 stop_measurement records. – pmoh Aug 17 '20 at 22:18

1 Answers1

2

You can use window functions. The idea is to start a new group every time a 'start_measurement' row is met for a given sensor. You can then use this information to group the data:

create view v_measurement_stat as
select
    sensor_name,
    min(timestamp) start_measurement,
    max(timestamp) filter(where value_cal = 'stop_measurement') stop_measurement
from (
    select 
        ms.*,
        count(*) filter(where value_cal = 'start_measurement')
            over(partition by sensor_name order by timestamp) grp
    from measurement_stat ms
) t
group by sensor_name, grp
order by sensor_name, min(timestamp)
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Thank you so much!! This worked after I filtered out a couple of other sensor names that I hadn't mentioned. – pmoh Aug 17 '20 at 22:32