My data, which unfortunately I cannot post due to company policies, consists of a timeseries with uneven time step (in seconds) which can vary wildly from sub-second to thousands of seconds between consecutive rows.
I need to to compute the one-hour moving average of a given measure of interest. Ideally it would look something like this:
WINDOW_AVG(max([Measure of Interest]),<<variable offset>>,0)
My challenge is determining <<variable offset>> given the uneven time step. The timeseries also specifies UTC data & time and elapsed seconds from some arbitrary start time, incremented by the timestep. Here's a small screenshot that I can post which shows the nature of the data
Your ideas are much appreciated. Thanks!
What should be your [Measure of Interest] value
for the Hour(s) with no rows (measurements) in the data
(when the Time Step is more than 3600 sec for some rows)?
Should it be Null, 0, or may be the previous TS value?
A general approach would be to densify the TS (one way or another),
then use MAX() aggregation along the densified time dimension.