37.10. 用户定义的聚合

• 37 .10.1. 移动聚合模式

• 37 .10.2. 多态和可变参数聚合

• 37 .10.3. 有序集合聚合

• 37 .10.4. 部分聚集

• 37 .10.5. 聚合的支持功能

PostgreSQL 中的聚合函数是根据状态值和状态转换函数定义的。即，聚合使用状态值进行操作，该状态值在处理每个连续的 Importing 行时更新。要定义一个新的聚合函数，请为状态值选择一种数据类型，为状态选择一个初始值以及一个状态转换函数。状态转换函数获取当前行的先前状态值和聚合的 Importing 值，然后返回新的状态值。如果汇总的期望结果与需要保持在运行状态值中的数据不同，则还可以指定最终函数。最终函数采用结束状态值，并返回所需的任何值作为聚合结果。原则上，过渡函数和最终函数只是普通函数，也可以在聚合上下文之外使用。 (实际上，出于性能原因，创建专门的转换函数通常仅在作为集合的一部分进行调用时才有用.)

因此，除了聚合的用户看到的参数和结果数据类型之外，还有一个内部状态值数据类型可能与参数和结果类型都不同。

如果我们定义了一个不使用最终函数的集合，那么我们将有一个集合来计算每行中列值的运行函数。 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的列.)

如果没有非空 Importing 值，则上面的sum的定义将返回零(初始状态值)。也许在这种情况下，我们想返回 null-SQL 标准期望sum表现为这种方式。我们可以简单地通过省略initcond短语来做到这一点，因此初始状态值为 null。通常，这意味着sfunc将需要检查空状态值 Importing。但是对于sum和其他一些简单的聚合(例如max和min)，将第一个非空 Importing 值插入状态变量，然后在第二个非空 Importing 值处开始应用转换函数就足够了。如果初始状态值为 null 且将转换函数标记为“严格”(即，不为空 Importing 调用)，则 PostgreSQL 将自动执行此操作。

“严格”转换函数的另一默认行为是，每当遇到空 Importing 值时，先前的状态值将保持不变。因此，空值将被忽略。如果您需要其他一些行为来 Importing 空值，请不要将转换函数声明为 strict；而是对其进行编码以测试是否为空 Importing 并执行所需的任何操作。

avg(平均值)是一个更复杂的汇总示例。它需要两种运行状态：Importing 的总和和 Importing 数量的计数。通过将这些数量相除可以得到最终结果。通常通过使用数组作为状态值来实现平均值。例如，avg(float8)的内置实现如下所示：

CREATE AGGREGATE avg (float8)
(
    sfunc = float8_accum,
    stype = float8[],
    finalfunc = float8_avg,
    initcond = '{0,0,0}'
);

SQL 中的聚合函数调用允许DISTINCT和ORDER BY选项，这些选项控制哪些行被馈送到聚合的转换函数以及以什么 Sequences 馈送。这些选项是在后台实现的，与聚合的支持功能无关。

有关更多详细信息，请参见CREATE AGGREGATE命令。

37 .10.1. 移动聚合模式

聚合函数可以选择支持* moving-aggregate mode ，该模式允许在具有移动帧起点的窗口内以更快的速度执行聚合函数。 (有关使用聚合函数作为窗口函数的信息，请参见Section 3.5和Section 4.2.8。)基本思想是，除常规的“正向”转换函数外，聚合还提供反向转换函数*，该函数可从中删除行。退出窗口框架时聚合的运行状态值。例如，使用加法作为正向转换函数的sum聚合将使用减法作为逆向转换函数。如果没有逆向转移函数，则窗口函数机制必须在帧起点每次移动时从头开始重新计算聚合，从而导致运行时间与 Importing 行数乘以平均帧长度成正比。使用逆转换函数，运行时间仅与 Importing 行数成正比。

向逆转换函数传递当前状态值和当前状态中包含的最早行的总 Importing 值。如果从未汇总给定 Importing 行，而仅汇总其后的行，则必须重新构造状态值。有时，这需要前向过渡功能保持比纯聚合模式所需的状态更多的状态。因此，移动聚合模式使用与纯模式完全不同的实现方式：它具有自己的状态数据类型，自己的前向过渡函数和自己的最终函数(如果需要)。如果不需要额外的状态，这些可以与普通模式的数据类型和功能相同。

