用户定义聚合

PostgreSQL 9.4.4 中文手册
上一页 | 上一级 | 章 35. 扩展SQL | 下一页

# 35.10. 用户定义聚合

在PostgreSQL里的聚合是依据状态值 和状态转换函数定义的。 也就是说，聚合操作使用一个随着每个输入行被处理而变化的状态值。 要定义一个新的聚合函数，就要选择表示状态值的数据类型、状态初始值、状态转换函数。 该状态转换函数接受之前的状态值和聚合的输入值作为当前行， 并返回一个新的状态值。 还可以声明一个最终处理函数， 用于对付期望的聚合结果不同于需要保留在状态值中数据的情况。 该最终处理函数接受最后的状态值并返回任何想要的作为聚合的结果。 一般而言，状态和最终函数只是普通函数，也可以用在聚合的外面 （实际上，创建特殊的只能作为聚合的一部分调用的转换函数通常对性能有帮助）。

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

如果定义了一个不使用最终处理函数的聚合，那么聚合就是对每条记录的字段值进行函数计算。 `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`列类型的那个。

如果不存在非 NULL 输入值，上面的`sum`定义将返回零值(初始状态值)。 要按照 SQL 标准的要求返回 NULL 只需忽略`initcond` 段就可以实现(这样初始状态值将变为 NULL)。 通常这也意味着`sfunc`需要检查 NULL 状态值输入，不过对于`sum`， `max`，`min`这类的简单聚合来说，把第一个非空输入插入到状态值里面， 然后从第二个非空输入状态值开始使用转换函数就足够了。 如果初始状态值是 NULL 并且转换函数被标记为"strict"(不能对 NULL 输入调用)， PostgreSQL就会自动处理这些内容。

另外一个"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命令。

## 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`，但这不是严格要求的。

> 注意: 可变聚合很容易在有`ORDER BY`选项的连接中滥用（参阅 第 4.2.7 节）， 因为解析器在这样的结合中很难断定给出的实际参数数量是否正确。 请记住，`ORDER BY`右侧的东西是排序键，不是聚合的一个参数。例如：

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

> 解析器将视其为一个单个的聚合函数参数和三个排序键。不过，用户可能想要

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

> 如果`myaggregate`是可变的，那么这些调用都可以是有效的。

> 由于相同的原因，在用相同的名字和不同数量的常规参数创建聚合函数前要再三考虑。

## 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`中找到示例。

上一页 | 起始页 | 下一页
C-语言函数 | 上一级 | 用户定义类型