COMPUTING ROLLING & CUMULATIVE STATS – 2

This is a continuing series for “Computing Running and Cumulative Average“.

A cumulative sum (or running total) refers to a data set’s partial sum. It is a sum of a number sequence that is refreshed whenever a new number is added to the sequence.

The cumulative sum (or running total) is quite common in finance and trend analysis. Especially when dealing with daily data such as daily sales, daily bank balances, and so on. Depending on the measurements, it displays the total number for each day or month. A running total is the total of the previous numbers in a column. Window operations act on a collection of rows, returning an aggregate value for each row in the result set.

Moving total, on the other hand, is a time series technique for evaluating and finding patterns in data (also known as rolling means/rolling averages/rolling totals). For each moment in time, they are calculated as the mean of the current and a defined number of immediately preceding values. The fundamental concept is to look at how these totals (or averages) perform over time rather than how the original or raw data pieces behave.

Example dataset:

DROP TABLE IF EXISTS OddEvenData;

CREATE TABLE OddEvenData(Id INT, PropertyDescription VARCHAR(10));

INSERT INTO OddEvenData VALUES
(1,’odd’),(2,’even’),(3,’odd’),(4,’even’),(5,’odd’)
,(6,’even’),(7,’odd’),(8,’even’),(9,’odd’),(10,’even’)
,(11,’odd’),(12,’even’),(13,’odd’),(14,’even’),(15,’odd’),(16,’even’);

OddEvenDataset
Let us now solve the following requests using this dataset.

Write an analytical function, which can produce the running count of even and odd numbers.
Write an analytical function, which can produce the running average of even and odd numbers.
Write a query for moving window: 1 row before the current row and 2 rows after the current row
Calculate the min() and max() Id for each property.

Answers:
SELECT Id, PropertyDescription,
COUNT(Id) OVER (PARTITION BY PropertyDescription ORDER BY Id) AS ‘CumulativeCount’
FROM OddEvenData;

SELECT Id, PropertyDescription,
AVG(Id) OVER (PARTITION BY PropertyDescription ORDER BY Id) AS ‘CumulativeAvg’ FROM OddEvenData;

SELECT Id, PropertyDescription,
COUNT(Id) OVER(PARTITION BY PropertyDescription
ORDER BY Id
ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS ‘TotalCount’
FROM OddEvenData
MovingWindow
SELECT Id, PropertyDescription,
MAX(Id) OVER (PARTITION BY PropertyDescription) AS max ,
MIN(Id) OVER (PARTITION BY PropertyDescription) AS min
FROM OddEvenData
MinMax