Window Frame Clauses

Every window function operates over a set of rows related to the current row, called the window frame. PARTITION BY decides which rows are eligible (the partition), ORDER BY decides their sequence, and the frame clause — ROWS BETWEEN ... AND ... or RANGE BETWEEN ... AND ... — decides exactly which rows within that ordered partition are included for each calculation.

Frame boundaries

A frame is defined by a starting boundary and an ending boundary, joined with BETWEEN ... AND. The available boundary keywords are:

UNBOUNDED PRECEDING — start (or end) at the very first row of the partition.
n PRECEDING — n rows before the current row.
CURRENT ROW — the row currently being evaluated.
n FOLLOWING — n rows after the current row.
UNBOUNDED FOLLOWING — end at the very last row of the partition.

SQL

SUM(amount) OVER (
  ORDER BY sale_date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS total_last_3_days

Common frame patterns

Frame clause	Meaning
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	From the start of the partition through the current row — a running total.
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	The entire partition, for every row — useful for LAST_VALUE, or a grand total shown next to each row.
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW	The current row plus the 6 before it — a 7-row moving average/sum.
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING	A centered window of 7 rows — 3 before, the current row, and 3 after.
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING	Everything after the current row, excluding the current row itself.
(no frame clause, ORDER BY present)	Defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

ROWS vs RANGE

ROWS counts physical rows — n PRECEDING always means exactly n rows back, regardless of their values. RANGE instead works on the logical value of the ORDER BY expression: n PRECEDING under RANGE means "all rows whose ORDER BY value is within n of the current row's value," which can include more or fewer physical rows than expected when ties exist.

SQL

-- ROWS: exactly the current row and the row directly before it (2 rows)
SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

-- RANGE: every row tied with the current row's ORDER BY value is included together
SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)

Note

In practice, ROWS is used far more often than RANGE, because its behavior is easier to predict: it always refers to a fixed count of physical rows. RANGE is mostly useful for gap-and-island style logic where ties in the ORDER BY value should genuinely be treated as one logical group.

Worked example

Given daily temperature readings:

SQL

reading_date | temp_c
-------------+-------
2024-06-01   | 21
2024-06-02   | 23
2024-06-03   | 19
2024-06-04   | 25
2024-06-05   | 22

SQL

SELECT
  reading_date,
  temp_c,
  AVG(temp_c) OVER (
    ORDER BY reading_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS centered_3day_avg
FROM temperatures;

reading_date | temp_c | centered_3day_avg
-------------+--------+------------------
2024-06-01   | 21     | 22.00
2024-06-02   | 23     | 21.00
2024-06-03   | 19     | 22.33
2024-06-04   | 25     | 22.00
2024-06-05   | 22     | 23.50

For 2024-06-03, the centered average covers 06-02, 06-03, and 06-04 — (23 + 19 + 25) / 3 = 22.33. At the edges of the partition, the frame simply shrinks to whatever rows are actually available, e.g. 2024-06-01 only has itself and the following row to average.

Tip

If you forget the frame clause entirely but include ORDER BY, most databases silently apply RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which is exactly right for running totals but is the classic cause of the LAST_VALUE() surprise, since it excludes rows after the current one.