1.0 - 8.00 - CumulativeMovAvg 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 a cumulative moving average for the price of stock.

Input

The input table, company1_stock, contains a series of common stock closing prices from 17 May 1961 to 2 November 1962.

company1_stock
id name period stockprice
1 Company1 1961-05-17 00:00:00 460
2 Company1 1961-05-18 00:00:00 457
3 Company1 1961-05-19 00:00:00 452
4 Company1 1961-05-22 00:00:00 459
5 Company1 1961-05-23 00:00:00 462
6 Company1 1961-05-24 00:00:00 459
7 Company1 1961-05-25 00:00:00 463
8 Company1 1961-05-26 00:00:00 479
9 Company1 1961-05-29 00:00:00 493
10 Company1 1961-05-31 00:00:00 490
11 Company1 1961-06-01 00:00:00 492
12 Company1 1961-06-02 00:00:00 498
13 Company1 1961-06-05 00:00:00 499
14 Company1 1961-06-06 00:00:00 497
15 Company1 1961-06-07 00:00:00 496
16 Company1 1961-06-08 00:00:00 490
17 Company1 1961-06-09 00:00:00 489
18 Company1 1961-06-12 00:00:00 478
19 Company1 1961-06-13 00:00:00 487
20 Company1 1961-06-14 00:00:00 491
... ... ... ...

SQL Call

SELECT * FROM CumulativeMovAvg (
  ON company1_stock PARTITION BY name ORDER BY period
  USING
  TargetColumns ('stockprice')
) AS dt ORDER BY period;

Output

id name period stockprice stockprice_mavg
1 Company1 1961-05-17 00:00:00 460 460.0
2 Company1 1961-05-18 00:00:00 457 458.5
3 Company1 1961-05-19 00:00:00 452 456.3333333333333
4 Company1 1961-05-22 00:00:00 459 457.0
5 Company1 1961-05-23 00:00:00 462 458.0
6 Company1 1961-05-24 00:00:00 459 458.1666666666667
7 Company1 1961-05-25 00:00:00 463 458.85714285714283
8 Company1 1961-05-26 00:00:00 479 461.375
9 Company1 1961-05-29 00:00:00 493 464.8888888888889
10 Company1 1961-05-31 00:00:00 490 467.4
11 Company1 1961-06-01 00:00:00 492 469.6363636363636
12 Company1 1961-06-02 00:00:00 498 472.0
13 Company1 1961-06-05 00:00:00 499 474.0769230769231
14 Company1 1961-06-06 00:00:00 497 475.7142857142857
15 Company1 1961-06-07 00:00:00 496 477.06666666666666
16 Company1 1961-06-08 00:00:00 490 477.875
17 Company1 1961-06-09 00:00:00 489 478.52941176470586
18 Company1 1961-06-12 00:00:00 478 478.5
19 Company1 1961-06-13 00:00:00 487 478.94736842105266
20 Company1 1961-06-14 00:00:00 491 479.55
... ... ... ... ...