创建自定义聚合函数：从简单累加到滑动窗口统计

讲解如何使用 CREATE AGGREGATE 定义自己的聚合函数，包括状态转移函数、最终函数，以及实现滑动窗口统计（如移动平均）。 · 难度：入门 · +10XP

自定义聚合函数

PostgreSQL 允许你使用 PL/pgSQL 或 C 语言创建自定义聚合函数。一个聚合函数由状态转移函数（处理每行）、最终函数（可选，输出最终结果）和初始状态组成。典型应用：实现移动平均、累积乘积、复杂统计量。例如，计算数值的加权移动平均，可以定义状态为队列，转移函数不断入队出队，最终函数计算平均值。

-- 创建自定义聚合：移动平均（窗口大小为3）
CREATE FUNCTION moving_avg_sfunc(state NUMERIC[], value NUMERIC) RETURNS NUMERIC[] AS $$
BEGIN
    state := array_append(state, value);
    IF array_length(state, 1) > 3 THEN
        state := state[2:];
    END IF;
    RETURN state;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION moving_avg_finalfunc(state NUMERIC[]) RETURNS NUMERIC AS $$
BEGIN
    RETURN (SELECT AVG(v) FROM unnest(state) AS v);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE AGGREGATE moving_avg(NUMERIC) (
    SFUNC = moving_avg_sfunc,
    STYPE = NUMERIC[],
    FINALFUNC = moving_avg_finalfunc,
    INITCOND = '{}'
);
-- 使用
SELECT id, value, moving_avg(value) OVER (ORDER BY id) FROM data_table;