Moving aggregates and window frames

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Average each product with its two immediate predecessors.

Run the example

SELECT TOP (5) ProductID, ListPrice,
       AVG(ListPrice) OVER (ORDER BY ProductID
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ThreeProductAverage
FROM dbo.Products
ORDER BY ProductID;

Read it step by step

1. The frame includes the current product and up to two preceding ProductIDs.
2. At the start there are fewer than three available rows, so AVG uses the available rows.
3. TOP affects the display after the window calculation.

Check the result

The moving average includes at most three rows and uses the decimal output scale of AVG.

Practice

Compute a moving sum of ListPrice over the current product and one predecessor for Products 1–5.

SELECT TOP (5) ProductID, ListPrice,
       SUM(ListPrice) OVER (ORDER BY ProductID
           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS TwoProductSum
FROM dbo.Products
ORDER BY ProductID;