PostgreSQL：分层聚合中无需GROUP BY获取正确平均值的方案

你用AVG(average(statsagg_IntValue))算出的平均值不准，核心原因是——这是对各1分钟桶平均值的简单算术平均，完全没考虑每个子桶的样本数量差异。如果不同1分钟桶里的样本数不一样，这种计算方式会拉偏结果，正确的平均值必须是总数值和除以总样本数，或者利用TimescaleDB的statsagg特性来获取。

下面给两种可行的替代方案：

方案1：用总Sum和总Count计算加权平均 #

既然你在1分钟级的物化视图里已经统计了每个桶的Sum_IntValue（数值总和）和Count_IntValue（样本数量），到5分钟聚合层时，直接累加所有子桶的Sum和Count，再相除就能得到准确的平均值：

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,
SUM(Count_IntValue) as Count_IntValue,  -- 这里用SUM而非COUNT，要累加所有子桶的样本总数
rollup(statsagg_IntValue) as Stats_IntValue, 
SUM(Sum_IntValue)::numeric / SUM(Count_IntValue) as Avg_IntValue  -- 总数值和÷总样本数
FROM public.values_summary_one_minute_1
GROUP BY variableid, bucket_interval_five_min

注意：原来的COUNT(Count_IntValue)要改成SUM(Count_IntValue)，前者是统计有多少个1分钟桶，后者才是所有子桶的样本总数。

方案2：直接从rollup后的statsagg对象提取平均值 #

你已经用了rollup(statsagg_IntValue)来合并子桶的统计信息，这个合并后的Stats_IntValue本身就包含了正确的加权平均值，直接用average()函数提取即可：

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,
SUM(Count_IntValue) as Count_IntValue,
rollup(statsagg_IntValue) as Stats_IntValue, 
average(rollup(statsagg_IntValue)) as Avg_IntValue  -- 直接从合并后的statsagg中取平均值
FROM public.values_summary_one_minute_1
GROUP BY variableid, bucket_interval_five_min

这种方式更简洁，statsagg的rollup操作会自动维护所有加权统计量，包括平均值、方差等，结果准确且无需手动计算Sum和Count的比值。

内容的提问来源于stack exchange，提问作者Amit Kumar