This example shows how to create a moving average, which is a common method of smoothing time series data, on the data in the price column.
First create the stk table.
CREATE SET TABLE stk ( stock_id INTEGER, price FLOAT, validity PERIOD(TIMESTAMP)) PRIMARY INDEX (stock_id);
The stk table contains the following set of 30 rows.
stock_id | price | validity |
1000 | 10.00 | 2006-01-01 09:00:00 , 2006-01-01 12:00:00 |
1000 | 11.00 | 2006-01-01 12:00:00 , 2006-01-01 15:00:00 |
1000 | 9.00 | 2006-01-01 15:00:00 , 2006-01-01 18:00:00 |
1000 | 12.00 | 2006-01-02 09:00:00 , 2006-01-02 12:00:00 |
1000 | 13.00 | 2006-01-02 12:00:00 , 2006-01-02 15:00:00 |
1000 | 14.00 | 2006-01-02 15:00:00 , 2006-01-02 18:00:00 |
1000 | 8.00 | 2006-01-03 09:00:00 , 2006-01-03 12:00:00 |
1000 | 5.00 | 2006-01-03 12:00:00 , 2006-01-03 15:00:00 |
1000 | 15.00 | 2006-01-03 15:00:00 , 2006-01-03 18:00:00 |
1000 | 19.00 | 2006-01-04 09:00:00 , 2006-01-04 12:00:00 |
1000 | 16.00 | 2006-01-04 12:00:00 , 2006-01-04 15:00:00 |
1000 | 16.00 | 2006-01-04 15:00:00 , 2006-01-04 18:00:00 |
1000 | 16.00 | 2006-01-05 09:00:00 , 2006-01-05 12:00:00 |
1000 | 16.00 | 2006-01-05 12:00:00 , 2006-01-01 15:00:00 |
1000 | 16.00 | 2006-01-05 15:00:00 , 2006-01-05 18:00:00 |
1001 | 20.00 | 2006-01-01 09:00:00 , 2006-01-01 12:00:00 |
1001 | 21.00 | 2006-01-01 12:00:00 , 2006-01-01 15:00:00 |
1001 | 19.00 | 2006-01-01 15:00:00 , 2006-01-01 18:00:00 |
1001 | 22.00 | 2006-01-02 09:00:00 , 2006-01-02 12:00:00 |
1001 | 23.00 | 2006-01-02 12:00:00 , 2006-01-02 15:00:00 |
1001 | 24.00 | 2006-01-02 15:00:00 , 2006-01-02 18:00:00 |
1001 | 18.00 | 2006-01-03 09:00:00 , 2006-01-03 12:00:00 |
1001 | 15.00 | 2006-01-03 12:00:00 , 2006-01-03 15:00:00 |
1001 | 25.00 | 2006-01-03 15:00:00 , 2006-01-03 18:00:00 |
1001 | 29.00 | 2006-01-04 09:00:00 , 2006-01-04 12:00:00 |
1001 | 26.00 | 2006-01-04 12:00:00 , 2006-01-04 15:00:00 |
1001 | 26.00 | 2006-01-04 15:00:00 , 2006-01-04 18:00:00 |
1001 | 26.00 | 2006-01-05 09:00:00 , 2006-01-05 12:00:00 |
1001 | 24.00 | 2006-01-05 12:00:00 , 2006-01-01 15:00:00 |
1001 | 24.00 | 2006-01-05 15:00:00 , 2006-01-05 18:00:00 |
This example returns a moving average of stock over a three day period.
SELECT stock_id, CAST (p AS DATE), AVG(price) OVER (PARTITION BY stock_id ORDER BY p ROWS 2 PRECEDING) FROM (SELECT stock_id, price, BEGIN(p) FROM stk EXPAND ON validity AS p BY ANCHOR DAY AT TIME ‘17:59:59’ FOR PERIOD(TIMESTAMP '2006-01-01 17:59:59', TIMESTAMP '2006-01-05 18:00:00')) AS dt;
This statement returns the following 10 rows with a moving average over price.
stock_id | CAST(p AS DATE) | AVG(price) |
1000 | 2006-01-01 | 9.00000000000000E 000 |
1000 | 2006-01-02 | 1.15000000000000E 001 |
1000 | 2006-01-03 | 1.26666666666667E 001 |
1000 | 2006-01-04 | 1.50000000000000E 001 |
1000 | 2006-01-05 | 1.56666666666667E 001 |
1001 | 2006-01-01 | 1.90000000000000E 001 |
1001 | 2006-01-02 | 2.15000000000000E 001 |
1001 | 2006-01-03 | 2.26666666666667E 001 |
1001 | 2006-01-04 | 2.50000000000000E 001 |
1001 | 2006-01-05 | 2.50000000000000E 001 |
You can produce the same result without using an EXPAND ON clause by joining with a calendar table, as in the following SELECT statement.
SELECT stock_id, CAST(CAST(p AS TIMESTAMP) AS DATE), AVG(price) OVER (PARTITION BY stock_id ORDER BY p ROWS 2 PRECEDING) FROM stk, (SELECT (calendar_date (FORMAT 'yyyy-mm-dd')) || ' ' || FROM sys_callendar.calendar WHERE calendar_date BETWEEN DATE '2006-01-01' AND DATE '2006-01-06') AS dt(p) WHERE BEGIN(validity) <= p AND p < END(validity)) AS expnd;