17.05 - Weighted Moving Average - Teradata Database

Teradata Vantage™ - Advanced SQL Engine Analytic Functions

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1206-170K

A weighted average has multiplying factors that give different weights to different data points. Mathematically, the moving average is the convolution of the data points with a moving average function. In technical analysis, a weighted moving average (WMA) has weights that decrease arithmetically. In an n-point weighted moving average, the most recent data point has weight n, the second most recent data point has weight (n - 1), and so on, until the weight is zero.

With MAvgType ('W'), the MovingAverage function uses this formula:

WMAM = (nVM + (n-1)VM-1 + … + 2V(M-n+2)) + V(M-n+1)) / (n + (n-1) + … + 2 + 1 )

Where TotalM = VM + … + V(M-n+1), the following equations are true:
  • TotalM+1 = TotalM + VM+1 - V(M-n+1)
  • NumeratorM+1 = NumeratorM + n*VM+1 - TotalM
  • WMAM+1 = NumeratorM+1 / (n*(n+1)/2)

VM is the target column value at index M in the window under consideration.

The value n—the number of old values to use when calculating the new weighted moving average—is specified by the WindowSize syntax element.