In this lesson, you will learn how to calculate running totals and progressive metrics over dynamic time series using the Cumulative Aggregate Pattern.
The Logic of Cumulative Aggregation
A cumulative aggregate—often called a "running total"—is a calculation where each row’s value is added to the sum of all preceding rows in a specific sequence. Unlike a standard GROUP BY that collapses data into single buckets, a cumulative aggregate requires the database to maintain a "memory" of values as it iterates through a sorted dataset.
To achieve this, we use the OVER clause combined with an ORDER BY statement. This tells the database to process the data in a specific sequence and incrementally compute the sum at each step.
Concrete Example
Imagine we have a table named DailySales with columns sale_date and amount. To see how our revenue grows day-by-day, we apply the pattern like this:
SELECT
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM DailySales;
How it Works Under the Hood
When you execute this, the database engine creates an "analytical frame." Because you’ve ordered by sale_date, the engine effectively "slides" a window down your table. For the first row, the window covers only the first row. For the second row, the window expands to include both the first and second, and so on.
Because we are working with dynamic time series, this pattern is incredibly powerful for identifying trends, such as when a specific milestone (e.g., reaching $1,000 in total sales) was crossed. By combining the ordering dynamics we discussed in previous lessons, you ensure the database performs this calculation efficiently, often leveraging existing indexes to avoid unnecessary sorting operations during the execution phase.
Key Takeaway: The Cumulative Aggregate Pattern transforms static snapshots of data into a progressive story by using the OVER (ORDER BY ...) clause to maintain a running tally as the database scans through your time-series records.