Window Frame (ROWS/RANGE)
SQL · Window Functions
Theory
A frame narrows the window to a slice of the partition measured from the current row. You write it after ORDER BY, for example ROWS BETWEEN 2 PRECEDING AND CURRENT ROW (this row and the two before it, a 3-row moving window) or ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (the whole partition). The boundary words are UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING and UNBOUNDED FOLLOWING.
ROWS counts physical rows. RANGE works on values of the ORDER BY column, so all rows with the same value as the current row are 'peers' and are included together. When you write ORDER BY without any frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That is why two orders on the same date both show the same running total under the default, while ROWS gives each its own step. Frames apply to aggregates and FIRST_VALUE/LAST_VALUE/NTH_VALUE; ranking functions and LAG/LEAD ignore them.
Why we use it
Frames let you answer 'last 3 months', 'everything so far' or 'the whole group' with the same function. They turn SUM and AVG into running totals and moving averages without any self-joins.
When to use it
Use ROWS for moving averages and exact row-by-row running totals. Use RANGE when tied values should be treated as one step (or for value ranges where supported). Always write the frame explicitly when results with ties or LAST_VALUE look odd.
How it works
- Partition is sorted by the window ORDER BY
- Take the current row
- Frame type ROWS? Yes: Count n physical rows around this row. No: RANGE: include equal-value peers.
- Collect the rows inside the frame boundaries
- Apply the function to only those rows
- Move to the next row and slide the frame
Syntax
function() OVER (
ORDER BY column
ROWS BETWEEN n PRECEDING AND CURRENT ROW
)Example
SELECT name, salary,
SUM(salary) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS running_2
FROM employees;Key points
- ROWS counts physical rows; RANGE groups rows with equal ORDER BY values
- Default with ORDER BY is RANGE UNBOUNDED PRECEDING to CURRENT ROW
- Without ORDER BY the frame is the whole partition
- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = 3-row moving window
- Ranking functions and LAG/LEAD ignore the frame