Moving Average Using AVG / OVER Teradata

Compatibility: ANSI

A moving average can now be obtained using ANSI syntax. This process is requested using the AVG by using the OVER option. This option causes the aggregate to act like an OLAP function. It provides a running average for a column's numeric value. This allows users to see what is happening with certain column averages over an ongoing progression. The results will be sorted in ascending or descending order and the sort list can consist of single or multiple columns as sort keys.

The following ANSI syntax is used with AVG to provide MAVG OLAP functionality:

However, in order to provide the moving functionality it is necessary to have a method that designates the number of rows to include in the AVG. The ANSI version of MAVG also uses a ROWS specification in the OVER to request the number of rows to involve in the operation as width. Like the MAVG, valid values for the width specification can be between 2 and 4096.

Furthermore and unlike the MAVG, it allows the AVG to add values from columns contained in rows that are before the current row and also rows that are after the current row. The MAVG only allows rows that precede (before) the current row.

The next SELECT uses AVG to produce a similar report as the previous seen MAVG, sorting on the sales date, for the dates in September:


9 Rows Returned
Moving Average Using AVG / OVER
Moving Average Using AVG / OVER

The ROWS 2 is the equivalent of using width of 3 in the MAVG.

To see product averages for the same period, the SELECT can be modified to add the product ID to the sort:


9 Rows Returned

SELECT can be modified to add the product ID
SELECT can be modified to add the product ID

Moving Average with Reset Capabilities

Like the other OLAP functions, there is a method to reset a moving average to provide a break or reset to occur.

Using AVG and OVER / ROWS and PARTITION BY

A reset on a moving average can now be obtained using ANSI syntax. This process is requested using the AVG by requesting the OVER option. The PARTITION BY identifies the column used to initiate a reset when its value changes.

The following ANSI syntax is used with AVG to provide MAVG OLAP functionality:

The next SELECT uses AVG to produce a similar report as the previous seen MAVG, sorting and breaking on the Product ID, but only for the dates prior to October 2:


8 Rows Returnedsimilar report as the previous seen MAVG

The result of the PARTITION BY causes the reset to occur when the product number changes from 1000 to 2000 on the fifth row. Therefore, the average is $41,888.88 for that row only. Then, the averaging begins again for all of the rows for product 2000.



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics