Teradata Window Functions

Teradata window aggregate functions

Window aggregate function is an aggregate function on which a window specification is applied.
Window aggregate functions in Teradata can be used to generate reports showing sub-count, sub-total, final count , final total, moving sum, cumulative sum, moving average and moving difference.
Teradata window aggregate functions are used to perform OLAP analysis data operations and data mining in a data mart or a data warehouse.

Window functions in Teradata may be performed on tables, views and INSERT or SELECT statements and are recognized by the OVER() clause.

Examples of ANSI-compliant window aggregate functions in Teradata:

  • COUNT OVER window function can be used to count rows in a defined group or in all groups.
  • SUM OVER function can produce cumulative sums, moving sums, moving averages over a group and calculate a group sum.

  • Teradata non-Ansi extensions to window aggregate functions:
  • Teradata SQL introduces the WITH and WITH...BY statements to implement window aggregate functions.
    Note that the output from WITH and WITH...BY is non-relational!
  • MDIFF function is used to generate moving differences over a group