Ordered Analytical Functions Window Syntax | Teradata Vantage - 17.10 - Window Specification Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
English (United States)
{ 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.