Frame clauses: ROWS BETWEEN ... vs RANGE BETWEEN ...

The frame clause defines which rows around the current row the window function sees — and ROWS, RANGE, and GROUPS count those rows differently when there are ties. Misunderstanding the frame is the #1 source of "my running total is wrong" bugs.

The frame, anchored to the current row

A frame is written inside OVER (...) after the ORDER BY:

SUM(amount) OVER (
  ORDER BY created_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total

Bounds: UNBOUNDED PRECEDING, N PRECEDING, CURRENT ROW, N FOLLOWING, UNBOUNDED FOLLOWING.

ROWS — physical row count

ROWS counts literal rows. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is exactly three physical rows (or fewer at the edges) — a true moving window, ideal for a 3-row moving average:

AVG(price) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

RANGE — logical value range

RANGE groups rows by their ORDER BY value. RANGE BETWEEN CURRENT ROW AND CURRENT ROW includes all peer rows that share the current row's ordering value, not just the physical current row. So with ties, a RANGE ... CURRENT ROW running total jumps to the same total for every tied row.

day        amount   ROWS sum   RANGE sum   (frame = ... CURRENT ROW)
2026-01-01   10        10         10
2026-01-02   20        30         50   <- both Jan-02 rows summed
2026-01-02   20        50         50      because they're peers

RANGE also supports value offsets when ordering by a number/date: RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW sums the last 7 days regardless of how many rows that is.

GROUPS — peer-group count

GROUPS (PostgreSQL 11+) counts distinct ordering-value groups. GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW means "the current peer group plus the one peer group before it," however many physical rows each contains.