登录38.12. 用户定义聚合#
PostgreSQL中的聚合函数根据状态值和状态转换函数进行定义。也就是说，聚合使用状态值进行操作，该状态值在处理每个连续输入行时都会更新。要定义一个新的聚合函数，需要选择一个状态值的数据类型、状态的初始值和一个状态转换函数。状态转换函数获取当前行的前一个状态值和聚合的输入值，并返回一个新的状态值。还可以指定一个最终函数，以防聚合的所需结果与需要保留在运行状态值中的数据不同。最终函数获取结束状态值并返回作为聚合结果的任何内容。原则上，转换函数和最终函数只是普通函数，也可以在聚合的上下文中外部使用。（实际上，出于性能原因，创建只能在作为聚合的一部分调用时才起作用的专门转换函数通常很有帮助。）
因此，除了聚合用户看到的参数和结果数据类型之外，还有一个内部状态值数据类型，它可能与参数和结果类型都不同。
如果我们定义一个不使用最终函数的聚合，我们就有了一个聚合，它计算每一行中列值的运行函数。sum是这种聚合的一个示例。sum从零开始，并始终将当前行的值添加到其运行总计中。例如，如果我们想要创建一个sum聚合来处理复数数据类型，我们只需要该数据类型的加法函数。聚合定义如下
CREATE AGGREGATE sum (complex)
(
sfunc = complex_add,
stype = complex,
initcond = '(0,0)'
);我们可以像这样使用它
SELECT sum(a) FROM test_complex;
sum
-----------
(34,53.9)（请注意，我们依赖于函数重载：有多个名为sum的聚合，但PostgreSQL可以找出哪种求和适用于类型为complex的列。）
如果不存在非空输入值，则上述sum定义将返回零（初始状态值）。也许我们希望在这种情况下返回 null — SQL 标准期望sum以这种方式运行。我们可以通过省略initcond短语来简单地做到这一点，以便初始状态值为 null。通常，这意味着sfunc需要检查 null 状态值输入。但是对于sum和一些其他简单的聚合（如max和min），将第一个非空输入值插入状态变量中，然后从第二个非空输入值开始应用转换函数就足够了。PostgreSQL将在初始状态值为 null 且转换函数标记为“strict”（即，不针对 null 输入调用）时自动执行此操作。
对于“strict”转换函数的另一个默认行为是，每当遇到 null 输入值时，都会保留前一个状态值不变。因此，将忽略 null 值。如果您需要针对 null 输入执行其他行为，请不要将转换函数声明为 strict；而是对其进行编码以测试 null 输入并执行所需的任何操作。
avg（平均值）是聚合的更复杂的示例。它需要两部分运行状态：输入的总和和输入数量的计数。最终结果是通过除以这些数量获得的。平均值通常通过使用数组作为状态值来实现。例如，avg(float8)的内置实现如下
CREATE AGGREGATE avg (float8)
(
sfunc = float8_accum,
stype = float8[],
finalfunc = float8_avg,
initcond = '{0,0,0}'
);注意
float8_accum需要一个三元素数组，而不仅仅是两个元素，因为它累积平方和以及输入的总和和计数。这是为了使其可以用于avg以及其他一些聚合。
SQL 中的聚合函数调用允许DISTINCT和ORDER BY选项，这些选项控制哪些行被馈送到聚合的转换函数以及按什么顺序。这些选项在幕后实现，与聚合的支持函数无关。
有关更多详细信息，请参阅CREATE AGGREGATE命令。
38.12.1. 移动聚合模式#
聚合函数可以选择支持移动聚合模式，这允许在具有移动框架起始点的窗口中大幅加快聚合函数的执行速度。（请参阅第 3.5 节和第 4.2.8 节，了解有关将聚合函数用作窗口函数的信息。）基本思想是，除了正常的“前向”转换函数外，聚合还提供了一个反向转换函数，该函数允许在行退出窗口框架时从聚合的运行状态值中删除行。例如，一个sum聚合使用加法作为前向转换函数，将使用减法作为反向转换函数。如果没有反向转换函数，则窗口函数机制必须在框架起始点每次移动时从头重新计算聚合，这会导致运行时间与输入行数乘以平均框架长度成正比。使用反向转换函数，运行时间仅与输入行数成正比。
反向转换函数将传递当前状态值和当前状态中包含的最早行的聚合输入值。它必须重建如果给定的输入行从未被聚合，但只有其后的行，则状态值将是什么。这有时要求前向转换函数保留比纯聚合模式所需的更多状态。因此，移动聚合模式使用与纯模式完全分开的实现：它有自己的状态数据类型、自己的前向转换函数以及在需要时有自己的最终函数。如果不需要额外状态，这些可以与纯模式的数据类型和函数相同。
作为一个示例，我们可以扩展上面给出的sum聚合以支持移动聚合模式，如下所示
CREATE AGGREGATE sum (complex)
(
sfunc = complex_add,
stype = complex,
initcond = '(0,0)',
msfunc = complex_add,
minvfunc = complex_sub,
mstype = complex,
minitcond = '(0,0)'
);名称以m开头的参数定义了移动聚合实现。除了反向转换函数minvfunc之外，它们对应于没有m的纯聚合参数。
移动聚合模式的前向转换函数不允许将 null 返回为新状态值。如果反向转换函数返回 null，则这表示反向函数无法针对此特定输入反转状态计算，因此将从头为当前框架起始位置重新执行聚合计算。此约定允许在某些情况下使用移动聚合模式，在这种情况下，有一些不常见的情况不适合从运行状态值中反转出来。反向转换函数可以在这些情况下“放弃”，但只要它能适用于大多数情况，它仍然可以领先。例如，使用浮点数的聚合可能会选择在必须从运行状态值中删除NaN（不是数字）输入时放弃。
在编写移动聚合支持函数时，务必确保逆转换函数可以准确重建正确的状态值。否则，根据是否使用移动聚合模式，结果中可能会出现用户可见的差异。sum对float4或float8输入求和的聚合是一个示例，乍一看为其添加逆转换函数似乎很容易，但实际上无法满足此要求。对sum(float8)的朴素声明可能是
CREATE AGGREGATE unsafe_sum (float8)
(
stype = float8,
sfunc = float8pl,
mstype = float8,
msfunc = float8pl,
minvfunc = float8mi
);但是，此聚合可能会产生与没有逆转换函数时截然不同的结果。例如，考虑
SELECT
unsafe_sum(x) OVER (ORDER BY n ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM (VALUES (1, 1.0e20::float8),
(2, 1.0::float8)) AS v (n,x);此查询返回0作为其第二个结果，而不是预期的答案1。原因是浮点值的精度有限：将1添加到1e20会再次得到1e20，因此从中减去1e20会得到0，而不是1。请注意，这是浮点运算的限制，而不是PostgreSQL的限制。
38.12.2. 多态和变参聚合#
聚合函数可以使用多态状态转换函数或最终函数，以便使用相同的函数来实现多个聚合。有关多态函数的说明，请参阅第 38.2.5 节。更进一步，聚合函数本身可以用多态输入类型和状态类型指定，从而允许单个聚合定义用于多种输入数据类型。以下是一个多态聚合的示例
CREATE AGGREGATE array_accum (anycompatible)
(
sfunc = array_append,
stype = anycompatiblearray,
initcond = '{}'
);在此，任何给定聚合调用的实际状态类型都是将实际输入类型作为元素的数组类型。聚合的行为是将所有输入连接成该类型的数组。（注意：内置聚合array_agg提供类似的功能，其性能优于此定义。）
以下是使用两种不同的实际数据类型作为参数的输出
SELECT attrelid::regclass, array_accum(attname)
FROM pg_attribute
WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
GROUP BY attrelid;
attrelid | array_accum
---------------+---------------------------------------
pg_tablespace | {spcname,spcowner,spcacl,spcoptions}
(1 row)
SELECT attrelid::regclass, array_accum(atttypid::regtype)
FROM pg_attribute
WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
GROUP BY attrelid;
attrelid | array_accum
---------------+---------------------------
pg_tablespace | {name,oid,aclitem[],text[]}
(1 row)通常，具有多态结果类型的聚合函数具有多态状态类型，如上面的示例所示。这是必要的，因为否则无法合理地声明最终函数：它需要具有多态结果类型，但没有多态参数类型，CREATE FUNCTION将拒绝，理由是无法从调用中推导出结果类型。但有时使用多态状态类型不方便。最常见的情况是聚合支持函数将用 C 编写，并且状态类型应声明为internal，因为没有 SQL 级别的等效项。为了解决这种情况，可以将最终函数声明为采用额外的“虚拟”参数，这些参数与聚合的输入参数匹配。此类虚拟参数始终作为空值传递，因为在调用最终函数时没有可用的特定值。它们唯一的用途是允许多态最终函数的结果类型连接到聚合的输入类型。例如，内置聚合array_agg的定义等效于
CREATE FUNCTION array_agg_transfn(internal, anynonarray)
RETURNS internal ...;
CREATE FUNCTION array_agg_finalfn(internal, anynonarray)
RETURNS anyarray ...;
CREATE AGGREGATE array_agg (anynonarray)
(
sfunc = array_agg_transfn,
stype = internal,
finalfunc = array_agg_finalfn,
finalfunc_extra
);此处，finalfunc_extra选项指定最终函数除了状态值之外，还接收与聚合的输入参数对应的额外虚拟参数。额外的anynonarray参数允许array_agg_finalfn的声明有效。
可以通过将最后一个参数声明为VARIADIC数组来使聚合函数接受可变数量的参数，这与常规函数的方式非常相似；请参阅第 38.5.6 节。聚合的转换函数必须与其最后一个参数具有相同的数组类型。转换函数通常也会标记为VARIADIC，但这并不是严格要求的。
注意
可变参数聚合很容易与ORDER BY选项结合使用（请参阅第 4.2.7 节），因为解析器无法判断在这种组合中是否给出了错误数量的实际参数。请记住，ORDER BY右侧的所有内容都是排序键，而不是聚合的参数。例如，在解析器会将其视为单个聚合函数参数和三个排序键。但是，用户可能打算如果myaggregate是可变参数的，则这两个调用都可能是完全有效的。出于同样的原因，在创建具有相同名称和不同数量常规参数的聚合函数之前，最好三思而后行。
38.12.3. 有序集聚合#
到目前为止，我们描述的聚合是“常规”聚合。PostgreSQL还支持有序集聚合，它在两个关键方面与常规聚合不同。首先，除了每个输入行评估一次的普通聚合参数外，有序集聚合还可以具有仅在每个聚合操作中评估一次的“直接”参数。其次，普通聚合参数的语法明确指定了它们的排序顺序。有序集聚合通常用于实现依赖于特定行排序的计算，例如排名或百分位数，因此排序顺序是任何调用的必需方面。例如，percentile_disc的内置定义等效于
CREATE FUNCTION ordered_set_transition(internal, anyelement)
RETURNS internal ...;
CREATE FUNCTION percentile_disc_final(internal, float8, anyelement)
RETURNS anyelement ...;
CREATE AGGREGATE percentile_disc (float8 ORDER BY anyelement)
(
sfunc = ordered_set_transition,
stype = internal,
finalfunc = percentile_disc_final,
finalfunc_extra
);此聚合采用一个float8直接参数（百分位数分数）和一个聚合输入，该输入可以是任何可排序数据类型。它可以用来获取中位数家庭收入，如下所示
SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY income) FROM households;
percentile_disc
-----------------
50489此处，0.5是一个直接参数；百分位数分数成为跨行变化的值毫无意义。
与常规聚合的情况不同，有序集聚合的输入行排序不是在后台完成的，而是聚合支持函数的责任。典型的实现方法是在聚合的状态值中保留对“tuplesort”对象的引用，将传入行馈送到该对象，然后完成排序并在最终函数中读出数据。此设计允许最终函数执行特殊操作，例如向要排序的数据中注入其他“假设”行。虽然常规聚合通常可以使用用PL/pgSQL或其他 PL 语言编写的支持函数来实现，但有序集聚合通常必须用 C 编写，因为它们的 state 值无法定义为任何 SQL 数据类型。（在上面的示例中，请注意 state 值被声明为internal类型——这是典型的。）此外，由于最终函数执行排序，因此不可能通过稍后再次执行转换函数来继续添加输入行。这意味着最终函数不是READ_ONLY；它必须在CREATE AGGREGATE中声明为READ_WRITE，或者如果其他最终函数调用有可能利用已排序的 state，则声明为SHAREABLE。
有序集聚合的 state 转换函数接收当前 state 值加上每行的聚合输入值，并返回更新的 state 值。这与普通聚合的定义相同，但请注意未提供直接参数（如果有）。最终函数接收最后一个 state 值、直接参数的值（如果有），以及（如果指定了finalfunc_extra）与聚合输入相对应的 null 值。与普通聚合一样，finalfunc_extra只有在聚合是多态时才真正有用；然后需要额外的虚拟参数来将最终函数的结果类型连接到聚合的输入类型。
目前，有序集聚合不能用作窗口函数，因此它们无需支持移动聚合模式。
38.12.4. 部分聚合#
聚合函数可以选择支持部分聚合。部分聚合的思想是独立地对输入数据的不同子集运行聚合的 state 转换函数，然后将这些子集产生的 state 值组合起来，以生成与扫描所有输入一次操作产生的相同 state 值。此模式可用于并行聚合，让不同的工作进程扫描表中的不同部分。每个工作进程生成一个部分 state 值，最后将这些 state 值组合起来生成最终 state 值。（将来此模式还可用于将本地和远程表上的聚合相结合等目的；但这尚未实现。）
为支持部分聚合，聚合定义必须提供一个组合函数，它采用聚合状态类型（表示在输入行的两个子集上聚合结果）的两个值，并生成状态类型的新值，表示在对这些行集的组合进行聚合后状态将是什么。两个集合中输入行的相对顺序未指定。这意味着通常不可能为对输入行顺序敏感的聚合定义一个有用的组合函数。
作为简单的示例，MAX和MIN聚合可以通过将组合函数指定为与用作其转换函数的两个中较大值或两个中较小值比较函数相同来支持部分聚合。SUM聚合只需要一个加法函数作为组合函数。（同样，这与它们的转换函数相同，除非状态值比输入数据类型宽。）
组合函数的处理方式与转换函数非常相似，后者碰巧将状态类型的值（而不是基础输入类型的值）作为其第二个参数。特别是，处理空值和严格函数的规则是相似的。此外，如果聚合定义指定了一个非空initcond，请记住，它不仅用作每个部分聚合运行的初始状态，还用作组合函数的初始状态，该函数将被调用以将每个部分结果组合到该状态中。
如果聚合的状态类型声明为internal，则组合函数负责将其结果分配到聚合状态值的正确内存上下文中。这尤其意味着当第一个输入为NULL时，简单地返回第二个输入是无效的，因为该值将处于错误的上下文中并且没有足够的生命周期。
当聚合的状态类型声明为internal时，通常也适合聚合定义提供一个序列化函数和一个反序列化函数，它们允许将这样的状态值从一个进程复制到另一个进程。如果没有这些函数，则无法执行并行聚合，并且诸如本地/远程聚合之类的未来应用程序可能也无法工作。
序列化函数必须采用一个类型为internal的参数，并返回类型为bytea的结果，该结果表示打包到平面字节块中的状态值。相反，反序列化函数会逆转该转换。它必须采用类型为bytea和internal的两个参数，并返回类型为internal的结果。（第二个参数未使用，并且始终为零，但出于类型安全原因需要它。）反序列化函数的结果应简单地分配在当前内存上下文中，因为与组合函数的结果不同，它不是长寿命的。
同样值得注意的是，要并行执行聚合，聚合本身必须标记为PARALLEL SAFE。不会咨询其支持函数上的并行安全性标记。
38.12.5. 聚合的支持函数#
用 C 编写的函数可以通过调用AggCheckCallContext来检测它是否被调用为聚合支持函数，例如
if (AggCheckCallContext(fcinfo, NULL))检查此项的一个原因是，当它为真时，第一个输入必须是临时状态值，因此可以安全地就地修改，而不是分配新副本。有关示例，请参见int8inc()。（虽然始终允许聚合转换函数就地修改转换值，但通常不建议聚合最终函数这样做；如果这样做，则在创建聚合时必须声明该行为。有关更多详细信息，请参见CREATE AGGREGATE。）
AggCheckCallContext的第二个参数可用于检索保留聚合状态值所在的内存上下文。这对于希望使用“展开”对象（请参见第 38.13.1 节）作为其状态值的转换函数非常有用。在第一次调用时，转换函数应返回一个展开的对象，其内存上下文是聚合状态上下文的子级，然后在后续调用中继续返回相同展开的对象。有关示例，请参见array_append()。（array_append()不是任何内置聚合的转换函数，但它被编写为在用作自定义聚合的转换函数时高效运行。）
用 C 编写的聚合函数可用的另一个支持例程是AggGetAggref，它返回定义聚合调用的Aggref解析节点。这主要对有序集聚合有用，它可以检查Aggref节点的子结构以找出它们应该实现哪种排序。可以在PostgreSQL源代码中的orderedsetaggs.c中找到示例。