PostgreSQL中aggregate与function的区别及使用问题解答

1. 两者核心区别 #

• 输入处理逻辑完全不同：普通函数是逐行独立计算，每次调用只处理当前传入的行参数，输入多少行就返回多少个对应结果，行和行之间的计算没有关联；聚合函数是跨行集合计算，会扫描整组/整表的多行数据，基于全量数据的累计状态输出结果，默认一组数据只返回一个结果。
• 调用场景限制不同：普通函数可以出现在SELECT列表、WHERE子句、JOIN条件等几乎所有允许写表达式的位置；聚合函数不能直接写在WHERE子句中，未写GROUP BY的场景下，SELECT列表里的聚合不能和非聚合普通列混用（除非分组列是表主键、或开启了特殊SQL兼容模式）。
• 实现结构不同：普通函数只需要定义单次入参到出参的计算逻辑即可；聚合函数至少要定义两部分核心逻辑：状态转移函数（逐行读入数据、更新累计计算状态）、最终结果函数（把最终累计状态转换成输出值），支持窗口移动计算的聚合还要额外定义逆转移函数。

举个最直观的例子：upper(user_name)是普通函数，查询返回100行用户数据，它就会被调用100次，每次只处理当前行的user_name值，返回100个大写用户名；count(user_id)是聚合函数，不管匹配到多少行数据，最终只会返回1个计数值，计算过程要遍历所有匹配行累计总数。

2. 聚合是否属于函数的特殊类型？ #

从PostgreSQL底层实现来看，聚合是独立的数据库对象，不算普通函数的子集，但从SQL语义层面说，它确实是一类专门处理多行集合的特殊函数，符合你提到的「入参是multiset多行集合、返回单值」的核心约束——不过PostgreSQL对聚合做了不少扩展，没有完全卡死这个约束：比如聚合作为窗口函数使用时，可以按窗口范围返回和输入行等量的结果；还有percentile_cont这类有序集聚合，支持额外的排序参数，不是只接收一个多行集合入参。
从系统表结构也能看出两者的区别：普通函数的元数据只存在pg_proc系统表中，聚合除了在pg_proc存调用入口信息，还会在pg_aggregate里单独存储状态转移逻辑、初始值、最终计算函数这些专属配置。

3. 所有支持使用聚合的场景是否都可以使用普通函数？ #

完全不能互换，两者的能力边界不存在包含关系：

• 但凡需要跨行计算的场景，普通函数本身根本做不到：普通函数单次调用只能拿到传入的当前行参数，没法直接读取同组其他行的数据。就算有人硬在函数里写游标、二次查表实现跨行逻辑，本质也是把聚合该做的工作塞到了普通函数里，不仅性能极差，还容易出现数据一致性问题，不算普通函数本身的能力。
• 反过来普通函数能用的场景聚合也不一定适用：比如WHERE子句里的逐行过滤逻辑，就不能直接用聚合——聚合是在WHERE条件过滤完所有符合条件的行之后才会计算，要过滤聚合结果必须走HAVING子句。
  只有极特殊场景下两者效果类似：比如把聚合当窗口函数使用、且窗口范围只限定当前行，此时返回结果和逐行调用普通函数差不多，但完全不具备通用性。

4. 使用function和aggregate的特殊注意事项 #

普通函数注意事项 #

• 一定要给自定义函数设置正确的易变性标记：标记为IMMUTABLE代表相同入参永远返回相同结果，可以用来创建表达式索引；标记为STABLE代表同一事务内相同入参返回结果不变，可以被优化器用于索引条件下推；标记为VOLATILE代表结果随时可能变化（比如random()、内部带数据修改逻辑的函数），无法用于索引，且实际调用次数会受执行计划影响，可能和预期不符。标记错误轻则导致索引失效，重则返回错误结果。
• 不要在普通函数里写增删改这类带副作用的逻辑，再把函数放到SELECT列表里调用——尤其是带多表关联、子查询的复杂SQL，执行计划变动时函数可能被重复调用多次，导致数据异常修改。
• 注意参数类型匹配，SQL函数的类型校验很严格，如果传入参数和定义类型不匹配触发隐式转换，很可能导致字段上的索引失效。

聚合函数注意事项 #

• 除了count(*)这种特殊写法，几乎所有内置聚合都会自动跳过NULL值：比如count(col)不会统计col为NULL的行，sum(col)会把NULL值直接忽略计算，写业务逻辑时别把这部分数据算漏。
• 自定义聚合如果要支持窗口函数的移动计算，一定要实现逆转移函数，不然大窗口场景下性能会差到无法正常使用。
• 别在WHERE子句里写聚合条件，WHERE执行阶段还没开始计算聚合值，要过滤聚合结果要么写在HAVING子句里，要么把聚合逻辑写到子查询/CTE中，外层再做过滤。
• 大表做GROUP BY聚合时，注意控制分组基数，如果分组数量太多、中间计算状态超过work_mem设置的内存阈值，PostgreSQL会把中间数据落盘，性能会出现明显下降，能提前过滤掉无用数据就不要全表扫描完再做聚合。

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