A running total adds up, on each row, everything before it plus the current row: year-to-date sales, an account balance after each transaction, units sold so far… Before window functions this needed correlated subqueries that were slow and hard to read. Today SUM(...) OVER (ORDER BY ...) is enough.
Running total
The shop’s cumulative amount order by order, by date:
| orderDate | product | totalPrice | runningTotal |
|---|---|---|---|
| 2022-01-21 | Xbox series X | 999.98 | 999.98 |
| 2022-06-20 | MacBook Pro M1 | 2449.50 | 3449.48 |
| 2022-07-09 | Xiaomi Mi 11 | 4304.25 | 7753.73 |
| 2022-07-18 | Play Station 5 | 2199.80 | 9953.53 |
| 2022-09-01 | Echo DOT 3 | 1999.50 | 11953.03 |
| 2022-09-01 | Echo DOT 4 | 2999.50 | 14952.53 |
| 2022-09-05 | Nintendo Switch | 899.97 | 15852.50 |
| 2022-09-10 | Balay Oven | 1399.10 | 17251.60 |
The last row matches total sales, $17,251.60. The line ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the window frame: it tells the engine which rows to add up at each point. Here, from the first one (UNBOUNDED PRECEDING) to the current one (CURRENT ROW).
The window frame
The frame is defined with ROWS BETWEEN start AND end, where each end can be:
| Bound | Meaning |
|---|---|
UNBOUNDED PRECEDING |
The first row of the partition |
n PRECEDING |
n rows before the current one |
CURRENT ROW |
The current row |
n FOLLOWING |
n rows after the current one |
UNBOUNDED FOLLOWING |
The last row of the partition |
ROWS vs RANGE: watch out for ties
If you write only ORDER BY without a frame, the engine uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW by default. The difference from ROWS shows up with ties: RANGE treats all rows with the same sort value as one, and gives them all the same running total.
| orderDate | product | totalPrice | withRows | withRange |
|---|---|---|---|---|
| 2022-07-09 | Xiaomi Mi 11 | 4304.25 | 4304.25 | 4304.25 |
| 2022-07-18 | Play Station 5 | 2199.80 | 6504.05 | 6504.05 |
| 2022-09-01 | Echo DOT 3 | 1999.50 | 8503.55 | 11503.05 |
| 2022-09-01 | Echo DOT 4 | 2999.50 | 11503.05 | 11503.05 |
| 2022-09-05 | Nintendo Switch | 899.97 | 12403.02 | 12403.02 |
| 2022-09-10 | Balay Oven | 1399.10 | 13802.12 | 13802.12 |
Both Echo DOTs are from September 1. With ROWS the total goes up in two steps; with RANGE both already get the sum of the two. Neither is wrong, but ROWS is almost always what’s expected, so it’s best to write the frame explicitly.
ROWS is also faster than RANGE, which has to store intermediate results on disk. Another reason to always write the frame.Running total per customer
With PARTITION BY the total resets for each group:
| name | orderDate | product | totalPrice | customerRunningTotal |
|---|---|---|---|---|
| Helen | 2022-09-01 | Echo DOT 3 | 1999.50 | 1999.50 |
| Helen | 2022-09-01 | Echo DOT 4 | 2999.50 | 4999.00 |
| Janice | 2022-06-20 | MacBook Pro M1 | 2449.50 | 2449.50 |
| Kimberly | 2022-09-05 | Nintendo Switch | 899.97 | 899.97 |
| Mavin | 2022-01-21 | Xbox series X | 999.98 | 999.98 |
| Mavin | 2022-07-18 | Play Station 5 | 2199.80 | 3199.78 |
| Peter | 2022-07-09 | Xiaomi Mi 11 | 4304.25 | 4304.25 |
Moving average
Changing the frame to “the two previous rows and the current one” gives a three-period moving average, which smooths out spikes and is widely used to spot trends:
| orderDate | product | totalPrice | movingAverage3 |
|---|---|---|---|
| 2022-01-21 | Xbox series X | 999.98 | 999.98 |
| 2022-06-20 | MacBook Pro M1 | 2449.50 | 1724.74 |
| 2022-07-09 | Xiaomi Mi 11 | 4304.25 | 2584.58 |
| 2022-07-18 | Play Station 5 | 2199.80 | 2984.52 |
| 2022-09-01 | Echo DOT 3 | 1999.50 | 2834.52 |
| 2022-09-01 | Echo DOT 4 | 2999.50 | 2399.60 |
| 2022-09-05 | Nintendo Switch | 899.97 | 1966.32 |
| 2022-09-10 | Balay Oven | 1399.10 | 1766.19 |
The first two rows don’t have three orders yet, so the average uses the ones available: one and two.
Cumulative percentage
Combining a running total with a grand total (OVER ()) gives the cumulative percentage, the basis of a Pareto analysis (which orders make up 80% of sales?):
| product | totalPrice | cumulativePercentage |
|---|---|---|
| Xiaomi Mi 11 | 4304.25 | 24.9 |
| Echo DOT 4 | 2999.50 | 42.3 |
| MacBook Pro M1 | 2449.50 | 56.5 |
| Play Station 5 | 2199.80 | 69.3 |
| Echo DOT 3 | 1999.50 | 80.9 |
| Balay Oven | 1399.10 | 89.0 |
| Xbox series X | 999.98 | 94.8 |
| Nintendo Switch | 899.97 | 100.0 |
The five largest orders already account for more than 80% of sales.
Practice
- Compute the running total of units (
productQuantity) by date. - Change the moving average to use the previous row, the current one and the next one.
- Use
LAST_VALUEwith the frameROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGto show the last product ordered on every row.
