ROWS BETWEEN
Sets the window frame: the rows of the partition that the window function uses to compute the value for the current row.
{ROWS | RANGE | GROUPS} BETWEEN frame_start AND frame_end [frame_exclusion]- [ ]optional part
- { a | b }one of the options
- ROWS | RANGE | GROUPS
- Bound unit: rows, ORDER BY values or groups of equal rowsKeywordROWSRANGEGROUPS
- frame_start
- Start of the frameKeywordUNBOUNDED PRECEDINGoffset PRECEDINGCURRENT ROWoffset FOLLOWING
- frame_end
- End of the frame, not before its startKeywordoffset PRECEDINGCURRENT ROWoffset FOLLOWINGUNBOUNDED FOLLOWING
- frame_exclusion
- Which rows to exclude from the frame, none by defaultKeywordOptionalEXCLUDE CURRENT ROWEXCLUDE GROUPEXCLUDE TIESEXCLUDE NO OTHERS
Return value
Does not return a value: sets the rows for the window function inside OVER (...)
Examples
Sum of the last three payments
SELECT payment_id,
date,
unit_price,
SUM(unit_price) OVER (
ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS last_three
FROM Payments
ORDER BY date;Running total for each family member
SELECT family_member,
date,
unit_price,
SUM(unit_price) OVER (
PARTITION BY family_member
ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM Payments
ORDER BY family_member,
date;Sum over the last 30 days
SELECT date,
unit_price,
SUM(unit_price) OVER (
ORDER BY date RANGE BETWEEN INTERVAL '30 days' PRECEDING
AND CURRENT ROW
) AS last_30_days
FROM Payments
ORDER BY date;ROWS, RANGE and GROUPS with equal prices
SELECT unit_price,
COUNT(*) OVER (
ORDER BY unit_price ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS rows_frame,
COUNT(*) OVER (
ORDER BY unit_price RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS range_frame,
COUNT(*) OVER (
ORDER BY unit_price GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS groups_frame
FROM Payments
ORDER BY unit_price;Details
Bounds: UNBOUNDED PRECEDING is the start of the partition, offset PRECEDING is offset rows before the current one, CURRENT ROW is the current row, offset FOLLOWING is offset rows after it, and UNBOUNDED FOLLOWING is the end of the partition. For RANGE the offset is given in ORDER BY units, for example INTERVAL '30 days' for dates.
If no frame is given, with ORDER BY it goes from the start of the partition to the current row together with all rows that have the same ORDER BY value (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), and without ORDER BY it is the whole partition. That is why LAST_VALUE and NTH_VALUE without an explicit frame often return something unexpected.
EXCLUDE CURRENT ROW removes the current row from the frame, EXCLUDE GROUP removes it and the rows equal to it in ORDER BY, and EXCLUDE TIES removes only the equal rows. The frame does not affect ranking functions, LAG and LEAD.