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 |
Company1 |
1961-05-22 00:00:00 |
457.247 |
1 |
Company1 |
1961-05-26 00:00:00 |
465.132 |
1 |
Company1 |
1961-06-02 00:00:00 |
494.12 |
1 |
Company1 |
1961-06-09 00:00:00 |
494.896 |
1 |
Company1 |
1961-06-16 00:00:00 |
486 |
1 |
Company1 |
1961-06-21 00:00:00 |
478.605 |
2 |
Company2 |
1961-05-22 00:00:00 |
67.8986 |
2 |
Company2 |
1961-05-26 00:00:00 |
65.7631 |
2 |
Company2 |
1961-06-02 00:00:00 |
65.8226 |
2 |
Company2 |
1961-06-09 00:00:00 |
66.6285 |
2 |
Company2 |
1961-06-16 00:00:00 |
66.4806 |
2 |
Company2 |
1961-06-21 00:00:00 |
64.1541 |
3 |
Company3 |
1961-05-22 00:00:00 |
18.0168 |
3 |
Company3 |
1961-05-26 00:00:00 |
17.5237 |
3 |
Company3 |
1961-06-02 00:00:00 |
19.2977 |
3 |
Company3 |
1961-06-09 00:00:00 |
22.785 |
3 |
Company3 |
1961-06-16 00:00:00 |
24.1296 |
3 |
Company3 |
1961-06-21 00:00:00 |
24.4742 |
... |
... |
... |
... |