WINDOW
Clause
The WINDOW
clause returns a partition of the data returned by the select command. Window functions reference the WINDOW
clause in their OVER
clauses. For more information about window functions, see Window Functions and Queries.
To copy an existing WINDOW
, provide an <existing_window_name>
that is an earlier entry in the WINDOW
list. The new window copies its partitioning and ordering clauses from the existing window. The new window can’t specify its own PARTITION BY
clause, but it can specify ORDER BY
if the existing window doesn’t have one. The new window uses the copied frame clause.
The parts of the PARTITION BY
list are similar to parts of a GROUP BY Clause, but they’re simple expressions and aren’t an output column. The PARTITION BY
can contain aggregate function calls.
The parts of the ORDER BY
list are similar to parts of an ORDER BY Clause, but they’re simple expressions and aren’t an output column.
The optional <frame_clause>
is a set of related rows for each row of the query.
If you define the <frame_start>
but not the <frame_end>
, <frame_end>
defaults to CURRENT ROW
. The <frame_start>
can’t be UNBOUNDED FOLLOWING
and <frame_end>
can’t be UNBOUNDED PRECEDING
.
The default framing option is RANGE UNBOUNDED PRECEDING
, which sets the frame to be all rows from the partition start through the current row’s last peer. The row’s last peer is a row that the window’s ORDER BY
clause considers the current row. If there’s no ORDER BY
clause, all rows are peers. Generally, UNBOUNDED PRECEDING
means that the frame starts with the first row of the partition, and UNBOUNDED FOLLOWING
means the frame ends with the last row of the partition. UNBOUNDED PRECEDING
and UNBOUNDED FOLLOWING
ignore the defined RANGE
or ROWS
mode.
In ROWS
mode, CURRENT ROW
means that the frame starts or ends with the current rows. In RANGE
mode, CURRENT ROW
means that the frame starts or ends with the current row’s first or last peer in the ORDER BY
ordering.
The <offset> PRECEDING
and <offset> FOLLOWING
definitions depend on the frame mode.
- In
ROWS
mode, the<offset>
is an integer indicating that the frame starts or ends that many rows before or after the current row. - In
RANGE
mode, the<offset>
option requires oneORDER BY
column in the window definition. IfPRECEDING
is set, the frame contains the rows that the ordering column value is less than<offset>
. IfFOLLOWING
is set, the current row’s ordering column value is more than<offset>
.
The data type of the <offset>
expression depends on the data type of the ordering column. For datetime ordering columns, the offset is an interval
. The value of the <offset>
is non-null and non-negative. The <offset>
can’t contain variables, aggregate functions, or window functions.
The <frame_exclusion>
option excludes rows around the current row from the frame, even if they’re included in the frame start and frame end options.
EXCLUDE CURRENT ROW
excludes the current row from the frame.EXCLUDE GROUP
excludes the current row and its ordering peers from the frame.EXCLUDE TIES
excludes any peers of the current row from the frame, but not the current row itself.EXCLUDE NO OTHERS
doesn’t exclude the current row or its peers.
If rows aren’t ordered uniquely in the ORDER BY
ordering, ROWS
mode can produce unpredictable results. The RANGE
mode ensures that rows that are peers in the ORDER BY
ordering are in the frame or excluded from it.
The optional WINDOW
is written as:
<window_name>
is a name that can be referenced from OVER
clauses or subsequent window definitions, and the <window_definition>
is defined as:
This example shows the possible <frame_clause>
syntax.
With <frame_start>
and <frame_end>
defined in this sample.
The <frame_exclusion>
syntax is provided in this sample.