Computing Running & Cumulative Average

This article helps you understand how to calculate the running average and cumulative average on a given data set.

As discussed earlier, windowing functions are the same in SQL Server, Oracle & Hive and perform computations across a set of table rows that are related to the current row either by the department, area, category, or date/time.

Please observe the sample data set:

Monthly summarized sales amount by location is specified from which we are about to calculate the running average as well as the cumulative average.

–Creating Table
CREATE TABLE SalesData(YYYYMM INT, Location VARCHAR(10), Amount DECIMAL(7,2))

–Inserting values
INSERT INTO SalesData VALUES
(201901,’AJM’,1300),(201901,’DXB’,1450),(201901,’AUH’,1375),
(201902,’AJM’,1150),(201902,’DXB’,1400),(201902,’AUH’,1305),
(201903,’AJM’,1350),(201903,’DXB’,1550),(201903,’AUH’,1250),
(201904,’AJM’,1400),(201904,’DXB’,1675),(201904,’AUH’,1575),
(201905,’AJM’,1200),(201905,’DXB’,1450),(201905,’AUH’,1400),
(201906,’AJM’,1050),(201906,’DXB’,1400),(201906,’AUH’,1250),
(201907,’AJM’,900),(201907,’DXB’,1150),(201907,’AUH’,1000),
(201908,’AJM’,1000),(201908,’DXB’,1250),(201908,’AUH’,1175)

Running Average / Moving Average / Rolling Average:
The reason to compute a rolling average is to streamline the highs and lows of the dataset and figure out the patterns or trends in the information.

–Moving Average
SELECT YYYYMM, Location, Amount,
AVG(Amount) OVER(PARTITION BY Location ORDER BY YYYYMM ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS ‘MovingAverage’
FROM SalesData

Cumulative Average:
Cumulative or Span of Time is the most common way time is used in a measure. Traditionally Cumulative measures sum data across a span of time.

–Cumulative Average
SELECT Location, YYYYMM, Amount,
AVG(Amount) OVER(PARTITION BY Location ORDER BY YYYYMM ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ‘CumulativeAverage’
FROM SalesData
GO

Both running and cumulative averages can be accomplished in a single SELECT statement.

SELECT Location,YYYYMM, Amount,
AVG(Amount) OVER(PARTITION BY Location ORDER BY YYYYMM ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
    AS ‘MovingAverage’,
AVG(Amount) OVER(PARTITION BY Location ORDER BY YYYYMM ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW)
    AS ‘CumulativeAverage’
FROM SalesData
GO