Window Frames
A window frame specifies which rows to consider for the current row when evaluating the window function.
The frame comprises three pieces: unit, start bound, and end bound.The unit can be RANGE
or ROWS
, which defines how the bounds will work.
The bounds are:

UNBOUNDED PRECEDING

<expr> PRECEDING

CURRENT ROW

<expr> FOLLOWING

UNBOUNDED FOLLOWING

With
RANGE
, theORDER BY
should specify exactly one expression, and that expression should be of a numeric, date, time, or timestamp type.For<expr> PRECEDING
, expr is subtracted from theORDER BY
expression, and for<expr> FOLLOWING
, expr is added.ForCURRENT ROW
, the expression is used asis.All rows inside the current partition that are between the bounds are considered part of the resulting window frame.

With
ROWS
,ORDER BY
expressions are not limited by number or type.For this unit,<expr> PRECEDING
and<expr FOLLOWING
relate to the row position within the current partition, and not the values of the ordering keys.
Both UNBOUNDED PRECEDING
and UNBOUNDED FOLLOWING
work identical with RANGE
and ROWS
.UNBOUNDED PRECEDING
start at the first row of the current partition, and UNBOUNDED FOLLOWING
ends at the last row of the current partition.
The frame syntax with <windowframestart>
specifies the startframe, with the endframe being CURRENT ROW
.
Some window functions discard frames:

ROW_NUMBER
,LAG
andLEAD
always work asROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

DENSE_RANK
,RANK
,PERCENT_RANK
andCUME_DIST
always work asRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

FIRST_VALUE
,LAST_VALUE
andNTH_VALUE
respect frames, but theRANGE
unit behaviour is identical toROWS
.
Example Using Frame
When the ORDER BY
clause is used, but a frame clause is omitted, the default considers the partition up to the current row.When combined with SUM
, this results in a running total:
select
id,
salary,
sum(salary) over (order by salary) sum_salary
from employee
order by salary;
Result:
 id  salary  sum_salary 
:::
 3  8.00  8.00 
 4  9.00  17.00 
 1  10.00  37.00 
 5  10.00  37.00 
 2  12.00  49.00 
On the other hand, if we apply a frame for the entire partition, we get the total for the entire partition.
select
id,
salary,
sum(salary) over (
order by salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) sum_salary
from employee
order by salary;
Result:
 id  salary  sum_salary 
:::
 3  8.00  49.00 
 4  9.00  49.00 
 1  10.00  49.00 
 5  10.00  49.00 
 2  12.00  49.00 
This example is to demonstrate how this works;the result of this example would be simpler to produce with sum(salary) over()
.
We can use a range frame to compute the count of employees with salaries between (an employee’s salary  1) and (their salary + 1) with this query:
select
id,
salary,
count(*) over (
order by salary
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
) range_count
from employee
order by salary;
Result:
 id  salary  range_count 
:::
 3  8.00  2 
 4  9.00  4 
 1  10.00  3 
 5  10.00  3 
 2  12.00  1 