Microsoft SQL Server Window functions Centered Moving Average

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

Example

Calculate a 6-month (126-business-day) centered moving average of a price:

SELECT TradeDate, AVG(Px) OVER (ORDER BY TradeDate ROWS BETWEEN 63 PRECEDING AND 63 FOLLOWING) AS PxMovingAverage
FROM HistoricalPrices

Note that, because it will take up to 63 rows before and after each returned row, at the beginning and end of the TradeDate range it will not be centered: When it reaches the largest TradeDate it will only be able to find 63 preceding values to include in the average.



Got any Microsoft SQL Server Question?