如何使用CREATE AGGREGATE创建自定义聚合函数？

在 PostgreSQL 数据库中，CREATE AGGREGATE 是一条用于创建用户自定义聚合函数的 DDL 命令。与内置的 SUM、COUNT、AVG 不同，自定义聚合允许开发者按照业务需求将多行数据归约为一个结果，例如拼接可变长度的字符串、计算带权重的统计量或维护复杂的数据结构。理解这条命令需要先抓住几个核心概念：状态值、状态转移函数和最终处理函数。状态值是一个跨行传递的中间结果，状态转移函数负责把每一行的输入合并到状态值中，最终处理函数则把状态值转换为输出结果。

一、CREATE AGGREGATE 的语法结构与执行模型

PostgreSQL 中 CREATE AGGREGATE 的基本语法可以概括为：先指定聚合名称和输入数据类型，然后通过一组参数定义状态类型、状态转移函数、最终函数、初始条件以及并行聚合相关函数。命令的核心结构如下：

CREATE AGGREGATE aggregate_name (input_data_type) (
    SFUNC = state_transition_function,
    STYPE = state_data_type,
    FINALFUNC = final_function,
    INITCOND = initial_condition,
    COMBINEFUNC = combine_function,
    PARALLEL = SAFE
);

其中 SFUNC 是必选参数，它必须是一个已经存在的函数，接收两个参数：当前状态值和当前行的输入值，返回新的状态值。这个函数会在每一行上被调用，因此它的时间复杂度直接影响聚合的性能。STYPE 同样为必选参数，用来声明状态值的数据类型，该类型必须与状态转移函数的第一个参数和返回值类型兼容。如果聚合需要输出与状态值不同的类型，可以使用 FINALFUNC 对状态值做最后处理；如果状态值本身就是要返回的结果，可以省略最终函数。

执行模型上，PostgreSQL 会先取出 INITCOND 作为初始状态，然后按扫描顺序对每行调用 SFUNC，把状态值不断更新，扫描结束后如果定义了 FINALFUNC 就调用它得到输出。对于支持并行执行的聚合，还可以通过 COMBINEFUNC 合并多个工作进程产生的局部状态，这需要状态类型具备可合并性。了解这个模型之后，就可以着手设计一个具体的自定义聚合了。

二、动手实现一个字符串连接聚合

虽然 PostgreSQL 内建了 string_agg 函数可以拼接字符串，但它默认不接受自定义分隔符等复杂规则。为了演示 CREATE AGGREGATE 的完整过程，下面实现一个类似但带部分逻辑的自定义字符串聚合。首先创建状态转移函数，它接收两个 text 类型参数，表示当前累积结果和当前行字符串，返回拼接后的结果。为了避免 NULL 输入导致状态变成 NULL，可以在函数中做空值处理。

CREATE FUNCTION concat_state(current_state text, input_value text)
RETURNS text AS
$$
BEGIN
    IF current_state IS NULL THEN
        RETURN input_value;
    ELSIF input_value IS NULL THEN
        RETURN current_state;
    ELSE
        RETURN current_state || ',' || input_value;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

接着创建一个最终函数，它可以直接把状态值作为输出，这里为了演示可以不使用最终函数，但为了保留扩展性仍然定义一个。最终函数接收 text 状态并原样返回。

CREATE FUNCTION concat_final(state text)
RETURNS text AS
$$
SELECT state;
$$ LANGUAGE sql IMMUTABLE;

有了这两个函数，就可以使用 CREATE AGGREGATE 把它们组装起来。注意状态类型 STYPE 必须与状态函数的参数和返回值类型一致，这里都是 text。初始条件设为空字符串或 NULL 均可，但为了与函数中的逻辑匹配，设置为 NULL 更合适。

CREATE AGGREGATE custom_string_agg (text) (
    SFUNC = concat_state,
    STYPE = text,
    FINALFUNC = concat_final,
    INITCOND = NULL
);

创建完成后，可以使用如下查询验证效果。假设有一张用户表包含姓名，按部门分组后聚合所有姓名：

SELECT department, custom_string_agg(name) AS names
FROM employees
GROUP BY department;

这个聚合函数会返回每个部门下所有姓名的逗号分隔列表。与内建 string_agg 相比，自定义版本可以在状态函数中加入更复杂的逻辑，例如去重、限制最大长度或根据条件过滤。需要注意的是，状态函数被标记为 IMMUTABLE 时必须保证对所有输入都返回确定结果，否则聚合结果可能不可预测。

三、优化并行聚合与避免常见陷阱

PostgreSQL 从 9.6 开始支持并行聚合，但如果自定义聚合只提供了 SFUNC 而没有提供 COMBINEFUNC，查询规划器无法将其用于并行计划。为了让聚合能够利用多核优势，需要额外定义合并函数，把两个部分状态合并成完整状态。对于字符串拼接场景，合并函数可以复用 concat_state，因为它的逻辑同样适用于两个累积结果之间的合并。同时需要把 PARALLEL 标记为 SAFE，表示该聚合在并行模式下是安全的。

CREATE AGGREGATE custom_string_agg_parallel (text) (
    SFUNC = concat_state,
    STYPE = text,
    COMBINEFUNC = concat_state,
    FINALFUNC = concat_final,
    INITCOND = NULL,
    PARALLEL = SAFE
);

在实际生产环境中，有几个高频错误需要规避。第一，状态类型选择不当，比如使用可变长度的数组或临时引用类型，可能导致状态函数多次调用时出现数据竞争或内存膨胀。第二，忘记处理 NULL 输入，状态转移函数必须对 NULL 输入有明确策略，否则聚合结果会意外变成 NULL。第三，INITCOND 的类型必须与 STYPE 完全匹配，如果 STYPE 是 integer 而 INITCOND 写成 0.0 就会报错。第四，使用不可并行的函数作为状态转移函数时，如果设置了 PARALLEL = SAFE 可能导致结果错误，需要改为 RESTRICTED 或放弃并行。

此外，如果聚合函数涉及排序或分组内部顺序，PostgreSQL 不会保证行到达状态函数的顺序，除非在查询中显式使用 ORDER BY。而且对于非常大的数据集，状态值不断增长会导致内存占用增加，这时可以考虑使用 serialfunc 和 deserialfunc 将状态序列化到磁盘，但实现复杂度较高。总体而言，CREATE AGGREGATE 提供了一种高度灵活的聚合抽象，只要理解状态转移与最终处理的分工，并做好 NULL 和并行规划，就能构建出既高效又符合业务语义的自定义聚合函数。

CREATE AGGREGATE自定义聚合PostgreSQL修改时间：2026-08-27 07:43:46