CREATE AGGREGATE

定义一个新的聚集函数

概要

argname

  1. CREATE AGGREGATE name ( [ argmode ] [ ] arg_data_type [ , ... ] ) (
  2.     SFUNC = sfunc,
  3.     STYPE = state_data_type
  4.     [ , SSPACE = state_data_size ]
  5.     [ , FINALFUNC = ffunc ]
  6.     [ , FINALFUNC_EXTRA ]
  7.     [ , COMBINEFUNC = combinefunc ]
  8.     [ , SERIALFUNC = serialfunc ]
  9.     [ , DESERIALFUNC = deserialfunc ]
  10.     [ , INITCOND = initial_condition ]
  11.     [ , MSFUNC = msfunc ]
  12.     [ , MINVFUNC = minvfunc ]
  13.     [ , MSTYPE = mstate_data_type ]
  14.     [ , MSSPACE = mstate_data_size ]
  15.     [ , MFINALFUNC = mffunc ]
  16.     [ , MFINALFUNC_EXTRA ]
  17.     [ , MINITCOND = minitial_condition ]
  18.     [ , SORTOP = sort_operator ]
  19.   )
  21.   CREATE AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]
  22.       ORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
  23.     SFUNC = sfunc,
  24.     STYPE = state_data_type
  25.     [ , SSPACE = state_data_size ]
  26.     [ , FINALFUNC = ffunc ]
  27.     [ , FINALFUNC_EXTRA ]
  28.     [ , COMBINEFUNC = combinefunc ]
  29.     [ , SERIALFUNC = serialfunc ]
  30.     [ , DESERIALFUNC = deserialfunc ]
  31.     [ , INITCOND = initial_condition ]
  32.     [ , HYPOTHETICAL ]
  33.   )
  35.   or the old syntax
  37.   CREATE AGGREGATE name (
  38.     BASETYPE = base_type,
  39.     SFUNC = sfunc,
  40.     STYPE = state_data_type
  41.     [ , SSPACE = state_data_size ]
  42.     [ , FINALFUNC = ffunc ]
  43.     [ , FINALFUNC_EXTRA ]
  44.     [ , COMBINEFUNC = combinefunc ]
  45.     [ , SERIALFUNC = serialfunc ]
  46.     [ , DESERIALFUNC = deserialfunc ]
  47.     [ , INITCOND = initial_condition ]
  48.     [ , MSFUNC = msfunc ]
  49.     [ , MINVFUNC = minvfunc ]
  50.     [ , MSTYPE = mstate_data_type ]
  51.     [ , MSSPACE = mstate_data_size ]
  52.     [ , MFINALFUNC = mffunc ]
  53.     [ , MFINALFUNC_EXTRA ]
  54.     [ , MINITCOND = minitial_condition ]
  55.     [ , SORTOP = sort_operator ]
  56.   )

描述

CREATE AGGREGATE定义一个新的聚合函数。 Greenplum数据库中已经提供了一些基本且常用的聚合函数， 例如count，min，max，sum，avg等。 如果定义了新类型或需要尚未提供的聚合函数，则可以使用CREATE AGGREGATE提供所需的功能。

如果指定了模式名称（例如CREATE AGGREGATE myschema.myagg …），则将在指定的模式中创建聚合函数。 否则，它将在当前模式中创建。

聚合函数由其名称和输入数据类型标识。 如果同一模式中的两个聚合函数对不同的输入类型进行操作，则它们可以具有相同的名称。 聚合函数的名称和输入数据类型也必须与同一模式中每个普通函数的名称和输入数据类型不同。 此行为与普通函数名称的重载相同。 请参阅CREATE FUNCTION。

一个简单的聚合函数由一个，两个或三个普通函数（必须是IMMUTABLE函数）组成：

  • 状态转换函数 sfunc
  • 可选的最终计算函数 ffunc
  • 可选的合并函数 combinefunc

这些函数的用法如下：

  1. sfunc( internal-state, next-data-values ) ---> next-internal-state
  2. ffunc( internal-state ) ---> aggregate-value
  3. combinefunc( internal-state, internal-state ) ---> next-internal-state

Greenplum数据库创建一个数据类型为stype的临时变量，以保存聚合函数的当前内部状态。 在每个输入行，将计算聚合参数值，并使用当前状态值和新参数值调用状态转换函数以计算新的内部状态值。 处理完所有行后，将一次调用最终函数以计算合计返回值。 如果没有最终函数，则按原样返回最终状态值。

您可以将combinefunc指定为优化聚合执行的方法。 通过指定combinefunc，可以首先在segment上并行执行聚合，然后在master上并行执行。 当执行两级执行时，在segment上执行sfunc以生成部分聚合结果， 而在master上执行combinefunc来聚合segment中的部分结果。 如果执行单级聚合，则将所有行发送到master，并将sfunc应用于行。

单级聚合和二级聚合是等效的执行策略。 两种类型的聚合都可以在查询计划中实现。 在实现函数combinefunc和sfunc时，必须确保在segment实例上调用sfunc， 然后在master上调用combinefunc产生与将所有行发送到master然后仅应用sfunc到行的单级聚合相同的结果。

