17.10 - Combining Window Functions, Teradata-Specific Functions, and GROUP BY - 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)

The following table provides the semantics of the allowable combinations of window functions, Teradata-specific functions, aggregate functions, and the GROUP BY clause.

    Combination   Semantics
Window Function Teradata-Specific Function Aggregate Function GROUP BY Clause  
X       A value is computed for each row.
  X     A value is computed for each row. The entire table constitutes a single group, or partition, over which the Teradata-specific function executes.
    X   One aggregate value is computed for the entire table.
X     X GROUP BY collapses all rows with the same value for the group-by columns into a single row, and a value is computed for each resulting row.
  X   X GROUP BY determines the partitions over which the Teradata-specific function executes. The clause does not collapse all rows with the same value for the group-by columns into a single row.
    X X An aggregation is performed for each group.
X X     Teradata-specific functions do not have partitions. The whole table is one partition.
X X   X GROUP BY determines partitions for Teradata-specific functions. GROUP BY does not collapse all rows with the same value for the group-by columns into a single row, and does not affect window function computation.
X   X X GROUP BY collapses all rows with the same value for the group-by columns into a single row. For window functions, a value is computed for each resulting row; for aggregate functions, an aggregation is performed for each group.