So in an ideal world I would like to be able to take a set of data and then compute a number of different parameters using a continious aggregation as to optimize queries over historical figures for that data. Namely I would like to compute figures like the moving average and a number of derivative statistics.
So for example I would like to write something like:
CREATE MATERIALIZED VIEW temp_moving_avg
WITH (timescaledb.continuous) AS
SELECT
time_bucket('15 minutes', timestamp) AS bucket,
station_name,
avg(temp)
FROM
station_data
GROUP BY
bucket, station_name
But rather than having that computed once every 15 minutes, have it computed once every minute over the last 15 minutes. Is there any way to do that?
Sadly it seems that using windowing does not work. While it would be an excellent solution, alas it seems like you can’t use it in aggregations. I also considered function calls, but it also looks like those would not work.