Comparing Window Aggregate Functions and Teradata-Specific Functions

Comparing Window Aggregate Functions and Teradata-Specific Functions - Teradata Vantage

Teradata® VantageCloud Lake

Deployment

VantageCloud

Edition

Lake

Product

Teradata Vantage

Published

January 2023

Language

English (United States)

Last Update

2024-04-03

dita:mapPath

phg1621910019905.ditamap

dita:ditavalPath

pny1626732985837.ditaval

dita:id

phg1621910019905

Avoid using Teradata-specific functions such as MAVG, CSUM, and MSUM for applications intended to be ANSI-compliant and portable.

ANSI Function	Teradata Function	Relationship
AVG	MAVG	The form of the AVG window function that specifies an aggregation group of ROWS value PRECEDING is the ANSI equivalent of the MAVG Teradata-specific function. The ROWS value PRECEDING phrase specifies the number of rows preceding the current row that are used, with the current row, to compute the moving average. The total number of rows in the aggregation group is value + 1. For the MAVG function, the total number of rows in the aggregation group is the value of width. For AVG window function, an aggregation group of ROWS 5 PRECEDING, for example, means that the 5 rows preceding the current row, plus the current row, are used to compute the moving average. Thus the moving average for the 6th row of a partition considers row 6, plus rows 5, 4, 3, 2, and 1 (that is, 6 rows in all). For the MAVG function, a width of 5 means that the current row, plus 4 preceding rows, are used to compute the moving average. The moving average for the 6th row considers row 6, plus rows 4, 5, 3, and 2 (that is, 5 rows in all).
SUM	CSUM MSUM	Be sure to use the ANSI-compliant SUM window function for any new applications you develop. Avoid using CSUM and MSUM for applications intended to be ANSI-compliant and portable. The following defines the relationship between the SUM window function and the CSUM and MSUM Teradata-specific functions, respectively: The SUM window function that uses the ORDER BY clause and specifies ROWS UNBOUNDED PRECEDING is the ANSI equivalent of CSUM. The SUM window function that uses the ORDER BY clause and specifies ROWS value PRECEDING is the ANSI equivalent of MSUM. The ROWS value PRECEDING phrase specifies the number of rows preceding the current row that are used, with the current row, to compute the moving average. The total number of rows in the aggregation group is value + 1. For the MSUM function, the total number of rows in the aggregation group is the value of width. Thus for the SUM window function that computes a moving sum, an aggregation group of ROWS 5 PRECEDING means that the 5 rows preceding the current row, plus the current row, are used to compute the moving sum. The moving sum for the 6th row of a partition, for example, considers row 6, plus rows 5, 4, 3, 2, and 1 (that is, 6 rows in all). For the MSUM function, a width of 5 means that the current row, plus 4 preceding rows, are used to compute the moving sum. The moving sum for the 6th row, for example, considers row 6, plus rows 5, 4, 3, and 2 (that is, 5 rows in all). Moreover, for data having fewer than width rows, MSUM computes the sum using all the preceding rows. MSUM returns the current sum rather than nulls when the number of rows in the sample is fewer than width.

ANSI Function

Teradata Function

Relationship

AVG

MAVG

The form of the AVG window function that specifies an aggregation group of ROWS value PRECEDING is the ANSI equivalent of the MAVG Teradata-specific function.

The ROWS value PRECEDING phrase specifies the number of rows preceding the current row that are used, with the current row, to compute the moving average. The total number of rows in the aggregation group is value + 1. For the MAVG function, the total number of rows in the aggregation group is the value of width.

For AVG window function, an aggregation group of ROWS 5 PRECEDING, for example, means that the 5 rows preceding the current row, plus the current row, are used to compute the moving average. Thus the moving average for the 6th row of a partition considers row 6, plus rows 5, 4, 3, 2, and 1 (that is, 6 rows in all).

For the MAVG function, a width of 5 means that the current row, plus 4 preceding rows, are used to compute the moving average. The moving average for the 6th row considers row 6, plus rows 4, 5, 3, and 2 (that is, 5 rows in all).

SUM

CSUM

MSUM

Be sure to use the ANSI-compliant SUM window function for any new applications you develop. Avoid using CSUM and MSUM for applications intended to be ANSI-compliant and portable.

The following defines the relationship between the SUM window function and the CSUM and MSUM Teradata-specific functions, respectively:

The SUM window function that uses the ORDER BY clause and specifies ROWS UNBOUNDED PRECEDING is the ANSI equivalent of CSUM.
The SUM window function that uses the ORDER BY clause and specifies ROWS value PRECEDING is the ANSI equivalent of MSUM.
The ROWS value PRECEDING phrase specifies the number of rows preceding the current row that are used, with the current row, to compute the moving average. The total number of rows in the aggregation group is value + 1. For the MSUM function, the total number of rows in the aggregation group is the value of width.

Thus for the SUM window function that computes a moving sum, an aggregation group of ROWS 5 PRECEDING means that the 5 rows preceding the current row, plus the current row, are used to compute the moving sum. The moving sum for the 6th row of a partition, for example, considers row 6, plus rows 5, 4, 3, 2, and 1 (that is, 6 rows in all).

For the MSUM function, a width of 5 means that the current row, plus 4 preceding rows, are used to compute the moving sum. The moving sum for the 6th row, for example, considers row 6, plus rows 5, 4, 3, and 2 (that is, 5 rows in all).

Moreover, for data having fewer than width rows, MSUM computes the sum using all the preceding rows. MSUM returns the current sum rather than nulls when the number of rows in the sample is fewer than width.