Window frame clause
Applies to: Databricks SQL Databricks Runtime
Specifies a sliding subset of rows within the partition on which the aggregate or analytic window function operates.
Syntax
{ frame_mode frame_start |
frame_mode BETWEEN frame_start AND frame_end } }
frame_mode
{ RANGE | ROWS }
frame_start
{ UNBOUNDED PRECEDING |
offset_start PRECEDING |
CURRENT ROW |
offset_start FOLLOWING }
frame_end
{ offset_stop PRECEDING |
CURRENT ROW |
offset_stop FOLLOWING |
UNBOUNDED FOLLOWING }
Parameters
frame_mode
ROWS
If specified, the sliding window frame is expressed in terms of rows preceding or following the current row.
RANGE
If specified, the window function must specify an ORDER BY clause with a single expression
obExpr
.The boundaries of the sliding window are then expressed as an offset from the
obExpr
for the current row.
frame_start
The starting position of the sliding window frame relative to the current row.
UNBOUNDED PRECEDING
Specifies that the window frame starts at the beginning of partition.
offset_start PRECEDING
If the mode is
ROWS
,offset_start
is the positive integral literal number defining how many rows prior to the current row the frame starts.If the mode is
RANGE
,offset_start
is a positive literal value of a type which can be subtracted fromobExpr
. The frame starts at the first row of the partition for whichobExpr
is greater or equal toobExpr - offset_start
at the current row.CURRENT ROW
Specifies that the frame starts at the current row.
offset_start FOLLOWING
If the mode is
ROWS
,offset_start
is the positive integral literal number defining how many rows past to the current row the frame starts. If the mode isRANGE
,offset_start
is a positive literal value of a type which can be added toobExpr
. The frame starts at the first row of the partition for whichobExpr
is greater or equal toobExpr + offset_start
at the current row.
frame_stop
The end of the sliding window frame relative to the current row.
If not specified, the frame stops at the CURRENT ROW. The end of the sliding window must be greater than the start of the window frame.
offset_start PRECEDING
If frame_mode is
ROWS
,offset_stop
is the positive integral literal number defining how many rows prior to the current row the frame stops. If frame_mode isRANGE
,offset_stop
is a positive literal value of the same type asoffset_start
. The frame ends at the last row off the partition for whichobExpr
is less than or equal toobExpr - offset_stop
at the current row.CURRENT ROW
Specifies that the frame stops at the current row.
offsetStop FOLLOWING
If frame_mode is
ROWS
,offset_stop
is the positive integral literal number defining how many rows past to the current row the frame ends. If frame_mode isRANGE
,offset_stop
is a positive literal value of the same type asoffset_start
. The frame ends at the last row of the partition for whichobExpr
is less than or equal toobExpr + offset_stop
at the current row.UNBOUNDED FOLLOWING
Specifies that the window frame stops at the end of the partition.