Running Totals and Moving Averages: The Frame
Controlling exactly which rows a window function spans — ROWS for running totals and moving averages, and the default RANGE frame that quietly includes tied peers and inflates your running sum. Run against PostgreSQL 18.
The ranking functions in the last chapter used the order of a window but not its reach. Every one of them looked at where a row sat in the sequence, not at a band of rows around it. The frame is the part of the window that controls that reach — how far back and how far forward the function actually looks. It’s what turns window functions from “rank things” into “running totals, moving averages, cumulative sums.” It’s also home to one of the most quietly wrong-looking behaviors in all of SQL: a default that inflates your running total the moment two rows tie. We’ll build the useful thing first, then walk into the trap.
The running total
A running total is a sum() that accumulates down the rows. You express it with a frame — the ROWS BETWEEN ... AND ... part of the OVER clause — that spans from the start of the partition to the current row. Real monthly revenue for 2025, with a running total beside it:
WITH monthly AS (
SELECT date_trunc('month', o.order_date)::date AS month,
round(sum(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.order_date >= '2025-01-01'
GROUP BY 1
)
SELECT month, revenue,
sum(revenue) OVER (ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
round(avg(revenue) OVER (ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3mo
FROM monthly
ORDER BY month; month | revenue | running_total | moving_avg_3mo
------------+-----------+---------------+----------------
2025-01-01 | 284938.88 | 284938.88 | 284938.88
2025-02-01 | 251420.97 | 536359.85 | 268179.93
2025-03-01 | 279193.61 | 815553.46 | 271851.15
2025-04-01 | 280253.68 | 1095807.14 | 270289.42
2025-05-01 | 278834.36 | 1374641.50 | 279427.22
2025-06-01 | 271431.39 | 1646072.89 | 276839.81
2025-07-01 | 273534.04 | 1919606.93 | 274599.93
2025-08-01 | 289583.04 | 2209189.97 | 278182.82
2025-09-01 | 243336.79 | 2452526.76 | 268817.96
(9 rows)Two frames, two shapes of answer. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means “every row from the first one up to and including this one.” So running_total accumulates: January’s revenue, then January plus February, and so on to a full-year total in the last row. The moving average uses a sliding frame. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a three-row window — this month and the two before it — that slides down, smoothing the month-to-month bumps. The first two rows have fewer than three predecessors, so they average what’s available. Change the 2 to 11 and you have a trailing-twelve-month average. This is the everyday shape of the frame: pick how many rows back the window reaches, and the aggregate follows.
The default frame, and the tie that inflates it
Here’s the part that bites people. If you write ORDER BY in a window but leave the frame off entirely, you do not get “no frame.” You get a default frame, and it is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — note RANGE, not ROWS. On distinct values those behave identically, which is exactly why the difference hides: the running total above would look the same with the frame omitted. The difference surfaces only when the ORDER BY column has ties.
Here are the top six customers by order count — and four of them tie at 24. Watch the same running sum under the default (RANGE) frame and an explicit ROWS frame:
WITH cust_orders AS (
SELECT customer_id, count(*) AS n_orders
FROM orders GROUP BY customer_id
),
top6 AS (
SELECT customer_id, n_orders
FROM cust_orders
ORDER BY n_orders DESC, customer_id
LIMIT 6
)
SELECT customer_id, n_orders,
sum(n_orders) OVER (ORDER BY n_orders DESC) AS running_default_range,
sum(n_orders) OVER (ORDER BY n_orders DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows
FROM top6
ORDER BY n_orders DESC, customer_id; customer_id | n_orders | running_default_range | running_rows
-------------+----------+-----------------------+--------------
3549 | 29 | 29 | 29
2976 | 25 | 54 | 54
300 | 24 | 150 | 78
409 | 24 | 150 | 102
1820 | 24 | 150 | 126
1840 | 24 | 150 | 150
(6 rows)Look at the four tied rows under running_default_range: all four say 150. That is almost never what you want from a “running” total. The RANGE frame defines “current row” as the current value and every row that ties with it — the whole peer group. So each of the four 24-order customers sees the frame extend through all four of them. Every one reports the total through the end of the tie: 54 + 24 + 24 + 24 + 24 = 150. The running total doesn’t climb; it jumps to the group total and sits there.
The ROWS version is what you meant. ROWS counts physical rows — “everything up to and including this specific row.” So it climbs 78, 102, 126, 150, one customer at a time, ignoring whether the sort key ties. The rule to carry out of this chapter: for a running total, always write an explicit ROWS frame. The bare sum(x) OVER (ORDER BY k) is a latent bug that looks perfect right up until k has a duplicate.
ROWS vs RANGE vs GROUPS
There are three frame modes, and the tie is the only place they diverge:
ROWScounts physical rows.2 PRECEDINGmeans the two rows immediately above this one, full stop. Predictable, tie-blind — the right default for running totals and moving averages.RANGEworks on values. The frame includes every row whoseORDER BYvalue falls in a range of the current row’s value, so all peers (equal values) are pulled in together. That’s the behavior that produced the 150s.GROUPScounts whole peer-groups.1 PRECEDINGmeans “the current group of ties, plus one entire group before it.”
One query shows the last two apart, on the same tied data:
SELECT customer_id, n_orders,
count(*) OVER (ORDER BY n_orders DESC
RANGE BETWEEN CURRENT ROW AND CURRENT ROW) AS range_peers,
count(*) OVER (ORDER BY n_orders DESC
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS groups_1back
FROM top6
ORDER BY n_orders DESC, customer_id; customer_id | n_orders | range_peers | groups_1back
-------------+----------+-------------+--------------
3549 | 29 | 1 | 1
2976 | 25 | 1 | 2
300 | 24 | 4 | 5
409 | 24 | 4 | 5
1820 | 24 | 4 | 5
1840 | 24 | 4 | 5
(6 rows)RANGE BETWEEN CURRENT ROW AND CURRENT ROW isn’t “just this row” — it’s this row’s whole peer group, so each of the four 24s reports range_peers = 4. GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW counts the current peer group plus the one before it. For the 24s that’s the four 24s plus the single 25, giving 5. You’ll reach for GROUPS rarely. But knowing it exists explains why RANGE and ROWS behave the way they do: they’re the two ends of the same idea, with GROUPS in between. All three are standard SQL and supported by Postgres; support in other databases varies (GROUPS in particular is newer and not everywhere).
Final thoughts
The frame is the window’s reach. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is a running total; a sliding ROWS BETWEEN n PRECEDING AND CURRENT ROW is a moving average; and the frame mode — ROWS, RANGE, or GROUPS — decides how ties are treated. The one fact worth pinning to the wall: an OVER (ORDER BY k) with no explicit frame defaults to RANGE. That folds tied rows together and turns a running total into a series of group totals. On unique keys you’ll never notice; on the day a duplicate appears, your numbers quietly inflate. Write ROWS and the surprise can’t happen. Next we use the window’s order differently — reaching to the specific row before or after this one, and pulling the top row out of each group.
Comments