1.1 - 8.10 - VWAP 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)

Input

stock_vol
id name period stockprice volume
1 Company1 1961-05-17 00:00:00 460 640000
1 Company1 1961-05-18 00:00:00 457 707200
1 Company1 1961-05-19 00:00:00 452 747200
1 Company1 1961-05-22 00:00:00 459 1.3312e+06
1 Company1 1961-05-23 00:00:00 462 1.848e+06
1 Company1 1961-05-24 00:00:00 459 779200
1 Company1 1961-05-25 00:00:00 463 528000
1 Company1 1961-05-26 00:00:00 479 843200
1 Company1 1961-05-29 00:00:00 493 1.728e+06
1 Company1 1961-05-31 00:00:00 490 880000
1 Company1 1961-06-01 00:00:00 492 760000
1 Company1 1961-06-02 00:00:00 498 1.848e+06
1 Company1 1961-06-05 00:00:00 499 1.3472e+06
1 Company1 1961-06-06 00:00:00 497 1.48e+06
1 Company1 1961-06-07 00:00:00 496 1.0912e+06
1 Company1 1961-06-08 00:00:00 490 1.096e+06
1 Company1 1961-06-09 00:00:00 489 760000
1 Company1 1961-06-12 00:00:00 478 904000
1 Company1 1961-06-13 00:00:00 487 1.9552e+06
1 Company1 1961-06-14 00:00:00 491 1.0912e+06
1 Company1 1961-06-15 00:00:00 487 1.3232e+06
1 Company1 1961-06-16 00:00:00 482 376000
1 Company1 1961-06-19 00:00:00 479 339200
1 Company1 1961-06-20 00:00:00 478 640000
1 Company1 1961-06-21 00:00:00 479 640000
2 Company2 1961-05-17 00:00:00 68.2502 2.7264e+06
2 Company2 1961-05-18 00:00:00 67.7501 1.6704e+06
2 Company2 1961-05-19 00:00:00 68.375 1.5168e+06
2 Company2 1961-05-22 00:00:00 67.1251 1.8528e+06
2 Company2 1961-05-23 00:00:00 67.1251 1.7184e+06
2 Company2 1961-05-24 00:00:00 66 1.2672e+06
... ... ... ... ...

SQL Call

SELECT * FROM VWAP (
  ON stock_vol PARTITION BY id ORDER BY period
  USING
  Price ('stockprice')
  Volume ('volume')
  TimeColumn ('period')
  TimeInterval (432000)
) AS dt ORDER BY id;

Output

Because the time interval (86,400 seconds) equals one day, the function groups the first two rows together and the last two rows together, and calculates the volume-weighted average price for each group.

 id name timestamp                  vwap               
 -- ---- -------------------------- ------------------ 
  1 ibm  1961-06-02 00:00:00.000000    494.11962890625
  1 ibm  1961-06-16 00:00:00.000000  485.9997253417969
  1 ibm  1961-06-21 00:00:00.000000   478.604736328125
  1 ibm  1961-05-26 00:00:00.000000  465.1324462890625
  1 ibm  1961-06-09 00:00:00.000000  494.8960876464844
  1 ibm  1961-05-22 00:00:00.000000     457.2470703125
  2 ge   1961-05-22 00:00:00.000000  67.89862060546875
  2 ge   1961-06-21 00:00:00.000000    64.154052734375
  2 ge   1961-06-16 00:00:00.000000  66.48062896728516
  2 ge   1961-06-09 00:00:00.000000  66.62847137451172
  2 ge   1961-06-02 00:00:00.000000  65.82262420654297
  2 ge   1961-05-26 00:00:00.000000  65.76305389404297
  3 pg   1961-06-02 00:00:00.000000  19.29766273498535
  3 pg   1961-06-16 00:00:00.000000  24.12955093383789
  3 pg   1961-06-21 00:00:00.000000 24.474178314208984
  3 pg   1961-06-09 00:00:00.000000 22.784996032714844
  3 pg   1961-05-26 00:00:00.000000  17.52366065979004
  3 pg   1961-05-22 00:00:00.000000 18.016775131225586

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