Moving Average

Suppose you are the owner of a chain of burger outlets, and you have the dataset of customer ratings over the past six months for each store. How then, will you determine which outlets are improving against those that are not?

Calculating the moving average for each outlet is one way this can be achieved.

We will be using the classicmodels database, which you can download from mysqltutorial for this article.

The Order Details Table

SELECT
   orderNumber,
   productCode,
   quantityOrdered,
   priceEach 
FROM
   orderdetails 
WHERE
   orderNumber IN 
   (
      10100,
      10101
   )
;

We shall assume that each order was made chronologically, and we want to determine if the order amounts are increasing or decreasing over time. We first have to create an intermediate table where we calculate the amount for each order.

Order Totals

SELECT
   lineTotals.orderNumber,
   SUM(lineTotals.lineTotal) AS 'orderTotal' 
FROM
   (
      SELECT
         orderNumber,
         quantityOrdered * priceEach AS 'lineTotal' 
      FROM
         orderdetails
   )
   AS lineTotals 
GROUP BY
   lineTotals.orderNumber 
ORDER BY
   lineTotals.orderNumber LIMIT 20;

Moving Average

SELECT
   ordertotals.ordernumber,
   ordertotals.ordertotal,
   AVG(ordertotals.ordertotal) OVER( 
ORDER BY
   ordertotals.ordernumber ASC range BETWEEN 4 PRECEDING AND 0 FOLLOWING) AS 'maFive' 
FROM
   (
      SELECT
         linetotals.ordernumber,
         sum(linetotals.linetotal) AS 'orderTotal' 
      FROM
         (
            SELECT
               ordernumber,
               quantityordered * priceeach AS 'lineTotal' 
            FROM
               orderdetails 
         )
         AS linetotals 
      GROUP BY
         linetotals.ordernumber 
      ORDER BY
         linetotals.ordernumber LIMIT 20 
   )
   ordertotals;

There are two points that you should note about the query and result set.

1. The BETWEEN function includes the edges. Therefore, if we are calculating the moving average of the last 5 entries, we intend to include the current entry as well as the past four entries, not five.
   
   

2. SQL will calculate the moving average even when there are insufficient entries (note the moving average in the first four rows). Do note and make necessary adjustments if this is not required for your analysis.

   This query was performed in MySQL - your results might vary based on the dialect of SQL that you are using.