7.23.1 Specification format for window functions
- Organization of this subsection
(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}
(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:
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.
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
Numeric data
Character string data
Datetime data
Binary data
Specified
ROWS
--
Y
Y
Y
N
RANGE
Specified
Y
N
Y
N
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.
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
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.