60 Second Rolling Avg With Different Starting Points
-
Hi,
I have a table with columns: jobId, timeStamp (for duration per jobId) and tempData (see attachment). I have created a query that calculates 60 second rolling average by jobId. (you can see it at the end of the post)
Where I am struggling is adding two additional rolling averages. Both of them would be 60 second rolling avg, but with different starting points.
One would start from 15 seconds above the current row and the other one would start from 15 seconds below the current row.
So if the current row shows 2022-11-20 01:40:08.590, one measure would show avg 60 seconds back from 2022-11-20 01:39:53.590 and the other would show avg 60 seconds back from 2022-11-20 01:40:23.590.
Also, for the first 60 seconds and for the last 15 seconds of each jobId - there would be no average (just NULL).
Using PRECEDING/FOLLOWING ROWS for calculation will not work, as timestamps are not continuous (there are missing values). Avg has to be based on timestamp value.
Below is the query I have so far for the rolling 60 second avg.
with cte as (
select jobId,
timeStamp,
dateadd(second, -60, timeStamp) AS lowerBound,
tempData
FROM My_Table t
)
select c.jobId,
c.timeStamp,
c.tempData,
avg(m.tempdata) as MovingAvg
from cte c
inner join My_Table m
ON m.jobId = c.jobId
AND m.timeStamp between c.lowerBound and c.timeStamp
group by c.jobId, c.timeStamp, c.tempData, c.lowerBound
order by 1, 2
Any help would be much appreciated!
-
This script can be used for sample data. Thank you.
-- Setup some testdata
if object_id('tempdb..#t_measures') is not null
drop table #t_measures
create table #t_measures (
jobid int, offset int, tempdata float
, dt datetime
)
insert into #t_measures (
jobid, offset, tempdata
)
values (1, 0, 30)
, (1, 1, 20)
, (1, 3, 21.3)
, (1, 10, 17.3)
, (1, 55, 34.3)
, (1, 62, 38.3)
, (1, 68, 36.3)
, (1, 75, 40.3)
, (1, 80, 30)
, (1, 90, 36)
, (1, 105, 46)
, (1, 155, 50)
insert into #t_measures (
jobid, offset, tempdata
)
SELECT 2
, offset + tempdata, tempdata - 5
FROM #t_measures
update t
set dt = dateadd(second, offset, case when jobid = 1 then '20220101' else '20220201' end)
from #t_measures t
-- Calculate average between current measure and all measures at most 60 seconds back
;with cte as (
select jobid, offset, dateadd(second, -60, dt) AS lowerBound
, dt
, tempdata
FROM #t_measures t
-- Optionally, add WHERE c.offset >= 60 or whatever you need to disregard
)
select c.jobid, c.dt, c.lowerBound, c.tempdata, avg(m.tempdata) as MovingAvg
from cte c
inner join #t_measures m
ON m.jobid = c.jobid
AND m.dt between c.lowerBound and c.dt
group by c.jobid, c.dt, c.tempdata, c.offset, c.lowerBound
order by 1, 2
-
I'm having some difficulty because your sample data and results do not match what is produced by your query. For any situations where timestamps are repeated, like 20-Nov-2022 01:36:02, the moving average in your sample results requires some bit of ordering as a second criteria to not include both data values in the average. Add the situation where timestamps are repeated with the same tempData value and it looks like some sort of line number ordering would be needed.
-
Afaik this matches the output from the existing query. To add additional columns you could add additional CROSS APPLY operators and then vary the date inequalities in the WHERE clause(s)
select t.*, last_60_sec.mvg_avg
from #t_measures t
cross apply (select avg(tt.tempdata)
from #t_measures tt
where tt.jobid=t.jobid
and tt.dt<=t.dt
and tt.dt>dateadd(second, -60, t.dt)) last_60_sec(mvg_avg)
order by t.jobid, t.dt;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply