How to use average() in a query without adding it to groupby - postgresql

Question

I have a hierarchal aggregate that uses below statement

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, AVG(average(statsagg_IntValue)) as Avg_IntValue
FROM public.values_summary_one_minute_1
GROUP BY variableid, bucket_interval_five_min

Here, I am using Avg(average(statssummary1d)), so that I don’t have to include it in group by. However, the value is not correct. Is there an alternative to finding the average in a hierarchical aggregate?

Answer 1

You can use average(rollup(statsagg_IntValue)) directly: demo

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

variableid	bucket_interval_five_min	min_intvalue	max_intvalue	sum_intvalue	count_intvalue	avg_intvalue	avg_1minavg_intvalue
1	2023-09-27 12:40:00+00	1	5	55	5	3.6666666666666665	3

Given five 1-minute blocks with the following IntValues:

(1),
(2,2),
(3,3,3),
(4,4,4,4),
(5,5,5,5,5) 

The avg(average(statsagg_IntValue)) would result in 3, since that's the average of individual, 1-minute averages.

The average(rollup(statsagg_IntValue)) should properly construct a 5-minute block with all of the underlying IntValues:

(1,2,2,3,3,3,4,4,4,4,5,5,5,5,5)

and arrive at 3.666666666666666