In brief:

I’m working with TimescaleDB and need assistance with hierarchical aggregates. I have successfully created 1-minute aggregates, and I’m now looking to create 5-minute aggregates. My specific challenge is calculating the average for a particular column during this aggregation process. I plan to extend this to create 1-hour, 1-day, and 1-week aggregates in a hierarchical manner.

CREATE MATERIALIZED VIEW IF NOT EXISTS public.baseValue_summary_one_minute

WITH (timescaledb.continuous,timescaledb.materialized_only = true) AS

SELECT variableid,

time_bucket(INTERVAL ‘1 minute’, timestamp) AS bucket_interval_one_min,

MIN(intvalue) as Min_IntValue, MAX(intvalue) as Max_IntValue, SUM(intvalue) as Sum_IntValue,

COUNT(intvalue) as Count_IntValue, stats_agg(intvalue) as stats_IntValue, AVG(intvalue) as AVG_IntValue,

FROM public.baseValueTable

GROUP BY variableid, bucket_interval_one_min;

TimescaleDB version: 2.11.2

PostgreSQL version: 15

Other software: timescaledb_toolkit

OS:Linux

Install method: Docker

Environment: Development

Problem statement:

With the above materialized view now I have to create a 5 mins aggregagate, but while creating not sure how do I create an average calculation on an column.

For Example: I need to add average calculation on intvalue column in below query:

CREATE MATERIALIZED VIEW IF NOT EXISTS public.values_summary_five_minutes_1

WITH (timescaledb.continuous,timescaledb.materialized_only = true) AS(

SELECT variableid, time_bucket(INTERVAL ‘5 minute’, bucket_interval_one_min) AS bucket_interval_five_min,

MIN(Min_IntValue) as Min_IntValue, MAX(Max_IntValue) as Max_IntValue, SUM(Sum_IntValue) as Sum_IntValue,

COUNT(Count_IntValue) as Count_IntValue, rollup(stats_IntValue) as Stats_IntValue

FROM public.baseValue_summary_one_minute

GROUP BY variableid, bucket_interval_five_min

order by bucket_interval_five_min asc)

Note: We are using rollup function as we need to create other aggregates such as 1hr, 1day and 1 week aggregates will be created hierarchically.