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