Reference for window functions
Some functions support an optional frame_clause
.
The frame_clause
can be one of the following:
where <frame_start>
and <frame_end>
is one of the following:
The frame_clause
specifies the set of rows constituting the window frame within the current partition. The frame can be specified in RANGE
or ROWS
mode; in each case, the frame runs from the <frame_start>
to the <frame_end>
. If <frame_end>
is omitted, the end defaults to CURRENT ROW
.
Usage
The default framing option is RANGE UNBOUNDED PRECEDING
, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
. With an ORDER BY
clause, this sets the frame to be all rows from the partition start through the current row’s last ORDER BY
peer. Without an ORDER BY
clause, all rows of the partition are included in the window frame, since all rows become peers of the current row.
The number of rows to the end of the frame is limited by the number of rows to the end of the partition; for rows near the partition ends, the frame might contain fewer rows than elsewhere.
UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING
<frame_start>
of UNBOUNDED PRECEDING
means that the frame starts with the first row of the partition. Similarly a <frame_end>
of UNBOUNDED FOLLOWING
means that the frame ends with the last row of the partition.CURRENT ROW
In RANGE
mode, a <frame_start>
of CURRENT ROW
means the frame starts with the current row’s first peer row (a row that the window’s ORDER BY
clause sorts as equivalent to the current row), while a <frame_end>
of CURRENT ROW
means the frame ends with the current row’s last peer row.
In ROWS
mode, CURRENT ROW
simply means the current row.
offset PRECEDING, offset FOLLOWING
offset PRECEDING
and offset FOLLOWING
frame options, the offset must be an expression not containing any variables, aggregate functions, or window functions. The meaning of the offset depends on the frame mode:
In ROWS
mode, the offset must yield a non-null, non-negative integer, and the option means that the frame starts or ends the specified number of rows before or after the current row.
In RANGE
mode, these options require that the ORDER BY
clause specify exactly one column. The offset specifies the maximum difference between the value of that column in the current row and its value in preceding or following rows of the frame. The data type of the offset expression varies depending on the data type of the ordering column. For numeric ordering columns it is typically of the same type as the ordering column. The offset is still required to be non-null and non-negative, though the meaning of “non-negative” depends on the data type.
In ROWS
mode, 0 PRECEDING
and 0 FOLLOWING
are equivalent to CURRENT ROW
. This normally holds in RANGE
mode as well, for an appropriate data-type-specific meaning of “zero”.
Restrictions
frame_start
cannot be UNBOUNDED FOLLOWING
frame_end
cannot be UNBOUNDED PRECEDING
frame_end
cannot appear earlier in the above list of frame_start
and frame_end
options than the frame_start
choice does.
For example RANGE BETWEEN CURRENT ROW AND offset PRECEDING
is not allowed, but ROWS BETWEEN 7 PRECEDING AND 8 PRECEDING
is allowed, even though it would never select any rows.Example
The example below is querying test scores for students in various grade levels. Unlike a regular AVG()
aggregation, the window function allows us to see how each student individually compares to the average test score for their grade level, as well as compute the average test score while looking at different slices of the data for different grade levels – narrowing down the set of rows that constitutes the window using framing options such as PRECEDING or FOLLOWING.
Returns: