I receive daily data from the exchange and save it to the database. Data includes time, price, value, volume, etc…
I want to create a continuous aggregation to aggregate data by time bucket, including the following values:
CREATE MATERIALIZED VIEW stock_every_minute
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 min', "time") AS time,
symbol,
last(price, time) AS close,
sum(value) AS value,
sum(volume) AS volume,
(???) AS total_value,
(???) AS total_volume
FROM stocks_real_time srt
GROUP BY time_bucket('1 min', "time"), symbol;
I also need to calculate ‘total_value’, ‘total_volume’, which is the value accumulated from the beginning of the session (beginning of the day). I have searched but have not found an answer yet. How can I achieve it?