Comparing Window Aggregate Functions and Teradata-Specific Functions - Analytics Database

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

SQL Functions, Expressions, and Predicates

Deployment

VantageCloud

VantageCore

Edition

Enterprise

IntelliFlex

VMware

Product

Analytics Database

Teradata Vantage

Release Number

17.20

Published

June 2022

Language

English (United States)

Last Update

2024-01-12

dita:mapPath

obm1628111499646.ditamap

dita:ditavalPath

qkf1628213546010.ditaval

dita:id

kby1472250656485

lifecycle

latest

Product Category

Teradata Vantage™

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. Note that the ROWS value PRECEDING phrase specifies the number of rows preceding the current row that are used, together 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 would have considered 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 would have considered 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. Note that the ROWS value PRECEDING phrase specifies the number of rows preceding the current row that are used, together 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, would have considered 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, would have considered 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.

Note that the ROWS value PRECEDING phrase specifies the number of rows preceding the current row that are used, together 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 would have considered 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 would have considered 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.
Note that the ROWS value PRECEDING phrase specifies the number of rows preceding the current row that are used, together 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, would have considered 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, would have considered 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.