ohlcv_continuous.sql

/* Create 15min OHLCV continuous aggregate */
CREATE MATERIALIZED VIEW demo_ohlcv_15min
WITH (timescaledb.continuous) AS 
SELECT 
time_bucket('15 min', time) AS bucket,
symbol,
FIRST(price_open, time) AS open_price,
MAX(price_high) AS highest_price,
MIN(price_low) AS lowest_price,
LAST(price_close, time) AS close_price,
SUM(trading_volume) AS volume
FROM stocks_intraday
GROUP BY bucket, symbol

/* Use the LAG() window function to compare this bucket's close_price with the previous bucket's close_price */
SELECT *, (close_price-LAG(close_price, 1) OVER (PARTITION BY symbol
					         ORDER BY bucket))/ close_price AS vs_last_bucket
FROM demo_ohlcv_15min


/*****************************/

/* Create 15min OHLCV continuous aggregate, plus open vs close price difference in the 15-min buckets */
CREATE MATERIALIZED VIEW demo_ohlcv_15min
WITH (timescaledb.continuous) AS 
SELECT 
time_bucket('15 min', time) AS bucket,
symbol,
FIRST(price_open, time) AS open_price,
MAX(price_high) AS highest_price,
MIN(price_low) AS lowest_price,
LAST(price_close, time) AS close_price,
SUM(trading_volume) AS volume,
(LAST(price_close, time)-FIRST(price_open, time))/FIRST(price_open, time) AS open_close_diff
FROM stocks_intraday
GROUP BY bucket, symbol