Question 253719 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    In PostgreSQL, you normally use a subquery or a window function when you want to calculate an average without include it in the GROUP BY clause. In your situation, a subquery can be used to determine the average. Here’s how to change your query to accomplish that:

    CREATE MATERIALIZED VIEW IF NOT EXISTS public.values_summary_five_minutes
WITH (timescaledb.continuous, timescaledb.materialized_only = true) AS
SELECT
    variableid,
    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,
    (SELECT AVG(statsagg_IntValue) FROM public.values_summary_one_minute_1 sub WHERE sub.variableid = main.variableid AND sub.bucket_interval_one_min = main.bucket_interval_five_min) as Avg_IntValue
FROM
    public.values_summary_one_minute_1 main
GROUP BY
    variableid,
    bucket_interval_five_min;

    Hope it works 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search