☰

Teradata Window Functions

Teradata Tutorial

• Teradata
• Teradata Temporary Tables
• Teradata Loading Utilities
• Teradata SQL Extensions
• Teradata Window Functions
• Custom Formatting In Teradata
• Copying Tables In Teradata SQL

Teradata resources

• Teradata Tutorials
• Teradata Forums
• Teradata Articles



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