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.
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 argument('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 | 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 | 458.4 | 1257386.6666666667 |
1 | Company1 | 1961-05-2400:00:00 | 459 | 779200 | 458.73333333333335 | 1165546.6666666667 |
1 | Company1 | 1961-05-2500:00:00 | 463 | 528000 | 460.46666666666664 | 980693.3333333334 |
1 | Company1 | 1961-05-2600:00:00 | 479 | 843200 | 467.1333333333333 | 912853.3333333334 |
1 | Company1 | 1961-05-2900:00:00 | 493 | 1.728e+06 | 476.6666666666667 | 1133546.6666666667 |
1 | Company1 | 1961-05-3100:00:00 | 490 | 880000 | 482.93333333333334 | 1045120.0 |
1 | Company1 | 1961-06-0100:00:00 | 492 | 760000 | 488.0 | 981226.6666666666 |
1 | Company1 | 1961-06-0200:00:00 | 498 | 1.848e+06 | 492.8666666666667 | 1281280.0 |
1 | Company1 | 1961-06-0500:00:00 | 499 | 1.3472e+06 | 495.73333333333335 | 1326400.0 |
... | ... | ... | ... | ... | ... | ... |