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_endParameters
- ROWS | RANGE | GROUPSkeyword
- How the bounds are counted: ROWS in rows, RANGE by the ORDER BY value, GROUPS in groups of rows with equal ORDER BY values
- Values:ROWSRANGEGROUPS
- frame_startkeyword
- Start of the frame
- Values:UNBOUNDED PRECEDINGoffset PRECEDINGCURRENT ROWoffset FOLLOWING
- frame_endkeyword
- End of the frame, not before its start
- Values:offset PRECEDINGCURRENT ROWoffset FOLLOWINGUNBOUNDED FOLLOWING
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.
After the frame you can exclude rows: EXCLUDE CURRENT ROW, EXCLUDE GROUP or EXCLUDE TIES. The frame does not affect ranking functions, LAG and LEAD.