PostgreSQL 提供了 SUM、AVG、COUNT、MIN、MAX 等常用聚合函数，但在业务场景中经常需要计算字符串拼接、中位数、众数或者复杂的累积统计值。内置聚合函数无法覆盖全部需求，此时可以利用 PostgreSQL 的 CREATE AGGREGATE 命令创建自定义聚合函数。自定义聚合函数与普通标量函数不同，它需要维护一个贯穿分组所有行的状态值，并通过状态转移函数不断更新这个状态，最终由可选的最终处理函数将状态转换为结果。理解这套状态机机制后，开发者即使不编写 C 扩展，也能用 SQL 或 PL/pgSQL 快速实现业务需要的聚合逻辑。
一、自定义聚合函数的基本组成
CREATE AGGREGATE 语法中最核心的参数是 SFUNC 和 STYPE。其中 STYPE 定义内部状态值的类型，例如整数、数组或者复合类型；SFUNC 是状态转移函数，它接收当前状态值和当前行输入值，返回更新后的状态值。PostgreSQL 会为每个分组初始化一个状态值，然后按扫描顺序对每一行调用状态转移函数，直到分组结束。如果直接返回状态值即可作为结果，那么可以不指定 FINALFUNC；如果需要把内部状态转换成其他类型，则要通过 FINALFUNC 完成最终转换。
以一个字符串拼接聚合为例，内置的 string_agg 已经能完成大部分工作，但如果我们想忽略 NULL 值或者添加固定前缀，自定义聚合可以更灵活。下面定义一个状态类型为 text 的聚合，状态转移函数把新字符串追加到已有状态后面：
CREATE OR REPLACE FUNCTION concat_state(text, text)
RETURNS text AS
$$
SELECT COALESCE($1, '') || COALESCE($2, '')
$$ LANGUAGE sql IMMUTABLE;
CREATE AGGREGATE my_concat(text)
(
SFUNC = concat_state,
STYPE = text,
INITCOND = ''
);
这里 INITCOND 用于指定初始状态值。当第一个输入行到来时，状态转移函数会以初始状态和该行值作为参数执行。注意状态转移函数必须与 STYPE 类型一致，第一个参数是状态类型，第二个参数是输入类型，返回值也必须是状态类型。上面的 COALESCE 处理了 NULL 输入，避免拼接结果变成 NULL。执行 SELECT my_concat(name) FROM users; 即可获得所有名称连接成的字符串。
如果希望最终结果与内部状态不同，可以增加 FINALFUNC。例如状态类型为数组，最终函数把数组转换成逗号分隔的文本，这样可以在最终阶段做去重或排序处理。需要特别注意的是，如果不指定 INITCOND，初始状态默认为 NULL，此时状态转移函数必须能够处理 NULL 状态值，否则会报错。
二、用PL/pgSQL实现中位数聚合
中位数是数据分析中常见的需求，但 PostgreSQL 没有内置的中位数聚合函数。我们可以使用数组作为状态类型，把分组内的所有数值累积到数组中，最后在 FINALFUNC 中排序数组并取中间值。虽然这种方式在大数据量下内存占用较高，但对于中小规模的分组非常实用，并且能清晰展示聚合函数的工作方式。
先创建一个数组追加函数，状态类型为 double precision[]，状态转移函数把新元素追加到数组末尾：
CREATE OR REPLACE FUNCTION median_accum(double precision[], double precision)
RETURNS double precision[] AS
$$
SELECT array_append(COALESCE($1, ARRAY[]::double precision[]), $2)
$$ LANGUAGE sql IMMUTABLE;
CREATE OR REPLACE FUNCTION median_final(double precision[])
RETURNS double precision AS
$$
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY x)
FROM unnest($1) AS t(x)
$$ LANGUAGE sql IMMUTABLE;
CREATE AGGREGATE median(double precision)
(
SFUNC = median_accum,
STYPE = double precision[],
FINALFUNC = median_final
);
这里 median_accum 接收当前数组状态和新输入值，使用 array_append 将新值加入数组。如果没有输入值，初始状态为 NULL，COALESCE 会将其转换为空数组。最终函数 median_final 把数组展开为行集后，借助 percentile_cont 计算中位数。这样在查询中使用 SELECT department_id, median(salary) FROM employees GROUP BY department_id; 就能得到每个部门的中位数工资。
如果需要处理空值，可以在状态转移函数中过滤掉 NULL 输入，避免数组里出现 NULL 影响排序和计算。另一种做法是在最终函数中过滤 NULL，但状态函数中过滤更节省内存。此外，PL/pgSQL 函数可以编写更复杂的逻辑，比如累积前只保留固定数量的元素，或者维护两个堆来计算流式中位数，不过这些方案需要更精细的状态管理。
自定义聚合不仅能用于标量输入，还可以接收多参数输入。例如 CREATE AGGREGATE agg_name(type1, type2) 可以定义两个输入列，状态转移函数相应地接收状态、第一个输入、第二个输入。这样就能实现类似 corr 或加权平均等复杂分析。
三、并行聚合与性能优化
PostgreSQL 的并行查询会为每个工作进程独立聚合一部分数据，然后再合并各工作进程的聚合结果。要让自定义聚合函数支持并行，必须指定 COMBINEFUNC 参数。它接收两个状态值并返回合并后的状态值，相当于把两个部分结果合成一个。如果没有 COMBINEFUNC，优化器在计划并行扫描时不会考虑使用该聚合，或者只能退化为单进程模式，影响性能。
以求和聚合为例，如果我们要实现一个自定义的 my_sum，状态类型为 bigint，状态转移函数是简单加法。为了让并行聚合生效，需要定义一个合并函数 sum_combine 把两个部分和相加：
CREATE OR REPLACE FUNCTION sum_state(bigint, bigint)
RETURNS bigint AS
$$
SELECT $1 + $2
$$ LANGUAGE sql IMMUTABLE;
CREATE OR REPLACE FUNCTION sum_combine(bigint, bigint)
RETURNS bigint AS
$$
SELECT $1 + $2
$$ LANGUAGE sql IMMUTABLE;
CREATE AGGREGATE my_sum(bigint)
(
SFUNC = sum_state,
STYPE = bigint,
COMBINEFUNC = sum_combine,
PARALLEL = SAFE
);
这里 PARALLEL = SAFE 声明聚合函数可以安全地并行执行。合并函数本身也必须是不可变函数，且其参数和返回值都要与状态类型一致。PostgreSQL 不会自动推断合并逻辑，开发者必须确保状态合并的正确性。如果状态函数不满足结合律，并行聚合的结果可能与串行不一致，因此引入 COMBINEFUNC 时需要仔细验证。
对于上面使用数组累积的中位数聚合，要支持并行需要把数组连接作为合并函数。可以定义 median_combine(double precision[], double precision[]) 使用 array_cat 合并两个数组。但是需要注意，并行模式下每个工作进程可能产生部分排序数组，最终函数会在合并后的完整数组上重新排序，因此结果依然正确。状态函数和合并函数的性能直接影响聚合速度，使用 SQL 函数通常比 PL/pgSQL 函数快，但对于复杂逻辑，C 扩展是更高性能的选择。
四、维护与常见问题
PostgreSQL 的 CREATE AGGREGATE 不支持 OR REPLACE 直接替换已存在的聚合函数，修改逻辑时必须先 DROP AGGREGATE 再重新创建。例如要修改上面 my_concat 的行为，应先执行 DROP AGGREGATE my_concat(text);，然后重新执行创建语句。删除聚合时需要用参数类型指定签名，否则会报错。如果该聚合被视图或物化视图依赖，删除会失败，需要先删除或修改依赖对象。
权限方面，创建聚合需要当前用户在模式上有 CREATE 权限，并且对状态函数和最终函数有执行权限。聚合函数创建后，其他用户如果需要调用，必须被授予 EXECUTE 权限。聚合的名称不能与同模式中的其他函数或聚合冲突。虽然聚合可以重载，但不同输入参数类型的聚合需要不同的状态函数，否则容易出现模糊调用。
常见错误包括：状态转移函数的返回类型与 STYPE 不一致、初始化条件类型不匹配、合并函数缺失但设置了 PARALLEL = SAFE 导致并行查询失败、最终函数输入类型错误等。排查时可以通过 \da+ 聚合名称 查看聚合的定义，确认各函数签名。对于需要窗口函数行为的场景，可以为聚合增加 INVERSE 函数以支持移动聚合窗口，否则 PostgreSQL 会使用较慢的重新计算方式。
自定义聚合函数是 PostgreSQL 强大的扩展能力之一，合理使用能够简化 SQL 查询、封装复杂业务逻辑并保持数据库端计算的一致性。从最简单的字符串拼接，到复杂的统计分析和并行优化，掌握 CREATE AGGREGATE 的各个参数能够大幅提升数据处理的灵活度。
PostgreSQL自定义聚合函数CREATE AGGREGATE修改时间：2026-08-27 21:51:40