BrevFeed
Sign in

31. Window frames: ROWS vs RANGE

📖 Reading · 9 min
💡 Most code boxes below are live — edit one and hit Run. Boxes without a Run button are reference-only (they need a real Postgres server).

The default frame you've been using without naming it

Every window function in this module so far has implicitly used a default frame. A frame is the sub-window of rows, within the current partition, that a window function actually looks at for the current row — up to now, that's silently been "the whole partition." This lesson makes the frame explicit and shows why controlling it directly unlocks running totals and moving averages.

The frame clause, explicitly

<window function> OVER (
    PARTITION BY ...
    ORDER BY ...
    <frame_type> BETWEEN <start> AND <end>
)

<frame_type> is ROWS, RANGE, or GROUPS (this course covers ROWS and RANGE — GROUPS is a less-common third option for tie-aware row-counting, worth knowing exists but rarely reached for). <start> and <end> are typically one of: UNBOUNDED PRECEDING, N PRECEDING, CURRENT ROW, N FOLLOWING, UNBOUNDED FOLLOWING.

ROWS: a literal count of physical rows

-- Running total of rental_rate, ordered by film_id: for each row, sum
-- from the very first row of the partition up through the current row.
SELECT film_id, title, rental_rate,
    sum(rental_rate) OVER (
        ORDER BY film_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM film
ORDER BY film_id
LIMIT 10;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — "from the start of the partition through the current row" — is close to the actual default frame when ORDER BY is present and no frame is specified explicitly, but the real default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, not ROWS — a distinction that matters the moment the ORDER BY column has ties (see the next section for exactly what changes). When the ordering column is unique per row (like film_id in the example above), RANGE and ROWS happen to produce identical results, which is why this distinction is easy to miss until it isn't. (The genuinely-whole-partition default only applies when there's no ORDER BY in the OVER() clause at all — worth being precise about, since it explains why adding an ORDER BY to an existing OVER() can silently change an aggregate's result — a real gotcha worth testing yourself.)

A moving average — a fixed-size sliding window instead of "from the start" — is the other extremely common ROWS pattern:

-- 3-row moving average of rental_rate: current row plus the 2 preceding.
SELECT film_id, title, rental_rate,
    avg(rental_rate) OVER (
        ORDER BY film_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM film
ORDER BY film_id
LIMIT 10;

RANGE: based on value, not row position

RANGE looks similar but means something meaningfully different: instead of counting a fixed number of rows, it includes every row whose ORDER BY value falls within a value-based distance of the current row's value — rows that tie on the ordering value are always included or excluded together, never split apart the way ROWS might split them. This is exactly why the standard default frame is RANGE-based: a "peer group" of tied rows is treated as a single unit, so CURRENT ROW in a RANGE frame actually means "through the end of the current row's entire peer group," not "stop at this exact physical row" — a subtlety that resurfaces as a genuine trap with LAST_VALUE in the next lesson.

-- All films within the SAME rental_rate as the current row are treated
-- as one range-frame group — ties move together, unlike ROWS.
SELECT film_id, title, rental_rate,
    count(*) OVER (
        ORDER BY rental_rate
        RANGE BETWEEN CURRENT ROW AND CURRENT ROW
    ) AS films_at_this_exact_rate
FROM film
ORDER BY rental_rate, film_id
LIMIT 10;

In practice, ROWS is what you reach for the overwhelming majority of the time (running totals, moving averages, "last N rows") — RANGE matters specifically when ties in the ordering column should be treated as a single unit rather than arbitrarily split, which is a narrower, less common need. Default to ROWS unless you have a specific reason to reach for RANGE.

The gotcha: default frame changes when you add ORDER BY

-- No ORDER BY in OVER(): default frame is the WHOLE partition — every
-- row gets the SAME total (the grand total), same as the window-basics lesson's examples.
SELECT film_id, rental_rate,
    sum(rental_rate) OVER () AS total
FROM film
ORDER BY film_id
LIMIT 5;

-- ADD an ORDER BY to the exact same OVER() clause, nothing else changes,
-- and the default frame silently becomes "start of partition through
-- current row" — this is now a running total, a completely different
-- computation, from what looks like a small edit.
SELECT film_id, rental_rate,
    sum(rental_rate) OVER (ORDER BY film_id) AS total
FROM film
ORDER BY film_id
LIMIT 5;

This is a genuinely common real-world bug: someone adds an ORDER BY to a window clause (perhaps to make ROW_NUMBER() work correctly alongside an existing SUM(...) OVER (...) in the same query) and unintentionally turns a grand-total column into a running-total column. The fix, when you want a whole-partition aggregate and need ORDER BY present for a different window function in the same SELECT list, is to specify the frame explicitly rather than relying on the default:

SELECT film_id, rental_rate,
    row_number() OVER (ORDER BY film_id) AS rn,
    sum(rental_rate) OVER (
        ORDER BY film_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS true_grand_total
FROM film
ORDER BY film_id
LIMIT 5;

Check yourself

  1. What frame does ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW describe, in plain language?
  2. Why does adding ORDER BY to an OVER() clause silently change the default frame, and what's the practical consequence if you didn't intend that?
  3. When would you reach for RANGE instead of ROWS?
← Previous Next →