PostgreSQL聚合函数状态留存：实现增量式指数加权滚动平均

1. 修改触发器传入初始状态：完全可行 #

你的核心问题是触发器每次触发都重新全量计算，没有复用summ表中已有的聚合状态。要解决这个问题，只需要在触发器函数中先读取summ表对应symbol的当前聚合状态，然后将这个状态作为初始值参与计算，避免从头扫描全量数据。

具体修改思路 #

假设你使用的指数加权平均（EWMA）公式为 EWMA_new = α*value_new + (1-α)*EWMA_old（α为衰减系数），触发器函数可以按以下逻辑调整：

• 新数据插入tab时，先查询summ中对应symbol的现有EWMA值
• 若存在现有记录，直接用增量公式计算新的EWMA；若不存在，用新数据初始化EWMA
• 最后将新结果写入summ表（存在则更新，不存在则插入）

触发器函数示例 #

CREATE OR REPLACE FUNCTION update_summ_trigger()
RETURNS TRIGGER AS $$
DECLARE
  v_current_ewma NUMERIC;
  v_alpha NUMERIC := 0.1; -- 替换成你的衰减系数
BEGIN
  -- 读取现有聚合状态
  SELECT ewma INTO v_current_ewma
  FROM summ
  WHERE symbol = NEW.symbol;

  IF FOUND THEN
    -- 增量计算新EWMA
    UPDATE summ
    SET ewma = v_alpha * NEW.value + (1 - v_alpha) * v_current_ewma,
        time_to = NEW.time_to
    WHERE symbol = NEW.symbol;
  ELSE
    -- 首次插入，初始化EWMA
    INSERT INTO summ (symbol, time_to, ewma)
    VALUES (NEW.symbol, NEW.time_to, NEW.value);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

如果必须保留自定义聚合函数smoother，可以修改它支持接受初始状态参数，在触发器中传入summ的现有状态，让聚合函数基于已有状态继续计算，而不是从头开始。

2. 更优实现方案：放弃全量聚合，改用增量计算 #

由于EWMA本身是增量可计算的统计量，完全不需要每次扫描全量历史数据，以下是针对大数据量的最优方案：

方案一：触发器内直接计算EWMA #

如上述示例，利用EWMA的增量公式直接完成计算，不依赖自定义聚合函数。每次操作仅涉及1次查询+1次更新/插入，时间复杂度O(1)，完全避免全量扫描，是性能最优的选择。

方案二：持久化聚合状态的自定义聚合 #

如果必须使用自定义聚合函数，可以：

1. 定义包含聚合状态的类型，比如：CREATE TYPE ewma_state AS (current_value NUMERIC, alpha NUMERIC);
2. 修改smoother聚合函数，支持接受初始ewma_state参数
3. 触发器中先读取summ存储的状态，传入聚合函数处理新数据，再将更新后的状态写回summ

方案三：分区表辅助优化（可选） #

若tab表数据量极大，可按symbol或time_to做分区，触发器仅处理对应分区的数据，进一步减少潜在的数据扫描范围，但核心逻辑仍需基于增量计算。

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