例如，我们可以扩展上面给出的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，则表示该逆向函数无法逆转此特定 Importing 的状态计算，因此将对当前帧的起始位置从头开始重做汇总计算。此约定允许在一些不常见的情况下无法从运行状态值中撤消的情况下使用移动聚合模式。逆转换函数可以在这些情况下“微不足道”，但只要能在大多数情况下起作用，它仍然会领先。例如，当必须从运行状态值中删除NaN(不是数字)Importing 时，使用浮点数的聚合可能会选择点平底锅。

在编写移动聚合支持函数时，重要的是要确保逆转移函数可以准确地重建正确的状态值。否则，取决于是否使用移动聚合模式，结果可能会有用户可见的差异。首先，似乎很容易添加逆转换函数的聚合示例，但不能满足此要求的是sum超过float4或float8Importing。天真的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 的限制。

37 .10.2. 多态和可变参数聚合

聚合函数可以使用多态状态转换函数或最终函数，以便可以将相同的函数用于实现多个聚合。有关多态函数的说明，请参见Section 37.2.5。更进一步，聚合函数本身可以用多态 Importing 类型和状态类型指定，从而允许单个聚合定义用于多种 Importing 数据类型。这是一个多态集合的示例：

CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

在此，任何给定集合调用的实际状态类型是具有实际 Importing 类型作为元素的数组类型。聚合的行为是将所有 Importing 连接到该类型的数组中。 (请注意：内置的集合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 级的等效项。为了解决这种情况，可以将最终函数声明为采用与聚合的 Importing 参数匹配的额外“虚拟”参数。此类伪参数始终作为空值传递，因为在调用最终函数时没有可用的特定值。它们唯一的用途是允许将多态最终函数的结果类型连接到集合的 Importing 类型。例如，内置聚合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选项指定最终函数除了状态值之外，还接收与聚合的 Importing 参数相对应的额外虚拟参数。额外的anynonarray参数允许array_agg_finalfn的声明有效。

通过将其最后一个参数声明为VARIADIC数组，可以使聚合函数接受不同数量的参数，其方式与常规函数几乎相同。参见Section 37.4.5。集合的转换函数必须具有与其最后一个参数相同的数组类型。转换函数通常也将标记为VARIADIC，但这不是严格要求的。

SELECT myaggregate(a ORDER BY a, b, c) FROM ...

SELECT myaggregate(a, b, c ORDER BY a) FROM ...

37 .10.3. 有序集合聚合

到目前为止，我们一直在描述的聚合是“常规”聚合。 PostgreSQL 还支持有序集合聚合，这与普通聚合在两个关键方面不同。首先，除了每个 Importing 行评估一次的普通聚合参数外，有序集合聚合还可以包含“直接”参数，每个聚合操作仅对它们进行一次评估。其次，普通聚合参数的语法为它们明确指定了排序 Sequences。通常使用有序集聚合来实现依赖于特定行 Sequences(例如，等级或百分位数)的计算，因此排序 Sequences 是任何调用的必需方面。例如，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直接参数(百分数)和可以为任何可排序数据类型的集合 Importing。它可用于获得家庭收入中位数，如下所示：

SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY income) FROM households;
 percentile_disc
-----------------
           50489

0.5是直接参数；百分位数分数在行之间变化是没有意义的。

与普通聚合不同，有序集聚合的 Importing 行排序不是在幕后进行的，而是聚合支持功能的职责。典型的实现方法是在聚合的状态值中保留对“ tuplesort”对象的引用，将传入的行馈送到该对象中，然后完成排序并在最终函数中读出数据。这种设计允许最终功能执行特殊操作，例如将其他“假设”行注入要排序的数据中。虽然通常可以使用用 PL/pgSQL 或另一种 PL 语言编写的支持功能来实现常规聚合，但是有序集聚合通常必须用 C 编写，因为它们的状态值不能定义为任何 SQL 数据类型。 (在上面的示例中，请注意，状态值被声明为类型internal-这是典型的.)

有序集聚合的状态转换函数接收当前状态值加上每一行的聚合 Importing 值，并返回更新后的状态值。此定义与普通聚合的定义相同，但请注意，未提供直接参数(如果有)。 final 函数接收最后一个状态值，直接参数的值(如果有)以及(如果指定了finalfunc_extra)与聚合 Importing 相对应的空值。与普通聚合一样，finalfunc_extra仅在聚合是多态的时才 true 有用。那么需要额外的伪参数来将最终函数的结果类型连接到集合的 Importing 类型。

当前，有序集的聚合不能用作窗口函数，因此不需要它们支持移动聚合模式。

