{ AVG ( value_expression ) |
CORR ( value_expression_1, value_expression_2 ) |
COUNT ( { value_expression | * } ) |
COVAR_POP ( value_expression_1, value_expression_2 ) |
COVAR_SAMP ( value_expression_1, value_expression_2 ) |
MAX ( value_expression ) |
MIN ( value_expression ) |
REGR_AVGX ( dependent_variable_expression, independent_variable_expression ) |
REGR_AVGY ( dependent_variable_expression, independent_variable_expression ) |
REGR_COUNT ( dependent_variable_expression, independent_variable_expression ) |
REGR_INTERCEPT ( dependent_variable_expression, independent_variable_expression ) |
REGR_R2 ( dependent_variable_expression, independent_variable_expression ) |
REGR_SLOPE ( dependent_variable_expression, independent_variable_expression ) |
REGR_SXX ( dependent_variable_expression, independent_variable_expression ) |
REGR_SXY ( dependent_variable_expression, independent_variable_expression ) |
REGR_SYY ( dependent_variable_expression, independent_variable_expression ) |
STDDEV_POP ( value_expression ) |
STDDEV_SAMP ( value_expression ) |
VAR_POP ( value_expression ) |
VAR_SAMP ( value_expression )
} window
Syntax Elements
-
window
-
OVER ( [ partition_by_clause ] [ order_by_clause ] [ rows_clause ] )
-
partition_by_clause
-
PARTITION BY column_reference [,...]
- In its column_reference, or comma-separated list of column references, the group, or groups, over which the function operates.
- PARTITION BY is optional. If there are no PARTITION BY or RESET WHEN clauses, then the entire result set, delivered by the FROM clause, constitutes a single group, or partition.
- PARTITION BY clause is also called the window partition clause.
-
order_by_clause
-
ORDER BY value_specification [,...] [ RESET WHEN condition ]
- In its value_expression the order in which the values in a group, or partition, are sorted.
-
rows_clause
-
{ ROWS { { UNBOUNDED | value } PRECEDING | CURRENT ROW } |
ROWS BETWEEN { { UNBOUNDED | value } PRECEDING AND
{ { UNBOUNDED | value }
FOLLOWING | value PRECEDING | CURRENT ROW } |
CURRENT ROW AND { { UNBOUNDED | value } FOLLOWING } |
value FOLLOWING AND { UNBOUNDED | value } FOLLOWING
}
}
-
value_specification
-
value_expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
- OVER
-
How values are grouped, ordered, and considered when computing the cumulative, group, or moving function.
- Values are grouped according to the PARTITION BY and RESET WHEN clauses, sorted according to the ORDER BY clause, and considered according to the aggregation group within the partition.
- RESET WHEN
-
The group or partition, over which the function operates, depending on the evaluation of the specified condition. If the condition evaluates to TRUE, a new dynamic partition is created inside the specified window partition.
- RESET WHEN is optional. If there are no RESET WHEN or PARTITION BY clauses, then the entire result set, delivered by the FROM clause, constitutes a single partition.
- If RESET WHEN is specified, then the ORDER BY clause must be specified also.
-
condition
-
A conditional expression used to determine conditional partitioning. The condition in the RESET WHEN clause is equivalent in scope to the condition in a QUALIFY clause with the additional constraint that nested ordered analytical functions cannot specify a RESET WHEN clause. In addition, you cannot specify SELECT as a nested subquery within the condition.
- The condition is applied to the rows in all designated window partitions to create sub-partitions within the particular window partitions.
- ROWS
-
The starting point for the aggregation group within the partition. The aggregation group end is the current row.
- The aggregation group of a row R is a set of rows, defined relative to R in the ordering of the rows within the partition.
- If there are no ROWS or ROWS BETWEEN clause, the default aggregation group is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
- The default when there is no ROWS clause for FIRST_VALUE/LAST_VALUE is different. For more information, see FIRST_VALUE/LAST_VALUE.
- ROWS BETWEEN
-
The aggregation group start and end, which defines a set of rows relative to the current row in the ordering of the rows within the partition.
- The row specified by the group start must precede the row specified by the group end.
- If there are no ROWS or ROWS BETWEEN clause, the default aggregation group is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
- UNBOUNDED PRECEDING
-
The entire partition preceding the current row.
- UNBOUNDED FOLLOWING
-
The entire partition following the current row.
- CURRENT ROW
-
The start or end of the aggregation group as the current row.
-
value PRECEDING
-
The number of rows preceding the current row.
- The value for value is always a positive integer literal.
- The maximum number of rows in an aggregation group is 4096 when value PRECEDING appears as the group start or group end.
-
value FOLLOWING
-
The number of rows following the current row.
- The value for value is always a positive integer literal.
- The maximum number of rows in an aggregation group is 4096 when value FOLLOWING appears as the group start or group end.
- ASC
-
That the results are to be ordered in ascending sort order.
- If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.
- The default order is ASC.
- DESC
-
That the results are to be ordered in descending sort order.
- If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.
- NULLS FIRST
-
NULL results are to be listed first.
- NULLS LAST
-
NULL results are to be listed last.