1.0 - 8.00 - WeightedMovAvg Example - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.0
8.00
Release Date
May 2019
Content Type
Programming Reference
Publication ID
B700-4003-098K
Language
English (United States)

This example computes the weighted moving average for stockprice and volume for three companies.

Input

The input table, stock_data, contains hypothetical stock price and volume data of three companies between 17 May 1961 and 21 June 1961.

Each partition contains all the rows of an entity. For example, if the weighted moving average of a particular equity share price is required, then all transactions of that equity share are in one partition. Rows are ordered from earliest to latest date.

stock_data
id name period stockprice volume
1 Company1 1961-05-1700:00:00 460 640000
1 Company1 1961-05-1800:00:00 457 707200
1 Company1 1961-05-1900:00:00 452 747200
1 Company1 1961-05-2200:00:00 459 1.3312e+06
1 Company1 1961-05-2300:00:00 462 1.848e+06
1 Company1 1961-05-2400:00:00 459 779200
1 Company1 1961-05-2500:00:00 463 528000
1 Company1 1961-05-2600:00:00 479 843200
1 Company1 1961-05-2900:00:00 493 1.728e+06
1 Company1 1961-05-3100:00:00 490 880000
1 Company1 1961-06-0100:00:00 492 760000
1 Company1 1961-06-0200:00:00 498 1.848e+06
1 Company1 1961-06-0500:00:00 499 1.3472e+06
1 Company1 1961-06-0600:00:00 497 1.48e+06
1 Company1 1961-06-0700:00:00 496 1.0912e+06
1 Company1 1961-06-0800:00:00 490 1.096e+06
1 Company1 1961-06-0900:00:00 489 760000
1 Company1 1961-06-1200:00:00 478 904000
1 Company1 1961-06-1300:00:00 487 1.9552e+06
1 Company1 1961-06-1400:00:00 491 1.0912e+06
1 Company1 1961-06-1500:00:00 487 1.3232e+06
1 Company1 1961-06-1600:00:00 482 376000
1 Company1 1961-06-1900:00:00 479 339200
1 Company1 1961-06-2000:00:00 478 640000
1 Company1 1961-06-2100:00:00 479 640000
2 Company2 1961-05-1700:00:00 68.2502 2.7264e+06
2 Company2 1961-05-1800:00:00 67.7501 1.6704e+06
2 Company2 1961-05-1900:00:00 68.375 1.5168e+06
2 Company2 1961-05-2200:00:00 67.1251 1.8528e+06
2 Company2 1961-05-2300:00:00 67.1251 1.7184e+06
2 Company2 1961-05-2400:00:00 66 1.2672e+06
... ... ... ... ...

SQL Call

SELECT * FROM WeightedMovAvg (
  ON stock_vol PARTITION BY id ORDER BY period
  USING
  TargetColumns ('stockprice', 'volume')
  WindowSize (5)
  IncludeFirst ('true')
) AS dt ORDER BY id, period;

Output

Because the window size is 5, the stockprice_mavg and volume_mavg columns contain the weighted average value of the previous five rows (days). Because of IncludeFirst argument('true'), the output table includes the first five rows even though they contain null values for those columns.

id name period stockprice volume stockprice_mavg volume_mavg
1 Company1 1961-05-1700:00:00 460 640000    
1 Company1 1961-05-1800:00:00 457 707200    
1 Company1 1961-05-1900:00:00 452 747200    
1 Company1 1961-05-2200:00:00 459 1.3312e+06    
1 Company1 1961-05-2300:00:00 462 1.848e+06 458.4 1257386.6666666667
1 Company1 1961-05-2400:00:00 459 779200 458.73333333333335 1165546.6666666667
1 Company1 1961-05-2500:00:00 463 528000 460.46666666666664 980693.3333333334
1 Company1 1961-05-2600:00:00 479 843200 467.1333333333333 912853.3333333334
1 Company1 1961-05-2900:00:00 493 1.728e+06 476.6666666666667 1133546.6666666667
1 Company1 1961-05-3100:00:00 490 880000 482.93333333333334 1045120.0
1 Company1 1961-06-0100:00:00 492 760000 488.0 981226.6666666666
1 Company1 1961-06-0200:00:00 498 1.848e+06 492.8666666666667 1281280.0
1 Company1 1961-06-0500:00:00 499 1.3472e+06 495.73333333333335 1326400.0
... ... ... ... ... ... ...