## 10.4 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:

`<expr> PRECEDING`

`<expr> FOLLOWING`

`CURRENT ROW`

With

`RANGE`

, the`ORDER 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 the`ORDER BY`

expression, and for`<expr> FOLLOWING`

,*expr*is added. For`CURRENT ROW`

, the expression is used as-is.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`

the last row of the current partition.

The frame syntax with `<window_frame_start> specifies the start-frame, with the end-frame being `CURRENT ROW`

.

Some window functions discard frames:

`ROW_NUMBER`

,`LAG`

and`LEAD`

always work as`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`

`DENSE_RANK`

,`RANK`

,`PERCENT_RANK`

and`CUME_DIST`

always work as`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`

`FIRST_VALUE`

,`LAST_VALUE`

and`NTH_VALUE`

respect frames, but the`RANGE`

unit behaviour is identical to`ROWS`

.

##### 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 just to demonstrate how this works;
the result of this specific example would be simpler to produce with just `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 |`