35.10. 用户定义聚合

35.10.1. Moving-Aggregate 模式

聚合函数可以选择性支持moving-aggregate模式， 这允许在窗口函数中用移动帧起始点更快的执行聚合函数。 （参阅第 3.5 节和第 4.2.8 节 获取关于使用聚合函数作为窗口函数的信息。） 基本思路是除了普通"正向"转换函数外，聚合还支持一个 反向转换函数，这允许在行退出窗口框架时， 将它们从聚合的运行状态值中删除。例如，一个sum聚合， 使用加法作为正向转换函数，将使用减法作为反向转换函数。 没有反向转换函数，窗口函数机制必须在每次帧起始点移动时重新计算该聚合， 导致运行时间与输入行数量和平均帧长度的乘积成正比。 有了反向转换函数，运行时间仅与输入行数量成正比。

传递当前状态值和聚合输入值给反向转换函数作为包含在当前状态中的最早行。 如果给定的输入行从未被聚合过，那么它必须重建该状态值，但是仅在该行跟随它的情况下。 这有时需要正向转换函数持有的状态比纯聚合模式所需的多。因此， moving-aggregate模式使用一个与纯模式完全独立的实现： 它有它自己的状态数据类型，它自己的正向转换函数，和它自己的最终函数（如果需要）。 如果不需要额外的状态，这些可以与纯模式的数据类型和函数相同。

作为一个示例，我们可以扩展上面给出的sum聚合， 支持moving-aggregate模式：

CREATE AGGREGATE sum (complex)
(
    sfunc = complex_add,
    stype = complex,
    initcond = '(0,0)',
    msfunc = complex_add,
    minvfunc = complex_sub,
    mstype = complex,
    minitcond = '(0,0)'
);

名字以m开头的参数定义了moving-aggregate实现。 除了反向转换函数minvfunc，它们都对应纯聚合中不带m 的参数。

moving-aggregate模式下的正向转换函数是不允许返回null作为新的状态值的。 如果反向转换函数返回了NULL，这将被当做反向函数不能为这个特殊输入解析状态计算的指示， 并且因此聚合计算将从当前帧起始位置重做。 这个约定允许moving-aggregate模式用在一些不常见的不切实际的逆转运行状态值的情况下。 反向转换函数可以无视这些情况，然后仍然继续，只要它可以为大多数情况工作。 作为一个示例，当一个NaN (not a number)输入从运行状态值中删除时， 处理浮点数的聚合可以选择无视。

在写moving-aggregate支持函数时，确定反向转换函数可以准确的重新构造正确状态值是很重要的。 否则在结果中可能会出现用户可见的差异，取决于是否启用了moving-aggregate模式。 添加一个反向转换函数的聚合示例最初看起来是简单的，但是还不能满足 float4或float8做sum的需求。 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减1为0，而不是1。 请注意，这是通常浮点数算法的一个限制，不只是PostgreSQL的。

35.10.2. 多态和可变聚合

聚合函数可以使用多态转换函数或者最终处理函数，这样，同一个函数就可以用于实现多个聚合。 参阅第 35.2.5 节获取多态函数的解释。再进一步， 聚合函数本身可以用多态的基本类型和状态类型来声明， 这样就允许一个聚合定义用于多种输入数据类型。下面是一个多态聚合的例子：

CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    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, anyelement)
  RETURNS internal ...;
CREATE FUNCTION array_agg_finalfn(internal, anyelement)
  RETURNS anyarray ...;

CREATE AGGREGATE array_agg (anyelement)
(
    sfunc = array_agg_transfn,
    stype = internal,
    finalfunc = array_agg_finalfn,
    finalfunc_extra
);

这里的finalfunc_extra选项声明最终函数的接收，除了状态值， 额外的假变量对应于聚合的输入参数。额外的anyelement 参数允许array_agg_finalfn的声明是有效的。

一个聚合函数可以通过声明它的最后一个参数为VARIADIC数组， 接受可变数量的参数，和常规函数的方式很相似；参阅 第 35.4.5 节。 聚合的转换函数必须和它们最后的参数有相同的数组类型。 转换函数通常也被标记为VARIADIC，但这不是严格要求的。

35.10.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编写， 因为它们的状态值不能确定为任意SQL数据类型。（在上面的例子中， 请注意状态值被声明为类型internal — 这是典型的。）

顺序集聚合的状态转换函数为每一行接收当前的状态值和聚合的输入值， 并返回更新的状态值。这和普通聚合的定义相同，但是请注意， 直接参数（如果有）是没有提供的。最终函数接收最后一个状态值， 直接参数的值（如果有），和(如果声明了finalfunc_extra) 空值对应于聚合的输入。对于普通聚合，finalfunc_extra 只在聚合是多态的时是真正有用的； 然后需要额外的假变量连接最终函数的结果类型到聚合的输入类型。

当前，顺序集聚合不能用作窗口函数，并且因此它们不需要支持moving-aggregate模式。

35.10.4. 聚合的支持函数

一个用 C 写的函数可以判断它是被当作一个聚合转换函数调用还是通过调用AggCheckCallContext 作为最终的函数，例如：

if (AggCheckCallContext(fcinfo, NULL))

检查这个的一个原因是，在它对于一个转换函数为真的时候，左边的输入必须是一个临时的状态值， 因此可以安全地现场修改，而不用分配新的拷贝。参阅int8inc()的例子。 （这是函数里唯一可以修改输入的传递引用的地方。特别的， 普通聚合的最终函数不应该在任何情况下修改他们的输入， 因为在某些情况下它们将在相同的最终状态值下重复执行。）

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