15.00 - MAVG - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

MAVG

Purpose  

Computes the moving average of a value expression for each row in a partition using the specified value expression for the current row and the preceding width-1 rows.

Type

Teradata-specific function.

Syntax  

where:

 

Syntax element …

Specifies …

value_expression

a numeric literal or column expression for which a moving average is to be computed.

The expression cannot contain any ordered analytical or aggregate functions.

width

number of previous rows to be used in computing the moving average.

The value is always a positive integer literal.

The maximum is 4096.

sort_expression

a literal or column expression or comma-separated list of literal or column expressions to be used to sort the values.

For example, MAVG(Sale, 6, Region ASC, Store DESC), where Sale is the value_expression, 6 is the width, and Region ASC, Store DESC is the sort_expression list.

The expression cannot contain any ordered analytical or aggregate functions.

ASC

that the results are to be ordered in ascending sort order.

If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.

The default order is ASC.

DESC

that the results are to be ordered in descending sort order.

If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Using AVG Instead of MAVG

The use of MAVG is strongly discouraged. It is a Teradata extension to the ANSI SQL:2011 standard, and is equivalent to the ANSI-compliant AVG window function that specifies ROWS value PRECEDING as its aggregation group. MAVG is retained only for backward compatibility with existing applications.

For more information on the AVG window function, see “Window Aggregate Functions” on page 984.

Result Type and Attributes

The data type, format, and title for MAVG are as follows:

Data Type: Same as operand x

  • If operand x is character, the format is the default format for FLOAT.
  • If operand x is numeric, date, or interval, the format is the same format as x.
  • For information on the default format of data types, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

    Problems With Missing Data

    Ensure that data you analyze using MAVG has no missing data points. Computing a moving average over data with missing points produces unexpected and incorrect results because the computation considers n physical rows of data rather than n logical data points.

    Computing the Moving Average When Number of Rows < width

    For the (possibly grouped) resulting relation, the moving average considering width rows is computed where the rows are sorted by the sort_expression list.

    When there are fewer than width rows, the average is computed using the current row and all preceding rows.

    Example  

    Compute the 7-day moving average of sales for product code 10 for each day in the month of October, 1996.

       SELECT cdate, itemID, MAVG(sumPrice, 7, date)
       FROM (SELECT a1.calendar_date, a1.itemID,
       SUM(a1.price)
       FROM Sales a1
       WHERE a1.itemID=10 AND a1.calendar_date
       BETWEEN 96-10-01 AND 96-10-31
       GROUP BY a1.calendar_date, a1.itemID) AS T1(cdate,
       itemID, sumPrice);

    Example  

    The following example calculates the 50-day moving average of the closing price of the stock for Zemlinsky Bros. Corporation. The ticker name for the company is ZBC.

       SELECT MarketDay, ClosingPrice, 
          MAVG(ClosingPrice,50, MarketDay) AS ZBCAverage
       FROM MarketDailyClosing
       WHERE Ticker = 'ZBC'
       ORDER BY MarketDay;

    The results for the query might look something like the following table:

     

    MarketDay

    ClosingPrice

    ZBCAverage

    12/27/1999

    89 1/16

    85 1/2

    12/28/1999

    91 1/8

    86 1/16

    12/29/1999

    92 3/4

    86 1/2

    12/30/1999

    94 1/2

    87