37 .10.4. 部分聚集

可选地，聚合函数可以支持部分聚合。局部聚合的思想是对 Importing 数据的不同子集独立运行聚合的状态转换函数，然后将这些子集产生的状态值组合在一起，以产生与扫描一个 Importing 中的所有 Importing 所得的状态值相同的状态值。单次操作。通过让不同的工作进程扫描表的不同部分，可以将该模式用于并行聚合。每个工作程序产生一个部分状态值，最后这些状态值被组合以产生最终状态值。 (将来，此模式也可能用于诸如合并本地和远程表上的聚合之类的目的；但这尚未实现.)

为了支持部分聚合，聚合定义必须提供* combine 函数*，该函数接受聚合状态类型的两个值(表示对 Importing 行的两个子集进行聚合的结果)并产生状态类型的新值，表示汇总这些行集合的组合后，状态将是什么。尚不确定两组 Importing 行的相对 Sequences 是什么。这意味着通常不可能为对 Importing 行 Sequences 敏感的聚合定义有用的合并函数。

作为简单的示例，可以通过将合并函数指定为用作其过渡函数的相同的二选一或二选一的比较函数，来使MAX和MIN聚合支持部分聚合。 SUM聚合只需要一个加法函数作为合并函数。 (同样，这与它们的转换函数相同，除非状态值比 Importing 数据类型宽.)

合并函数被视为过渡函数，它恰好将状态类型的值而不是基础 Importing 类型的值作为其第二个参数。特别是，处理空值和严格函数的规则是相似的。另外，如果聚合定义指定了非 null initcond，请记住，它不仅将用作每个部分聚合运行的初始状态，而且还将用作 Combine 函数的初始状态，该函数将被调用以合并每个部分结果都进入该状态。

如果将聚合的状态类型声明为internal，则组合函数的责任是在正确的内存上下文中为聚合状态值分配其结果。这尤其意味着，当第一个 Importing 为NULL时，仅返回第二个 Importing 是无效的，因为该值将处于错误的上下文中并且将没有足够的寿命。

当将聚合的状态类型声明为internal时，聚合定义通常还应该提供* serialization 函数和 deserialization 函数*，这允许将这样的状态值从一个进程复制到另一个进程。没有这些功能，将无法执行并行聚合，并且将来的应用程序(例如本地/远程聚合)也可能无法正常工作。

序列化函数必须采用类型为internal的单个参数，并返回类型为bytea的结果，该结果表示打包为字节整数的状态值。相反，反序列化功能可逆转该转换。它必须接受bytea和internal类型的两个参数，并返回internal类型的结果。 (第二个参数未使用，并且始终为零，但是出于类型安全的原因，它是必需的.)反序列化函数的结果应仅在当前内存上下文中分配，这与 Combine 函数的结果不同，它不长-住了

还值得注意的是，要并行执行聚合，聚合本身必须标记为PARALLEL SAFE。不参考其支持功能上的 Parallel 安全标记。

37 .10.5. 聚合的支持功能

用 C 编写的函数可以通过调用AggCheckCallContext来检测它是否正在被称为聚合支持函数，例如：

if (AggCheckCallContext(fcinfo, NULL))

对此进行检查的一个原因是，当对转换函数为真时，第一个 Importing 必须是一个临时状态值，因此可以安全地就地修改而不是分配新副本。有关示例，请参见int8inc()。 (这是仅情况，函数可以安全地修改按引用传递的 Importing.特别是，正常聚合的最终函数在任何情况下都不得修改其 Importing，因为在某些情况下，它们将被重新分配-在相同的最终状态值上执行.)

AggCheckCallContext的第二个自变量可用于检索保存聚合状态值的内存上下文。这对于希望使用“扩展”对象(请参见Section 37.11.1)作为其状态值的转换函数很有用。第一次调用时，转换函数应返回一个扩展对象，其内存上下文是聚合状态上下文的子级，然后在后续调用中 continue 返回相同的扩展对象。有关示例，请参见array_append()。 (array_append()不是任何内置聚合的转换函数，但是在用作自定义聚合的转换函数时，它被编写为有效地表现.)

可用于聚合用 C 编写的函数的另一个支持例程是AggGetAggref，它返回定义聚合调用的Aggref解析节点。这主要用于有序集合聚合，可以检查Aggref节点的子结构以找出应该实现的排序 Sequences。可以在 PostgreSQL 源代码的orderedsetaggs.c中找到示例。