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).
SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
{ 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 |
-- 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
ROWSfor moving windows. It is exact, predictable and usually fastest. UseRANGEwhen you really mean "values within 10 of this one" andGROUPSfor "the last 3 distinct values". RANGEincludes 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 useROWSor add a unique tie-breaker to theORDER BY.- Ranking and offset functions ignore frames.
ROW_NUMBER(),RANK(),NTILE(),LAG()andLEAD()always work on the whole partition. Frames affect aggregates andFIRST_VALUE,LAST_VALUEandNTH_VALUE. - Incomplete frames at the edges.
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWon 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. RANGEwith offsets requires exactly one numeric (or date-like)ORDER BYexpression, and the order direction flips what "preceding" means.- Frame offsets are constants in most databases (for example
2, not a column). EXCLUDE CURRENT ROWis handy to compare a row against its neighbours without including itself.