7.23.1 Specification format for window functions

Organization of this subsection

(1) Specification format
(2) Explanation of specification format

(1) Specification format

window-function ::= {RANK()
                   | DENSE_RANK()
                   | CUME_DIST()
                   | ROW_NUMBER()
                   | set-function} OVER(window-specification)
 
 
  window-specification ::= [window-partition-clause] [window-order-clause]
                           [window-frame-clause]
    window-partition-clause ::= PARTITION BY value-expression[,value-expression]...
    window-order-clause ::= ORDER BY sort-specification-list
    window-frame-clause ::= {ROWS | RANGE} {window-frame-start | window-frame-range}
 
  window-frame-start ::= {UNBOUNDED PRECEDING
                         |window-frame-value-specification PRECEDING
                         |CURRENT ROW}
  window-frame-range ::= BETWEEN window-frame-start-boundary
                                        AND window-frame-end-boundary
    window-frame-start-boundary ::= window-frame-boundary
    window-frame-end-boundary ::= window-frame-boundary
    window-frame-boundary ::= {UNBOUNDED PRECEDING
                              | window-frame-value-specification PRECEDING
                              | CURRENT ROW
                              | window-frame-value-specification FOLLOWING
                              | UNBOUNDED FOLLOWING}
      window-frame-value-specification ::= {unsigned-value-specification | labeled-duration}

To Page Top

(2) Explanation of specification format

(a) window-partition-clause

window-partition-clause ::= PARTITION BY value-expression[,value-expression]...

Partitions the results of the table expression using the results of value-expression. If window-partition-clause is omitted, the result will be a single window (partition) for the entire table expression.

The following figure gives a functional overview of the window partition clause:

Figure 7‒4: Functional overview of the window partition clause

The following rules apply:

You must specify a value expression that contains a column specification in the window partition clause.
No more than 16 value expressions can be specified in the window partition clause.
A column specified in a single column specification in the window partition clause cannot be specified again.
You cannot specify binary data for the value expressions in the window partition clause.

(b) window-order-clause

window-order-clause ::= ORDER BY sort -specification-list

Specify this to order (sort) the data in a window (partition). For details about the specification format and rules of the sort specification list, see 7.24 Sort specification list.

The following figure gives a functional overview of the window order clause.

Figure 7‒5: Functional overview of the window order clause

The following rules apply:

The data types that can be specified in the sort key of the sort specification list are shown in the following table.

Table 7‒28: Data types that can be specified in the sort key of the sort specification list of the window order clause
Window frame clause specification		Window frame value specification	Data type of sort key
Window frame clause specification		Window frame value specification	Numeric data	Character string data	Datetime data	Binary data
Specified	`ROWS`	--	Y	Y	Y	N
	`RANGE`	Specified	Y	N	Y	N
	`RANGE`	Not specified	Y	Y	Y	N
Not specified		--	Y	Y	Y	N

Legend:

Y: Can be specified.

N: Cannot be specified.

--: Not applicable.

If a dynamic parameter is specified by itself for the sort key of the sort specification list, the assumed data type of the dynamic parameter is INTEGER.
You cannot specify a window order clause when using a DISTINCT set function or inverse distribution function as the window function.
In order to specify RANK, DENSE_RANK, or CUME_DIST, you must specify a window order clause in the window specification.
If you specify RANGE in the window frame clause and a window frame value specification in the window frame boundary, no more than one sort specification is permitted in the sort specification list in the window order clause.

(c) window-frame-clause

window-frame-clause ::= {ROWS | RANGE} {window-frame-start | window-frame-range}

Specifies a window frame to serve as the aggregation range for the window function.

The following figure gives a functional overview of the window frame clause.

Figure 7‒6: Functional overview of the window frame clause

If ROWS is specified in the window frame clause, a physical row-by-row window frame is used. If RANGE is specified, the window frame is implemented as a logical offset (a logical interval such as a datetime).

When window-frame-clause is omitted, the range of the window frame will be as follows.

When a window order clause is specified

The range of the window frame is equivalent to specifying the following window frame range:
```
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
```
The range of the window function will extend from the first row of the window (partition) to the current row. However, because RANGE is assumed, later rows with the same sort key value as the current row will also be included in the aggregation range.
When a window order clause is not specified

The range of the window function will be the window (partition) containing the current row.

The following rules apply:

If a window frame clause is specified, either COUNT(*) or a general set function (excluding DISTINCT set functions) must be used as the window function.
If you specify a window frame clause other than one of the following that represents all windows (partitions), you must specify a window order clause:
- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
- RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
If RANK, DENSE_RANK, CUME_DIST, or ROW_NUMBER is specified, you cannot specify a window frame clause in the window specification.
If window-frame-start is specified in the window frame clause, the range of the window frame is equivalent to the following:
```
BETWEEN window-frame-start AND CURRENT ROW
```
If a window frame range is specified in the window frame clause, the upper boundary of the window frame is set to window-frame-start-boundary, and the lower boundary of the window frame is set to window-frame-end-boundary.

