Moving Average Using the MAVG Function Teradata

Compatibility: Teradata Extension

A moving average incorporates the same window of rows and addition as seen in the MSUM. However, the aspect of the average incorporates a count of all the values involved and then divides the sum by the count to obtain the average.

The Moving Average (MAVG) function provides a moving average on a column's value, based on a defined number of rows also known as the query width. Like the MSUM, the MAVG defaults to ascending order for the sort. So, once you learn the MSUM, the MAVG is easier to learn because of the similarities.

If the number of rows is less than the width defined then the calculation will be based on the rows present.

The syntax for MAVG is:

The MAVG command uses the first parameter as the column containing a numeric value to average.

The second parameter used by the MAVG is the width number. It represents the number of rows included in the summation. Valid values are from 1 to 4096. If the number of rows is less than the width defined then the calculation will be based on the rows present. Although 1 is an acceptable value, it does not make sense to use it. This would mean that every average was the value for that one row.

The third parameter is entered as a major sort key that sequences the output rows. This column will be sorted to determine the sequence of the detail row data along with the MAVG. By default, the sort sequence is ascending (ASC). The DESC can be specified to request a descending (highest to lowest) sequence. Optionally, additional sort keys can be entered to request one or more minor sort sequences.

The following SELECT shows a 5-row (day) moving average for product 1000 from the Sales table using MAVG:


7 Rows Returned

moving average for product 1000 from the Sales table using MAVG

The above output averages 5 rows (5 days) all the way through the end of the returned rows. As the sixth row comes into the average operation, the first row drops out. This continues through the end of the rows.

To make the sequence more refined, the next SELECT uses an additional sort column based on the product number:


6 Rows Returned

moving average for product 1000 from the Sales table using MAVG
moving average for product 1000 from the Sales table using MAVG

Now, the major sequence is the product ID with it being sorted by date within the ID.

Moving Average with Reset Capabilities

The moving sum operation can also be written to provide the equivalence of a sub- average. When the GROUP BY designation is added to the query it indicates which column or columns to monitor for a change to occur. It also indicates the order of the sort to perform. When the data value in the column changes, the GROUP BY causes the accumulation value to be reset back to zero. Hence a sub-average is provided within a moving window.

Using MAVG and GROUP BY

The next SELECT shows a 5-row (day) moving average for products 1000 and 2000 from the Sales table using MAVG with a break on the year and month portion of the sale date in the GROUP BY:


7 Rows Returned

Using MAVG and GROUP BY
Using MAVG and GROUP BY

In the above output, the biggest change occurs on October 1, for product 1000. There, the average is the same as the sales for that day when the reset occurred as a result of finishing all rows for September.


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

Teradata Topics