60 Second Rolling Avg With Different Starting Points

[Vuksi938]

Hi,

I have a table with columns: jobId, timeStamp (for duration per jobId) and tempData. I 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!

[harishgg1]

hi

could you post sample data script ? thank you

[Vuksi938]

Hi,

Something like this. I have exposed lowerBound for the 60 sec reference, but it will be hidden from the main query.

-- 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

[harishgg1]

hi

i tried to do this using cursors

please see my attempt

i know what you said about begining and end 60 seconds and 15 seconds
but take a look at this first .. then we can see

i have made some changes to your script

i added row number to your table
i created my output table
from that output table i got my result

create the sample data script

create table t_measures (rn int,jobid int, offset int, tempdata float, dt datetime
)

create table Final_Output (rn2 int , jobid2 int , sumTempData2 FLOAT , TotTempData FLOAT , Lt60 varchar(1) ,counter int )

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

; with cte as (select ROW_NUMBER() over(order by dt) as rn,dt from t_measures t ) update t set t.rn = cte.rn from cte join t_measures t on cte.dt = t.dt

DECLARE @rn int , @jobid int , @counter int = 0 , @TempData FLOAT; DECLARE db_cursor CURSOR FOR SELECT rn  , jobid  , TempData  FROM t_measures
OPEN db_cursor ; FETCH NEXT FROM db_cursor INTO @rn  , @jobid  , @TempData 
WHILE @@FETCH_STATUS = 0  
BEGIN  
       set @counter = @counter  + 1 	  
	  -----------------------------------------------------------------------------------
	  DECLARE @rn2 int , @jobid2 int , @sumTempData2 FLOAT =0.0, @TotTempData FLOAT = 0.0 , @Lt60 varchar(1) = 'x'; DECLARE db_cursor2 CURSOR FOR SELECT rn  , jobid  , TempData  FROM t_measures where rn <= @rn order by rn desc 
	  OPEN db_cursor2  ;  FETCH NEXT FROM db_cursor2 INTO @rn2  , @jobid2  , @sumTempData2 
	  WHILE @@FETCH_STATUS = 0  
        BEGIN  		    		   
			set @TotTempData = @TotTempData + @sumTempData2
			IF  ( @TotTempData <= 60 ) set @Lt60 = 'Y'ELSE set @Lt60 = 'x'
			insert into Final_Output select @rn2  , @jobid2  , @sumTempData2 , @TotTempData , @Lt60 ,@counter
		  FETCH NEXT FROM db_cursor2 INTO @rn2  , @jobid2  , @sumTempData2 
		END 
      CLOSE db_cursor2  
      DEALLOCATE db_cursor2
	  -----------------------------------------------------------------------------------
      FETCH NEXT FROM db_cursor INTO @rn  , @jobid  , @TempData 
END 
CLOSE db_cursor  
DEALLOCATE db_cursor 

; with cte as ( select jobid2,counter,min(rn2) as minrn2 from  Final_Output where Lt60 = 'Y' group by jobid2,counter ) 
 select 
      a.jobid2
	, TotTempData
	, a.counter  
 from 
    Final_Output  a , cte 
 where 
     Lt60 = 'Y' 
	    and 
  	 cte.jobid2 = a.jobid2 and cte.counter = a.counter and cte.minrn2 = a.rn2