The window frame boundary can be set to one of the following.

UNBOUNDED PRECEDING:

The window frame will start from the first row in the window (partition). UNBOUNDED PRECEDING can be set for window-frame-start-boundary.
UNBOUNDED FOLLOWING:

The window frame will end at the last row in the window (partition). UNBOUNDED FOLLOWING can be set for window-frame-end-boundary.
CURRENT ROW:

• If ROWS is specified:

If it is specified in window-frame-start-boundary, the window frame will start from the current row. If it is specified in window-frame-end-boundary, the window frame will end at the current row.

• If RANGE is specified:

If it is specified in window-frame-start-boundary, the window frame will start from the first row with the same sort key value as the current row. If it is specified in window-frame-end-boundary, the window frame will end at the last row with the same sort key value as the current row.

window-frame-value-specification PRECEDING or window-frame-value-specification FOLLOWING

• If ROWS is specified:

The value of window-frame-value-specification is a physical row offset from the current row. The data type of unsigned-value-specification must be INTEGER. You cannot specify a labeled duration.

• If RANGE is specified:

The value of window-frame-value-specification is a logical offset from the sort key value of the current row. The following table shows the data type of the sort key specified in the window order clause, and the unsigned value specification or labeled duration that can be specified.

Table 7‒29: Data type of the sort key specified in the window order clause, and the unsigned value specification or labeled duration that can be specified (when RANGE is specified)
Data type of the sort key specified in window-order-clause	Unsigned value specification or labeled duration that can be specified
Numeric data	An unsigned value specification consisting of numeric data
`DATE`	Labeled duration (`YEARS`, `MONTHS`, `DAYS`)
`TIME`	Labeled duration (`HOURS`, `MINUTES`, `SECONDS`, `MILLISECONDS`, `MICROSECONDS`, `NANOSECONDS`, `PICOSECONDS`)
`TIMESTAMP`	Labeled duration (`YEARS`, `MONTHS`, `DAYS`, `HOURS`, `MINUTES`, `SECONDS`, `MILLISECONDS`, `MICROSECONDS`, `NANOSECONDS`, `PICOSECONDS`)

Certain combinations of window frame boundaries cannot be specified in the window frame range. For example:

UNBOUNDED FOLLOWING is never permitted for window-frame-start-boundary.
UNBOUNDED PRECEDING is never permitted for window-frame-end-boundary.

The following table shows the combinations that can be specified:

Table 7‒30: Combinations that can be specified
Window frame start boundary specification	Window frame end boundary specification
Window frame start boundary specification	UNBOUNDED FOLLOWING	CURRENT ROW	window-frame-value-specification PRECEDING	window-frame-value-specification FOLLOWING
`UNBOUNDED` `PRECEDING`	Y	Y	Y	Y
`CURRENT` `ROW`	Y	Y	N	Y
`window-frame-value-specification` `PRECEDING`	Y	Y	Y	Y
`window-frame-value-specification` `FOLLOWING`	Y	N	N	Y

Legend:

Y: Can be specified.

N: Cannot be specified.

When you specify a labeled duration for window-frame-value-specification in the window frame range, the same labeled duration qualifier must be used for both window-frame-start-boundary and window-frame-end-boundary. For details about labeled duration qualifiers, see 7.28.1 Specification format and rules for labeled durations.

Example:
```
BETWEEN 2 DAYS PRECEDING AND 1 DAYS PRECEDING
```
When you specify a labeled duration for window-frame-value-specification, only a value specification can be specified for value-expression-primary in the labeled duration.
When you specify a labeled duration for window-frame-value-specification, the following value ranges are permitted:

YEARS: 0 to 9,998

MONTHS: 0 to 119,987

DAYS: 0 to 3,652,058

HOURS: 0 to 87,649,415

MINUTES: 0 to 5,258,964,959

SECONDS: 0 to 315,537,897,599

MILLISECONDS: 0 to 315,537,897,599,999

MICROSECONDS: 0 to 315,537,897,599,999,999

NANOSECONDS: 0 to 9,223,372,036,854,775,807

PICOSECONDS: 0 to 9,223,372,036,854,775,807
An error results if you specify a negative value or null value for window-frame-value-specification.

The following table shows which data type is assumed when a dynamic parameter is specified for window-frame-value-specification.

Table 7‒31: Assumed data type when the window frame value specification is a dynamic parameter
Window frame specification	Data type of the sort key in the window order clause	Assumed data type of the window frame value specification
`RANGE`	`SMALLINT`	`SMALLINT`
	`INTEGER`	`INTEGER`
	`DECIMAL`	`DECIMAL`
	`DOUBLE` `PRECISION`	`DOUBLE` `PRECISION`
	`DATE`	-- (Only a labeled duration is permitted)
	`TIME`
	`TIMESTAMP`
`ROWS`	--	`INTEGER`

Legend:: --: Not applicable.

To Page Top