Question on hierarchical aggregates

TimescaleDB and PostgreSQL Installation, Configuration, and Errors
1

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.

2

Solution: Found Solution, might be useful for others.

CREATE MATERIALIZED VIEW IF NOT EXISTS public.values_summary_five_minutes
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(statsagg_IntValue) as Stats_IntValue, 
        average(rollup(statsagg_IntValue)) as Avg_IntValue,     --this
        avg(average(statsagg_IntValue)) as Avg_1minAvg_IntValue 
FROM public.values_summary_one_minute_1
GROUP BY variableid, bucket_interval_five_min
1 Like
3

Thanks for sharing the resolution!