Calculate a Moving Average in SQL using A Windows Function

Problem

How do you create a moving average using windows functions in SQL?

Background

There is no built-in function to create a moving average, but you can use the AVG() window function to do so.  For our example we calculate the 4 week moving average of weekly sales.  Here is the table we’ll use:

Note:  The tables and data for this example are found located within the EssentialSQL\WinCode GitHub Repo

Solution

To create a 4-week moving average of the sales data, we can use the window functions available in SQL. Specifically, we can use the AVG() function as a window function to calculate the average of the previous four rows in the Amount column for each row. Here is the SQL code for creating the 4-week moving average:

select SalesDate, Product, Amount,
    AVG(Amount) over (partition by Product order by SalesDate
                 rows between 3 PRECEDING and current row) as FourWeekMovingAvg
from WeeklySales;

In this SQL code, we use the PARTITION BY clause to group the data by product and the ORDER BY clause to sort the data by sales date. We then use the ROWS BETWEEN clause to specify the window for the moving average calculation. We use the 3 PRECEDING and CURRENT ROW to specify a window of four rows for the moving average calculation.

The resulting output will include the sales data and the calculated 4-week moving average for each product:

Discussion

Now that we have computed the 4-week moving average for each product, we can observe how it changes over time. We can use this information to make better decisions and forecast future sales trends. For example, if we see that the moving average of sales for a particular product is increasing, we may want to increase production or advertising for that product. Conversely, if we see that the moving average is decreasing, we may want to scale back production or advertising for that product. By using the moving average to identify trends, we can make data-driven decisions that are more likely to lead to successful outcomes.

In summary, using a window function to calculate a moving average in SQL is a powerful technique that provides many advantages over alternative methods. Window functions allow us to compute complex calculations over a range of rows, and they can be used to perform many different types of analysis. By calculating a moving average with a window function, we can identify trends and make more informed decisions based on our data. Ultimately, using a moving average in SQL can help us to gain insights into our data and make better-informed decisions, which can lead to improved outcomes in our business or organization.

If window functions are not available, there are a few other alternatives you can use to calculate a moving average in SQL, although they may not be as efficient or flexible as window functions.

One option is to use a subquery with a GROUP BY clause to calculate the rolling average. The subquery would sum up the sales for each product for the previous four weeks, and then divide by four to get the average. You would then join this subquery back to the original table to get the rolling averages for each row. Here is an example SQL query, written for SQL Server, to compute the 4-week rolling average using a subquery:

select SalesDate, Product, Amount,
    AVG(Amount) over (partition by Product order by SalesDate
                 rows between 3 PRECEDING and current row) as FourWeekMovingAvg
from WeeklySales;

In this query, the subquery calculates the average sales amount for the previous four weeks by using the AVG aggregate function and a WHERE clause that filters the rows based on the current product and the current sales date. The main query then selects the sales date, product, amount, and moving average for each row, and orders the results by product and sales date.

Another alternative is to use a user-defined function (UDF) to calculate the moving average. A UDF is a custom function that can be defined in SQL and called from queries. The function would take as input the sales data and the number of weeks to use for the moving average, and would return the moving averages for each row. Here is an example SQL code for a UDF to compute a 4-week moving average:

CREATE FUNCTION MovingAvg4Weeks(@SalesDate DATE, @Product VARCHAR(20))
RETURNS FLOAT
AS
BEGIN
    DECLARE @Avg FLOAT;
    SELECT @Avg = AVG(Amount)
    FROM WeeklySales
    WHERE Product = @Product
        AND SalesDate BETWEEN DATEADD(week, -3, @SalesDate) AND @SalesDate;
    RETURN @Avg;
END;

In this UDF, the AVG function is used to calculate the moving average for the previous four weeks, and the function returns the result as a floating-point number. To use this function in a query, you would call it like this:

select SalesDate, Product, Amount, dbo.MovingAvg4Weeks(SalesDate, Product) AS MovingAvg
from WeeklySales
order by Product, SalesDate;

In summary, while window functions are a powerful and efficient way to calculate a moving average in SQL, there are other alternatives you can use if they are not available. These alternatives include subqueries with GROUP BY clauses and user-defined functions. However, these alternatives may not be as flexible or efficient as window functions and may require more code to achieve the same results.