聚合函数可以提供可选的初始条件，即内部状态值的初始值。 它是作为text类型的值指定并存储在数据库中的，但是它必须是状态值数据类型的常量的有效外部表示形式。 如果未提供，则状态值开始为NULL。

如果状态转换函数声明为STRICT，则不能使用NULL输入调用它。 使用这种转换函数，聚合执行的行为如下。 输入值为空的行将被忽略（不调用该函数，并且保留先前的状态值）。 如果初始状态值为NULL，则在具有所有非空输入值的第一行，第一个参数值将替换状态值，并且在具有所有非空输入值的后续行中调用转换函数。 这对于实现像max这样的聚合很有用。 请注意，只有当state_data_type与第一个input_data_type相同时，此行为才可用。 当这些类型不同时，必须提供一个非空的初始条件或使用一个非严格的转换函数。

如果未将状态转换函数声明为STRICT，则将在每个输入行无条件调用该状态转换函数， 并且必须自己处理NULL输入和NULL转换值。 这使聚合作者可以完全控制聚合对NULL值的处理。

如果将最终函数声明为STRICT，则当结束状态值为NULL时将不调用该函数；而是将自动返回NULL结果。 （这是STRICT函数的正常行为。）在任何情况下，最终函数都可以选择返回NULL值。 例如，当avg的最终函数看到输入行为零时，将返回NULL。

有时，将最终函数声明为不仅接受状态值，而且接受与聚合的输入值相对应的额外参数是有用的。 这样做的主要原因是，如果最终函数是多态的，并且状态值的数据类型不足以固定结果类型。 这些额外的参数始终以NULL的形式传递（因此，在使用FINALFUNC_EXTRA选项时，最后的函数一定不能严格），但是它们仍然是有效的参数。 最终函数可以例如使用get_fn_expr_argtype来标识当前调用中的实际参数类型。

聚合可以选择支持移动聚合模式，如PostgreSQL文档中的移动聚合模式中所述。 这需要指定msfunc，minfunc和mstype函数， 以及可选的mspace，mfinalfunc， mfinalfunc_extra和minitcond函数。 除了minvfunc以外，这些函数的工作方式与不带m的相应简单聚合函数相同； 它们定义了包含逆转换函数的聚合的单独实现。

参数列表中带有ORDER BY的语法会创建一种特殊的聚合类型，称为有序集聚合。 或者，如果指定了HYPOTHETICAL，则创建一个假设集合。 这些聚合以顺序相关的方式对排序值的组进行操作，因此指定输入排序顺序是调用的重要组成部分。 而且，它们可以具有直接参数，这些参数是每个聚合仅评估一次的参数，而不是每个输入行评估一次的参数。 假设集合聚合是有序集合聚合的子类，其中一些直接参数需要在数量和数据类型上与聚合参数列匹配。 这允许将那些直接自变量的值作为附加的“假设”行添加到集合输入行的集合中。

有时可以通过查看索引而不是扫描每个输入行来优化单参数聚合函数（例如min或max）。 如果可以如此优化此聚合，请通过指定排序运算符进行指示。 基本要求是，集合必须在运算符引起的排序顺序中产生第一个元素；换一种说法：

  1. SELECT agg(col) FROM tab;

必须等于：

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

进一步假设聚合函数将忽略NULL输入，并且当且仅当不存在非null输入时，它才会传递NULL结果。 通常，数据类型的<运算符是MIN的适当排序运算符，>是MAX的适当的排序运算符。 请注意，除非指定的运算符是B树索引运算符类的“小于”或“大于”策略成员，否则优化实际上不会生效。

为了能够创建聚合函数，您必须对参数类型，状态类型和返回类型具有USAGE特权，并且对过渡和最终函数具有EXECUTE特权。

参数

name

要创建的聚合函数的名称（可以由模式指定）。

argmode

参数的模式：IN或VARIADIC。 （聚合函数不支持OUT参数。）如果省略，则默认值为IN。 只有最后一个参数可以标记为VARIADIC。

argname

参数的名称。当前这仅用于文档目的。如果省略，则参数没有名称。

arg_data_type

此聚合函数在其上操作的输入数据类型。 要创建零参数聚合函数，请写*代替参数说明列表。 （此类汇总的一个示例是count(*)。）

base_type

在CREATE AGGREGATE的旧语法中，输入数据类型由basetype参数指定，而不是写在聚合名称旁边。 请注意，此语法仅允许一个输入参数。 要使用此语法定义零参数聚合函数，请将basetype指定为”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值。 当定义多态聚合时，这主要用于允许正确解析聚合结果类型。

serialfunc

仅当state_data_type为internal的聚合函数具有serialfunc函数时， 该函数才能参与并行聚合，该函数必须将聚合状态序列化为bytea值以传输到另一个进程。 该函数必须接受一个类型为internal的单个参数，并返回bytea类型。 还需要相应的deserialfunc。

