Window functions
Reference for window functions
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 asRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With anORDER BYclause, this sets the frame to be all rows from the partition start through the current row’s lastORDER BYpeer. Without anORDER BYclause, 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
- A
<frame_start>ofUNBOUNDED PRECEDINGmeans that the frame starts with the first row of the partition. Similarly a<frame_end>ofUNBOUNDED FOLLOWINGmeans that the frame ends with the last row of the partition.
CURRENT ROW
-
In
RANGEmode, a<frame_start>ofCURRENT ROWmeans the frame starts with the current row’s first peer row (a row that the window’sORDER BYclause sorts as equivalent to the current row), while a<frame_end>ofCURRENT ROWmeans the frame ends with the current row’s last peer row. -
In
ROWSmode,CURRENT ROWsimply means the current row.
offset PRECEDING, offset FOLLOWING
- For the
offset PRECEDINGandoffset FOLLOWINGframe 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
ROWSmode, 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
RANGEmode, these options require that theORDER BYclause 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
ROWSmode,0 PRECEDINGand0 FOLLOWINGare equivalent toCURRENT ROW. This normally holds inRANGEmode as well, for an appropriate data-type-specific meaning of “zero”.
-
Restrictions
frame_startcannot beUNBOUNDED FOLLOWINGframe_endcannot beUNBOUNDED PRECEDINGframe_endcannot appear earlier in the above list offrame_startandframe_endoptions than theframe_startchoice does. For exampleRANGE BETWEEN CURRENT ROW AND offset PRECEDINGis not allowed, butROWS BETWEEN 7 PRECEDING AND 8 PRECEDINGis 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: