1.1 - 8.10 - WeightedMovAvg Example - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
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 ('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 ibm  1961-05-17 00:00:00.000000      460.0  640000.0               NULL               NULL
  1 ibm  1961-05-18 00:00:00.000000      457.0  707200.0               NULL               NULL
  1 ibm  1961-05-19 00:00:00.000000      452.0  747200.0               NULL               NULL
  1 ibm  1961-05-22 00:00:00.000000      459.0 1331200.0               NULL               NULL
  1 ibm  1961-05-23 00:00:00.000000      462.0 1848000.0              458.4 1257386.6666666667
  1 ibm  1961-05-24 00:00:00.000000      459.0  779200.0 458.73333333333335 1165546.6666666667
  1 ibm  1961-05-25 00:00:00.000000      463.0  528000.0 460.46666666666664  980693.3333333334
  1 ibm  1961-05-26 00:00:00.000000      479.0  843200.0  467.1333333333333  912853.3333333334
  1 ibm  1961-05-29 00:00:00.000000      493.0 1728000.0  476.6666666666667 1133546.6666666667
  1 ibm  1961-05-31 00:00:00.000000      490.0  880000.0 482.93333333333334          1045120.0
  1 ibm  1961-06-01 00:00:00.000000      492.0  760000.0              488.0  981226.6666666666
  1 ibm  1961-06-02 00:00:00.000000      498.0 1848000.0  492.8666666666667          1281280.0
  1 ibm  1961-06-05 00:00:00.000000      499.0 1347200.0 495.73333333333335          1326400.0
  1 ibm  1961-06-06 00:00:00.000000      497.0 1480000.0              496.6 1382186.6666666667
  1 ibm  1961-06-07 00:00:00.000000      496.0 1091200.0  496.8666666666667 1324906.6666666667
  1 ibm  1961-06-08 00:00:00.000000      490.0 1096000.0 494.73333333333335 1255146.6666666667
  1 ibm  1961-06-09 00:00:00.000000      489.0  760000.0              492.4 1050986.6666666667
  1 ibm  1961-06-12 00:00:00.000000      478.0  904000.0              487.0           967360.0
  1 ibm  1961-06-13 00:00:00.000000      487.0 1955200.0              486.0          1263680.0
  1 ibm  1961-06-14 00:00:00.000000      491.0 1091200.0              487.0          1240320.0
  1 ibm  1961-06-15 00:00:00.000000      487.0 1323200.0              487.0 1294293.3333333333
  1 ibm  1961-06-16 00:00:00.000000      482.0  376000.0 485.53333333333336 1017386.6666666666
  1 ibm  1961-06-19 00:00:00.000000      479.0  339200.0 483.53333333333336  753813.3333333334
  1 ibm  1961-06-20 00:00:00.000000      478.0  640000.0  481.1333333333333           628160.0
  1 ibm  1961-06-21 00:00:00.000000      479.0  640000.0  479.6666666666667  590186.6666666666
  2 ge   1961-05-17 00:00:00.000000   68.25024 2726400.0               NULL               NULL
  2 ge   1961-05-18 00:00:00.000000  67.750082 1670400.0               NULL               NULL
  2 ge   1961-05-19 00:00:00.000000  68.375038 1516800.0               NULL               NULL
  2 ge   1961-05-22 00:00:00.000000   67.12512 1852800.0               NULL               NULL
  2 ge   1961-05-23 00:00:00.000000   67.12512 1718400.0  67.53343986666667          1774720.0
  2 ge   1961-05-24 00:00:00.000000       66.0 1267200.0  66.95839986666665          1564800.0
  2 ge   1961-05-25 00:00:00.000000  66.500158 2006400.0  66.70009519999999          1698560.0
  2 ge   1961-05-26 00:00:00.000000   64.12512 2515200.0  65.73343946666667          1979520.0
  2 ge   1961-05-29 00:00:00.000000  64.750082 2236800.0  65.25843226666667          2101120.0
  2 ge   1961-05-31 00:00:00.000000  65.375038 2227200.0  65.15007960000001          2193920.0
  2 ge   1961-06-01 00:00:00.000000  66.624962 2659200.0  65.57504040000002          2396800.0
  2 ge   1961-06-02 00:00:00.000000  66.874557 1200000.0  66.04153540000001          2020480.0
  2 ge   1961-06-05 00:00:00.000000  67.000322 1478400.0  66.52499213333336          1790720.0
  2 ge   1961-06-06 00:00:00.000000  67.249918 1219200.0  66.89996740000002          1543680.0
  2 ge   1961-06-07 00:00:00.000000  65.875202 1084800.0   66.6500482666667          1319680.0
  2 ge   1961-06-08 00:00:00.000000  66.124798  643200.0  66.44998353333338          1024640.0
  2 ge   1961-06-09 00:00:00.000000  66.500158 1296000.0   66.4083830666667          1081600.0
  2 ge   1961-06-12 00:00:00.000000  67.750082 1017600.0  66.80838386666672          1039360.0
  2 ge   1961-06-13 00:00:00.000000  67.499519 1152000.0  67.07487966666673          1072640.0
  2 ge   1961-06-14 00:00:00.000000  67.249918 1238400.0  67.24153506666673          1139200.0
  2 ge   1961-06-15 00:00:00.000000  66.249601 1219200.0   66.9831037333334          1189120.0
  2 ge   1961-06-16 00:00:00.000000  64.500481 1584000.0  66.13331220000005          1322240.0
  2 ge   1961-06-19 00:00:00.000000   64.87488 1056000.0  65.54163213333338          1260160.0
  2 ge   1961-06-20 00:00:00.000000  63.624962  835200.0  64.72499286666671          1121920.0
  2 ge   1961-06-21 00:00:00.000000  63.874557 1142400.0  64.24985573333338          1107200.0
  3 pg   1961-05-17 00:00:00.000000     18.375 1032000.0               NULL               NULL
  3 pg   1961-05-18 00:00:00.000000      18.25 1435200.0               NULL               NULL
  3 pg   1961-05-19 00:00:00.000000     17.625 1582000.0               NULL               NULL
  3 pg   1961-05-22 00:00:00.000000      17.75  317200.0               NULL               NULL
  3 pg   1961-05-23 00:00:00.000000     17.625  608800.0 17.791666666666668           864080.0
  3 pg   1961-05-24 00:00:00.000000      17.75 1038800.0 17.733333333333334  878666.6666666666
  3 pg   1961-05-25 00:00:00.000000      17.25  911200.0              17.55  850266.6666666666
  3 pg   1961-05-26 00:00:00.000000       17.5 2005600.0 17.516666666666666          1221600.0
  3 pg   1961-05-29 00:00:00.000000       18.0 1672800.0 17.658333333333335          1453760.0
  3 pg   1961-05-31 00:00:00.000000       18.0 1735600.0 17.783333333333335          1616480.0
  3 pg   1961-06-01 00:00:00.000000       21.5 1228400.0              19.05 1535013.3333333333
  3 pg   1961-06-02 00:00:00.000000       22.0  635600.0 20.233333333333334 1243306.6666666667
  3 pg   1961-06-05 00:00:00.000000       22.0  434800.0               21.1           903040.0
  3 pg   1961-06-06 00:00:00.000000       22.5  570800.0 21.833333333333332  712826.6666666666
  3 pg   1961-06-07 00:00:00.000000       23.0 1582000.0 22.433333333333334  933146.6666666666
  3 pg   1961-06-08 00:00:00.000000      22.75  984800.0 22.616666666666667           964640.0
  3 pg   1961-06-09 00:00:00.000000       23.0  922400.0               22.8  991573.3333333334
  3 pg   1961-06-12 00:00:00.000000     23.125  339600.0 22.958333333333332           805120.0
  3 pg   1961-06-13 00:00:00.000000     24.125  909200.0             23.375           814880.0
  3 pg   1961-06-14 00:00:00.000000     24.125  916000.0 23.683333333333334  804346.6666666666
  3 pg   1961-06-15 00:00:00.000000     24.375  538000.0               24.0  712213.3333333334
  3 pg   1961-06-16 00:00:00.000000     24.625  438800.0 24.291666666666668           616800.0
  3 pg   1961-06-19 00:00:00.000000       24.5  620000.0 24.433333333333334  614026.6666666666
  3 pg   1961-06-20 00:00:00.000000     24.375  952800.0 24.441666666666666  703493.3333333334
  3 pg   1961-06-21 00:00:00.000000     24.625  520400.0 24.516666666666666           645920.0

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.