deserialfunc

将先前序列化的聚合状态反序列化回state_data_type。 此函数必须接受bytea和internal类型的两个参数，并产生一个internal类型的结果。 （注意：第二个internal参数未使用，但出于类型安全的原因是必需的。）

initial_condition

状态值的初始设置。 这必须是数据类型state_data_type接受的字符串常量。 如果未指定，则状态值开始为null。

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确定的聚合结果类型必须与由聚合的常规实现确定的结果类型匹配。

minitial_condition

使用移动聚合模式时，状态值的初始设置。这与initial_condition相同。

sort_operator

类似于MIN或MAX的聚合的关联排序运算符。 这只是一个运算符名称（可以由模式指定）。 假定运算符具有与聚合相同的输入数据类型（必须是单参数常规聚合）。

HYPOTHETICAL

仅对于有序集合聚合，此标志指定将根据假设集合聚合的要求处理聚合参数： 也就是说，最后几个直接参数必须与聚合（WITHIN GROUP）参数的数据类型匹配。 HYPOTHETICAL标志对运行时行为没有影响，仅对数据类型的解析时解析和聚合参数的排序规则有影响。

combinefunc

组合函数的名称。 这是两个参数的函数，两个参数的类型均为state_data_type。 它必须返回state_data_type类型的值。 组合函数采用两个过渡状态值，并返回代表组合聚合的新过渡状态值。 在Greenplum数据库中，如果以分段方式计算聚合函数的结果，则对各个内部状态调用合并函数，以将它们组合为结束内部状态。

请注意，该函数在segment内的哈希聚合模式下也被调用。 因此，如果您在没有合并函数的情况下调用此聚合函数，则永远不会选择哈希聚合。 由于哈希聚合非常有效，因此请考虑尽可能定义一个合并函数。

注解

必须先定义用于定义新聚合函数的普通函数。 请注意，在此版本的Greenplum数据库中， 要求将用于创建聚合的sfunc，ffunc和Combinefunc函数定义为IMMUTABLE。

如果Greenplum数据库服务器配置参数gp_enable_multiphase_agg的值关闭，则仅执行单级聚合。

用于自定义函数的所有已编译代码（共享库文件）必须放在Greenplum数据库阵列（master和所有segment）中每个主机的相同位置。 此位置也必须位于LD_LIBRARY_PATH中，以便服务器可以找到文件。

在以前的Greenplum数据库版本中，有一个有序聚合的概念。 从版本6开始，可以使用以下语法将任何聚合称为有序聚合：

  1. name ( arg [ , ... ] [ORDER BY sortspec [ , ...]] )

接受ORDERED关键字是为了实现向后兼容，但会被忽略。

在以前的Greenplum数据库版本中，COMBINEFUNC选项称为PREFUNC。 为了向后兼容，它仍然被接受，作为COMBINEFUNC的同义词。

Example

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

在创建聚合函数之前，创建两个用作聚合函数的sfunc和combinefunc函数的函数。

该函数在聚合函数中指定为sfunc函数。

  1. CREATE FUNCTION mysfunc_accum(numeric, numeric, numeric)
  2.   RETURNS numeric
  3.     AS 'select $1 + $2 + $3'
  4.     LANGUAGE SQL
  5.     IMMUTABLE
  6.     RETURNS NULL ON NULL INPUT;

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

  1. CREATE FUNCTION mycombine_accum(numeric, numeric )
  2.   RETURNS numeric
  3.     AS 'select $1 + $2'
  4.     LANGUAGE SQL
  5.     IMMUTABLE
  6.     RETURNS NULL ON NULL INPUT;

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

  1. CREATE AGGREGATE agg_prefunc(numeric, numeric) (
  2.   SFUNC = mysfunc_accum,
  3.   STYPE = numeric,
  4.   COMBINEFUNC = mycombine_accum,
  5.   INITCOND = 0 );

以下命令创建一个表，添加一些行，并运行聚合函数。

  1. create table t1 (a int, b int) DISTRIBUTED BY (a);
  2. insert into t1 values
  3.     (10, 1),
  4.     (20, 2),
  5.     (30, 3);
  6. select agg_prefunc(a, b) from t1;

此EXPLAIN命令显示两阶段聚合。

  1. explain select agg_prefunc(a, b) from t1;
  3. QUERY PLAN
  4. --------------------------------------------------------------------------
  5. Aggregate (cost=1.10..1.11 rows=1 width=32)  
  6.  -> Gather Motion 2:1 (slice1; segments: 2) (cost=1.04..1.08 rows=1
  7.       width=32)
  8.      -> Aggregate (cost=1.04..1.05 rows=1 width=32)
  9.        -> Seq Scan on t1 (cost=0.00..1.03 rows=2 width=8)
  10. (4 rows)

兼容性

CREATE AGGREGATE是Greenplum数据库语言的扩展。 SQL标准不提供用户定义的聚合函数。

另见

ALTER AGGREGATE , DROP AGGREGATE , CREATE FUNCTION

Parent topic: SQL Command Reference