I want to find three day average of values. Expected output:

Average should be calculated for 3 day window. My query is:

select a.dt, avg(b.amt) over(order by a.dt ) as running_avg,b.amt
from trans a
left join trans b on b.dt = a.dt
where a.dt between DATEADD(day,-3, a.dt) and getdate()

My query is just giving normal running average and not average for 3 days. Let me know how this can be done in SQL Server.

Thanks!

CodePudding user response：

It seems you need a ROWS/RANGE argument in the windowed AVG():

Data:

SELECT *
INTO trans
FROM (VALUES
   (CONVERT(date, '20220501'), 100),
   (CONVERT(date, '20220502'), 150),
   (CONVERT(date, '20220503'), 50),
   (CONVERT(date, '20220514'), 250),
   (CONVERT(date, '20220515'), 0)
) v (dt, amt)

Statement:

SELECT 
   dt, 
   amt,
   AVG(amt) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS running_avg,
   CASE WHEN COUNT(*) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) = 3 
      THEN AVG(amt) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
      ELSE 0
   END AS running_avg2
FROM trans
ORDER BY dt

Results: