CREATE AGGREGATE

定义一个新的聚集函数。

概要

CREATE [ OR REPLACE ] AGGREGATE <name> ( [ <argmode> ] [ <argname> ] <arg_data_type> [ , ... ] ) (
    SFUNC = <sfunc>,
    STYPE = <state_data_type>
    [ , SSPACE = <state_data_size> ]
    [ , FINALFUNC = <ffunc> ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , COMBINEFUNC = <combinefunc> ]
    [ , SERIALFUNC = <serialfunc> ]
    [ , DESERIALFUNC = <deserialfunc> ]
    [ , INITCOND = <initial_condition> ]
    [ , MSFUNC = <msfunc> ]
    [ , MINVFUNC = <minvfunc> ]
    [ , MSTYPE = <mstate_data_type> ]
    [ , MSSPACE = <mstate_data_size> ]
    [ , MFINALFUNC = <mffunc> ]
    [ , MFINALFUNC_EXTRA ]
    [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , MINITCOND = <minitial_condition> ]
    [ , SORTOP = <sort_operator> ]
    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
    [ , REPSAFE = <boolean> ]
  )

  CREATE [ OR REPLACE ] AGGREGATE <name> ( [ [ <argmode> ] [ <argname> ] <arg_data_type> [ , ... ] ]
      ORDER BY [ <argmode> ] [ <argname> ] <arg_data_type> [ , ... ] ) (
    SFUNC = <sfunc>,
    STYPE = <state_data_type>
    [ , SSPACE = <state_data_size> ]
    [ , FINALFUNC = <ffunc> ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , COMBINEFUNC = <combinefunc> ]
    [ , INITCOND = <initial_condition> ]
    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
    [ , REPSAFE = <boolean> ]
    [ , HYPOTHETICAL ]
  )

  or the old syntax

  CREATE [ OR REPLACE ] AGGREGATE <name> (
    BASETYPE = <base_type>,
    SFUNC = <sfunc>,
    STYPE = <state_data_type>
    [ , SSPACE = <state_data_size> ]
    [ , FINALFUNC = <ffunc> ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , COMBINEFUNC = <combinefunc> ]
    [ , SERIALFUNC = <serialfunc> ]
    [ , DESERIALFUNC = <deserialfunc> ]
    [ , INITCOND = <initial_condition> ]
    [ , MSFUNC = <msfunc> ]
    [ , MINVFUNC = <minvfunc> ]
    [ , MSTYPE = <mstate_data_type> ]
    [ , MSSPACE = <mstate_data_size> ]
    [ , MFINALFUNC = <mffunc> ]
    [ , MFINALFUNC_EXTRA ]
    [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , MINITCOND = <minitial_condition> ]
    [ , SORTOP = <sort_operator> ]
    [ , REPSAFE = <boolean> ]
  )

描述

CREATE AGGREGATE 定义一个新的聚合函数。CREATE OR REPLACE AGGREGATE 将定义新聚合函数或替换现有定义。数据库中已提供若干基本常用聚合函数，如 count()、min()、max()、sum()、avg() 等。若需定义新类型或使用未提供的聚合函数，可通过 CREATE AGGREGATE 实现所需功能。

替换现有定义时，参数类型、结果类型及直接参数数量均不得更改。此外，新定义必须与旧定义属于相同类型（普通聚合函数、有序集聚合函数或假设集聚合函数）。

若指定模式名称（例如 CREATE AGGREGATE myschema.myagg ...），则聚合函数将创建于指定模式中；否则默认创建于当前模式。

聚合函数通过其名称和输入数据类型进行标识。同一模式中两个聚合函数若操作不同输入类型，可使用相同名称。聚合函数的名称和输入数据类型还必须与同一模式中所有普通函数的名称及输入数据类型保持唯一性。此行为与普通函数名称重载规则完全一致。参见CREATE FUNCTION。

简单聚合函数由一个或两个普通函数构成：状态转换函数sfunc和可选的最终计算函数ffunc。

这些函数的使用方式如下：

sfunc( internal-state, next-data-values ) ---> next-internal-state
ffunc( internal-state ) ---> aggregate-value

数据库创建一个数据类型为state_data_type的临时变量，用于存储聚合函数的当前内部状态。在处理每行输入数据时，会计算聚合函数的参数值，并调用状态转换函数——该函数以当前状态值和新参数值为参数，计算出新的内部状态值。当所有行数据处理完毕后，最终函数将被调用一次以计算聚合函数的返回值。若未定义最终函数，则直接返回结束状态值。

聚合函数可提供初始条件，即内部状态值的初始值。该值以文本类型存储于数据库中，但必须是状态值数据类型常量的有效外部表示形式。若未提供初始值，则状态值初始化为空。

若状态转换函数声明为STRICT类型，则不能使用空值作为输入参数调用该函数。在此类转换函数下，聚合执行行为如下：包含任何空值输入的行将被忽略（函数不被调用，且保留前一状态值）。若初始状态值为空，则在首个所有输入值均非空的行处，首个参数值将替换状态值，此后所有输入值非空的行均会调用该转换函数。此特性适用于实现max()等聚合函数。需注意：该行为仅在state_data_type与第一个arg_data_type类型相同时生效。当类型不一致时，必须提供非空初始条件或使用非严格转换函数。

如果状态转换函数未声明为STRICT，则它将在每个输入行处无条件调用，并必须自行处理空值输入和空值状态。这使得聚合函数的作者能够完全掌控聚合函数对空值的处理方式。

如果最终函数声明为STRICT类型，则当结束状态值为空时不会调用该函数，而是自动返回空结果（这是STRICT函数的常规行为）。无论如何，最终函数均可选择返回空值。例如，当avg()函数检测到输入行数为零时，其最终函数会返回空值。

有时将最终函数声明为不仅接受状态值，还接受与聚合输入值对应的额外参数会很有用。这样做的主要原因是：当最终函数具有多态性时，状态值的数据类型可能不足以确定结果类型。这些额外参数始终以NULL形式传递（因此当使用FINALFUNC_EXTRA选项时，最终函数必须避免使用strict修饰），但它们仍是有效的参数。最终函数可通过调用get_fn_expr_argtype等方法，识别当前调用中的实际参数类型。

聚合函数可选地支持移动聚合模式，具体说明详见PostgreSQL文档中的移动聚合模式。这需要指定MSFUNC、MINVFUNC和MSTYPE函数，并可选地指定MSSPACE、MFINALFUNC、MFINALFUNC_EXTRA、MFINALFUNC_MODIFY和MINITCOND函数。除MINVFUNC外，这些函数的工作方式与不带M的对应简单聚合函数相同；它们定义了包含逆转换函数的独立聚合实现。

在参数列表中使用ORDER BY的语法会创建一种称为有序集合聚合的特殊聚合类型；若指定HYPOTHETICAL，则创建假设集合聚合。这些聚合以依赖顺序的方式对排序值组进行操作，因此指定输入排序顺序是调用的关键部分。此外，它们可包含直接参数——这类参数仅在每次聚合时评估一次，而非针对每行输入单独计算。假设集合聚合是有序集合聚合的子类，其中部分直接参数在数量和数据类型上必须与聚合参数列匹配。这使得这些直接参数的值能作为额外的"假设"行添加到聚合输入行集合中。

聚合函数可选地支持部分聚合。这需要指定COMBINEFUNC参数。若state_data_type为内部类型，通常还需提供SERIALFUNC和DESERIALFUNC参数以实现并行聚合。请注意，聚合函数还必须标记为PARALLEL SAFE才能启用并行聚合。

行为类似于 min() 或 max() 的聚合函数有时可通过查询索引而非扫描每行输入数据来优化。若该聚合函数可进行此类优化，请通过指定排序运算符来标识。基本要求是该聚合函数必须返回运算符所诱导排序顺序中的首项元素；换言之：

SELECT agg(col) FROM tab;

必须等同于：

SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;

进一步假设聚合函数会忽略空值输入，并且仅当所有输入均为空值时才会返回空值结果。通常情况下，数据类型的<运算符是min()的正确排序运算符，而>运算符是max()的正确排序运算符。请注意，除非指定的运算符是B树索引运算符类中的"小于"或"大于"策略成员，否则该优化策略永远不会实际生效。

要创建聚合函数，必须对参数类型、状态类型和返回类型拥有USAGE权限，并对支持函数拥有EXECUTE权限。

您可以将combinefunc指定为优化聚合执行的方法。通过指定combinefunc，聚合操作可先在分段节点并行执行，再由协调节点进行汇总。当执行两级聚合时，sfunc在分段节点运行以生成部分聚合结果，combinefunc则在协调节点运行以汇总各分段节点的部分结果。若执行单级聚合，则所有行数据将发送至协调节点，并在该节点上对行数据应用 sfunc 操作。

单层聚合与双层聚合是等效的执行策略。这两种聚合方式均可在查询计划中实现。当实现组合函数（combinefunc）和子函数（sfunc）时，必须确保对分段实例调用子函数后，再由协调器执行组合函数所产生的结果，与将所有行发送至协调器后仅对行应用子函数的单层聚合结果完全一致。

参数

name
- 要创建的聚合函数的名称（可选带模式限定）。
argmode
- 参数模式：IN 或 VARIADIC（聚合函数不支持 OUT 参数）。若省略，默认为 IN。仅最后一个参数可标记为 VARIADIC。
argname
- 参数的名称。目前仅用于文档说明。若省略，则该参数无名称。
arg_data_type
- 此聚合函数操作的输入数据类型。要创建零参数聚合函数，请用 代替参数规格列表。（此类聚合函数的示例为 count()。）
base_type
- 在旧版 CREATE AGGREGATE 语法中，输入数据类型通过基类型参数指定，而非紧邻聚合函数名称书写。请注意该语法仅允许一个输入参数。若要使用此语法定义零参数聚合函数，请将基类型指定为 "ANY"（而非 *）。有序集合聚合函数无法使用旧版语法定义。
sfunc
- 每个输入行调用的状态转换函数名称。对于常规的N参数聚合函数，状态转换函数sfunc必须接受N+1个参数，其中第一个参数类型为state_data_type，其余参数类型需与聚合函数声明的输入数据类型匹配。该函数必须返回state_data_type类型的值。此函数接收当前状态值和当前输入数据值，并返回下一个状态值。
- 对于有序集合（包括假设集合）聚合，状态转换函数仅接收当前状态值和聚合参数，而非直接参数。其余部分保持不变。
state_data_type
- 聚合状态值的数据类型。
state_data_size
- 聚合函数状态值的近似平均大小（以字节为单位）。若省略此参数或其值为零，则根据 state_data_type 使用默认估计值。规划器利用此值估算分组聚合查询所需的内存。仅当哈希表估计可容纳于工作内存时，规划器才会考虑为该查询使用哈希聚合；因此，此参数取值过大将抑制哈希聚合的使用。
ffunc
- 在遍历所有输入行后用于计算聚合结果的最终函数名称。该函数必须接受一个类型为 state_data_type 的参数。聚合函数的返回数据类型即定义为该函数的返回类型。若未指定 ffunc，则使用结束状态值作为聚合结果，且返回类型为 state_data_type。
- 对于有序集合（包括假设集合）聚合，最终函数不仅接收最终状态值，还接收所有直接参数的值。
- 若指定了 FINALFUNC_EXTRA，则最终函数除接收最终状态值和任何直接参数外，还会获得额外的 NULL 值，这些值对应于聚合函数的常规（聚合）参数。此特性主要用于在定义多态聚合函数时，确保聚合结果类型的正确解析。
FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }
- 此选项指定最终函数是否为纯函数（不修改其参数）。READ_ONLY 表示不修改；其余两个值表示该函数可能改变转换状态值。更多细节请参见下文注释。默认值为 READ_ONLY，但有序集合聚合的默认值为 READ_WRITE。
combinefunc
- combinefunc 函数可选指定，用于使聚合函数支持部分聚合。若提供该函数，combinefunc 必须将两个 state_data_type 值进行组合——每个值包含对输入值子集进行聚合后的结果——从而生成新的 state_data_type，该值代表对两组输入进行聚合后的结果。此函数可视为 sfunc 的变体：它不直接作用于单个输入行并将其添加到累积聚合状态中，而是将另一个聚合状态添加到当前累积状态中。
- 组合函数必须声明为接受两个状态数据类型的参数，并返回状态数据类型的值。该函数可选地标记为“严格”。在此情况下，当任一输入状态为空时，函数将不会被调用；此时将直接采用另一个状态作为正确结果。
- 对于状态数据类型为内部类型的聚合函数，组合函数不得为严格函数。在此情况下，组合函数必须确保空状态得到正确处理，且返回的状态在聚合内存上下文中被妥善存储。
- 在数据库中，若聚合函数的结果以分段方式计算，则会对各个内部状态调用组合函数，将其合并为最终内部状态。
- 请注意，该函数在分段内的哈希聚合模式下也会被调用。因此，若在未指定组合函数的情况下调用此聚合函数，系统将永远不会选择哈希聚合。鉴于哈希聚合的高效性，建议尽可能定义组合函数。
serialfunc
- 当聚合函数的状态数据类型为内部类型时，只有当其具备序列化函数才能参与并行聚合。该序列化函数必须将聚合状态序列化为bytea值以便传输至其他进程。此函数必须接受单个内部类型的参数，并返回bytea类型。同时还需提供对应的反序列化函数。
deserialfunc
- 将先前序列化的聚合状态反序列化回状态数据类型。该函数必须接受两个参数，类型分别为bytea和internal，并返回类型为internal的结果。（注：第二个internal参数未被使用，但出于类型安全考虑必须存在。）
initial_condition
- 状态值的初始设置。该值必须是符合数据类型 state_data_type 要求的字符串常量。若未指定，则状态值初始化为空。
msfunc
- 在移动聚合模式下，针对每行输入调用的前向状态转换函数名称。该函数与常规转换函数完全相同，区别在于其第一个参数和返回值的类型为 mstate_data_type，该类型可能与 state_data_type 不同。
minvfunc -在移动聚合模式下使用的逆向状态转换函数名称。该函数与 msfunc 具有相同的参数和结果类型，但用于从当前聚合状态中移除值，而非向其添加值。逆向转换函数必须与正向状态转换函数具有相同的严格性属性。
mstate_data_type
- 在使用移动聚合模式时，聚合状态值的数据类型。
mstate_data_size
- 使用移动聚合模式时，聚合状态值的近似平均大小（以字节为单位）。其工作原理与state_data_size相同。
mffunc
- 在移动聚合模式下，遍历所有输入行后用于计算聚合结果的最终函数名称。其作用与ffunc相同，区别在于其第一个参数类型为mstate_data_type，额外的虚拟参数通过编写MFINALFUNC_EXTRA来指定。由mffunc或mstate_data_type确定的聚合结果类型必须与聚合常规实现确定的类型一致。
MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }
- 此选项类似于 FINALFUNC_MODIFY，但描述的是移动聚合最终函数的行为。
minitial_condition
- 在使用移动聚合模式时，状态值的初始设置。其作用与初始条件相同。
sort_operator
- 用于类似 min() 或 max() 的聚合函数的关联排序运算符。这仅是一个运算符名称（可能带模式限定）。该运算符被假定具有与聚合函数相同的输入数据类型（该聚合函数必须是单参数的常规聚合函数）。
PARALLEL = { SAFE | RESTRICTED | UNSAFE }
- PARALLEL SAFE、PARALLEL RESTRICTED 和 PARALLEL UNSAFE 的含义与 CREATE FUNCTION 中相同。若聚合函数标记为PARALLEL UNSAFE（默认状态！）或PARALLEL RESTRICTED，则不会被纳入并行化考虑范围。需注意：规划器仅参考聚合函数本身的并行安全标记，不会考虑其支持函数的标记。
REPSAFE = boolean
- 指定聚合操作是否可在复制切片上安全执行。在此上下文中，顺序无关聚合将被视为安全。默认值为false。
- 设置 REPSAFE = true 将指示优化器执行额外的优化操作，这些操作专门用于抑制某些广播操作。

注意对于顺序相关的聚合函数，若错误地设置 REPSAFE = true，可能会导致结果不正确。

HYPOTHETICAL
- 仅适用于有序集合聚合函数时，此标志指定聚合函数的参数应按假设集合聚合函数的要求进行处理：即最后几个直接参数必须与聚合（WITHIN GROUP）参数的数据类型匹配。HYPOTHETICAL标志对运行时行为无影响，仅影响解析时对聚合函数参数数据类型和排序规则的解析。

CREATE AGGREGATE 语句的参数可以按任意顺序编写，而不仅限于上文所示的顺序。

示例

以下简单示例创建了一个计算两列之和的聚合函数。

在创建聚合函数之前，需先创建两个函数，它们将作为聚合函数的sfunc和combinefunc函数使用。

该函数被指定为聚合函数中的 sfunc 函数。

CREATE FUNCTION mysfunc_accum(numeric, numeric, numeric) 
  RETURNS numeric
   AS ‘select $1 + $2 + $3’
   LANGUAGE SQL
   RETURNS NULL ON NULL INPUT;

该函数在聚合函数中被指定为 combinefunc 函数。

CREATE FUNCTION mycombine_accum(numeric, numeric )
  RETURNS numeric
   AS ‘select $1 + $2’
   LANGUAGE SQL
   RETURNS NULL ON NULL INPUT;

此 CREATE AGGREGATE 命令创建用于累加两列的聚合函数。

CREATE AGGREGATE agg_twocols(numeric, numeric) (
   SFUNC = mysfunc_accum,
   STYPE = numeric,
   COMBINEFUNC = mycombine_accum,
   INITCOND = 0 );

以下命令创建表、插入数据并运行聚合函数：

CREATE TABLE t1 (a int, b int) DISTRIBUTED BY (a);
INSERT INTO t1 VALUES
   (10, 1),
   (20, 2),
   (30, 3);
SELECT agg_twocols(a, b) FROM t1;

有关创建聚合函数的更多示例，请参阅PostgreSQL文档中的《用户自定义聚合函数》。

兼容性

CREATE AGGREGATE 是一种 YMatrix 的语言扩展。SQL 标准没有提供用户定义的聚集函数。

另见

ALTER AGGREGATE, DROP AGGREGATE, CREATE FUNCTION

← 上一篇

CREATE_ACCESS_METHOD

CREATE_CAST

简体中文 English Русский

CREATE AGGREGATE

概要

描述

参数

示例

兼容性

另见

简体中文

English

Русский