Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Window Functions

SQL window frames: ROWS, RANGE and GROUPS

Reference for SQL window frames: ROWS vs RANGE vs GROUPS, UNBOUNDED PRECEDING, CURRENT ROW, offsets, EXCLUDE, the default frame and examples for moving averages.

A window frame is the set of rows, inside the current partition, that an aggregate or value function looks at for the current row. It is the last, optional part of the OVER() clause. Frames are what turn SUM() into a running total, AVG() into a moving average and LAST_VALUE() into something that sees the whole group.

A frame has a unit (ROWS, RANGE or GROUPS), a start and an end. The unit decides how positions are counted. ROWS counts physical rows, RANGE counts by sort value, and GROUPS counts groups of peer rows (rows with equal sort values).

SQL
SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Try it

SQL ROWS: a three-row moving sum ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL ROWS, RANGE and GROUPS with tied values ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

SQL RANGE by value and EXCLUDE CURRENT ROW ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
{ ROWS | RANGE | GROUPS } BETWEEN frame_start AND frame_end
[ EXCLUDE { NO OTHERS | CURRENT ROW | GROUP | TIES } ]

-- frame_start / frame_end:
UNBOUNDED PRECEDING
n PRECEDING
CURRENT ROW
n FOLLOWING
UNBOUNDED FOLLOWING
Part Description
ROWS Boundaries are counted in physical rows. 2 PRECEDING is exactly two rows back.
RANGE Boundaries are values: 10 PRECEDING means rows whose sort value is at most 10 below the current one. CURRENT ROW includes all peers. Needs a single numeric or date ORDER BY column when offsets are used.
GROUPS Boundaries count groups of peers. 1 PRECEDING is the previous distinct sort value, with all its rows.
UNBOUNDED PRECEDING / FOLLOWING The first or last row of the partition.
EXCLUDE Removes the current row, its peers, or only the peers' ties from the frame. NO OTHERS is the default.

The start may not come after the end. A short form, ROWS 2 PRECEDING, means BETWEEN 2 PRECEDING AND CURRENT ROW.

Defaults: with ORDER BY the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; without ORDER BY it is the whole partition.

Dialect differences

Feature SQLite PostgreSQL MySQL SQL Server Oracle
ROWS frames 3.25+ Yes 8.0+ 2012+ Yes
RANGE with offsets (10 PRECEDING) 3.25+ 11+ 8.0+ Only UNBOUNDED/CURRENT ROW Yes
GROUPS 3.28+ 11+ No No No
EXCLUDE 3.28+ 11+ No No No
Interval offsets for dates No (numbers only) Yes Yes No Yes
SQL
-- PostgreSQL: moving sum over the last 7 days (date-based RANGE)
SELECT SUM(amount) OVER (ORDER BY paid_on RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)
FROM payments;

-- MySQL, Oracle also accept INTERVAL offsets; SQLite does not (use a numeric day column)

Usage notes

  • Prefer ROWS for moving windows. It is exact, predictable and usually fastest. Use RANGE when you really mean "values within 10 of this one" and GROUPS for "the last 3 distinct values".
  • RANGE includes peers. With the default frame, rows with equal sort values all see the same result. If you need a strict row-by-row running total, either use ROWS or add a unique tie-breaker to the ORDER BY.
  • Ranking and offset functions ignore frames. ROW_NUMBER(), RANK(), NTILE(), LAG() and LEAD() always work on the whole partition. Frames affect aggregates and FIRST_VALUE, LAST_VALUE and NTH_VALUE.
  • Incomplete frames at the edges. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW on the first row contains only one row, so a three-row average of the first rows uses fewer than three values. Mask them with a row number if you need full windows.
  • RANGE with offsets requires exactly one numeric (or date-like) ORDER BY expression, and the order direction flips what "preceding" means.
  • Frame offsets are constants in most databases (for example 2, not a column).
  • EXCLUDE CURRENT ROW is handy to compare a row against its neighbours without including itself.