Hi
pturmel
December 21, 2022, 2:26pm
2
You use your DB's lead() or lag() functions with some subquery nesting. Examples lifted from a recent nearly identical question:
I usually do all of this in the database with lead() and/or lag() window functions. Say you have multiple of these recording to a single table, using an id column to segregate them. You record every change, and due to tag restarts or script restarts you get some duplicate rows as you’ve shown. The query to pick out all of the completed cycles that overlap a time period would be something like this:
Select
id,
t_stamp,
finish_ts,
finish_ts - t_stamp As duration
From (
Select
id,
t_st…
I presume you want to ignore repeat ones and zeros in the output column? If so, running periods from table1 would be something like this:
Select t_stamp as start_ts,
lead(t_stamp, 1, current_timestamp) Over (Order by t_stamp) as end_ts
From (
Select t_stamp, output
From (
Select t_stamp,
output,
lag(output, 1, 0) Over (Order By t_stamp) as lag_out
From table1
) innerq
Where output != lag_out
) changesq
Where output = 1
Order By start_ts
And similarly, downtime pe…
And more here .
