CumulativeMovAvg Example - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product

Teradata Vantage

Release Number

8.00

1.0

Published

May 2019

Language

English (United States)

Last Update

2019-11-22

dita:mapPath

blj1506016597986.ditamap

dita:ditavalPath

blj1506016597986.ditaval

dita:id

B700-4003

lifecycle

Product Category

Teradata Vantage™

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
...	...	...	...	...