PostgreSQL如何计算相同时间戳对应数值的平均值

在PostgreSQL中，可通过窗口函数一步完成同时间戳分组的平均值计算与原表填充，不需要逐行循环计算，假设原表名为raw_metric_data，示例SQL如下：

-- 原表更新平均值的语句
UPDATE raw_metric_data
SET average = calc.avg_value
FROM (
    SELECT
        date,
        -- 匹配示例中逗号分隔的小数格式，可按需开启文本替换逻辑
        REPLACE(AVG(value) OVER (PARTITION BY date)::text, '.', ',') AS avg_value
    FROM raw_metric_data
) calc
WHERE raw_metric_data.date = calc.date

执行后即可实现预期效果：相同时间戳对应的所有行，都会被填入该时间戳下全部value值的平均值。

两种存储方案的优劣势和适用场景可参考如下：

• 原表冗余存储平均值的问题
  • 存储冗余：同一时间戳下的N条记录，需要重复存储N次相同的平均值，当单时间戳的记录数较多时，会产生不必要的存储开销。
  • 维护成本高：当原表的value发生修改、新增或删除同时间戳的记录时，需要同步更新该时间戳下所有行的Average值，容易出现更新遗漏导致的数据不一致。
• 单独建表存储平均值的优势
  • 无冗余存储：每个时间戳的平均值仅存储1条记录，存储开销和原表的明细记录数完全解耦。
  • 数据一致性更易保障：不需要逐行同步更新平均值，仅需维护单条时间戳对应的聚合值即可。
• 具体实现路径
  • 对实时性要求不高的场景，可直接用物化视图存储聚合结果，不需要手动编写同步逻辑：

  -- 独立存储每个时间戳平均值的物化视图
  CREATE MATERIALIZED VIEW timestamp_avg_metric AS
  SELECT
      date,
      AVG(value) AS avg_value
  FROM raw_metric_data
  GROUP BY date;
  

  后续需要更新聚合数据时，执行REFRESH MATERIALIZED VIEW timestamp_avg_metric即可完成全量计算。
  • 对实时性要求较高的场景，可给原表配置触发器，在原表发生新增、修改、删除操作时，自动更新独立聚合表中对应时间戳的平均值，全程不需要人工维护。
• 选型建议：如果原表数据量小、变动频率低，直接在原表填充平均值的方案操作更简便；如果原表数据量大、变动频繁，单独建表存储聚合值的方案长期维护成本更低，是更合理的数据库设计。

内容的提问来源于stack exchange，提问作者Lautaro Aguilera