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 one ORDER BY column in the window definition. If PRECEDING is set, the frame contains the rows that the ordering column value is less than <offset>. If FOLLOWING 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.