I’m experimenting with Timescale for tracking views on a website, and while continuous aggregates work great for aggregating views per day/week/month/etc., I was wondering how to use them for “all-time views”, something like this:
CREATE MATERIALIZED VIEW metrics_episode_views_total WITH (timescaledb.continuous)
AS
SELECT
episode_id,
COUNT(1) AS views
FROM metrics_episode_views
GROUP BY
episode_id
WITH NO DATA
;
SELECT add_continuous_aggregate_policy('metrics_episode_views_total',
start_offset => INTERVAL '2 hours',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour'
);
If I try this, Timescale complains that continuous aggregate view must include a valid time bucket function
.
I could try to cheat that by giving a time bucket of 1000 years or so, but it made me wonder if I was approaching this wrong? Is continuous aggregates not the right tool to use for this kind of thing?