CREATE AGGREGATE

定义一个新的聚集函数。

概要

  1. CREATE [ORDERED] AGGREGATE name (input_data_type [ , ... ]) 
  2.       ( SFUNC = sfunc,
  3.         STYPE = state_data_type
  4.         [, PREFUNC = prefunc]
  5.         [, FINALFUNC = ffunc]
  6.         [, INITCOND = initial_condition]
  7.         [, SORTOP = sort_operator] )

描述

CREATE AGGREGATE定义一个新的聚集函数。在发布中已经包括了一些基本的和常用的聚集函数，像count、min、max、sum、avg等在Greenplum数据库中已被提供。如果要定义一个新类型或者需要一个还没有被提供的聚集函数，那么CREATE AGGREGATE就可以被用来提供想要的特性。

一个聚集函数需要用它的名称和输入数据类型标识。同一个方案中的两个聚集可以具有相同的名称，只要它们在不同的输入类型上操作即可一个聚集的名称和输入数据类型必须与同一方案中的每一个普通函数区分开。

聚集函数由一个，两个或三个常规函数组成（所有这些函数都必须是IMMUTABLE函数）：

  • 一个状态转移函数sfunc
  • 一个可选的Segment级预备计算函数prefunc
  • 一个可选的最终计算函数ffunc

这些函数被使用如下：

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

用户可以指定PREFUNC作为优化聚集执行的方法。通过指定PREFUNC，聚集可以先在Segment上并行执行然后再在Master上执行。在执行双层聚集时，SFUNC 在Segment上被执行以产生部分聚集结果，而PREFUNC在Master上被执行以聚集来自Segment的部分结果。如果执行单层聚集，所有的行都会被发送到Master，然后在行上应用sfunc。

单层聚集和双层聚集是等效的执行策略。任何一种聚集类型都可以在查询计划中实现。当用户实现函数prefunc和sfunc时，必须确保在Segment实例上调用sfunc接着在Master上调用prefunc产生的结果和单层聚集（将所有的行发送到Master然后只在行上应用sfunc）相同。

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

聚集函数可以提供一个可选的初始条件，即一个内部状态值的初始值。在数据库中这被指定并且存储为文本类型的值，但是它必须是内部状态之数据类型的一个合法外部表达。如果不支持这种初始值，则状态值会开始于NULL。

如果状态转移函数被声明为STRICT，那么就不能用NULL输入调用它。对于这样一个转移函数，聚集执行会按下面的方式执行。带有任何空输入值的行会被忽略（该函数不会被调用并且保持之前的状态值）。如果初始状态值为NULL，那么在第一个具有所有非空输入值的行那里，第一个参数值会替换状态值，并且对后续具有所有非空输入值的行调用转移函数。这对于实现max之类的聚集很有用。注意，只有当state_data_type与第一个input_data_type相同时，这种行为才可用。当这些类型不同时，用户必须提供一个非空初始条件或者使用一个非严格的转移函数。

如果状态转移函数没有被声明为STRICT，那么对每一个输入行将无条件调用它，并且它必须自行处理NULL输入和NULL转移状态。这允许聚集的作者完全控制聚集对NULL值的处理。

如果最终函数被声明为STRICT，那么当结束状态值为NULL时不会调用它，而是自动返回一个NULL结果（这是STRICT函数的通常行为）。在任何情况下，最终函数都有返回一个NULL值的选项。例如，avg的最终函数会在它看到零个输入行时返回NULL。

单参数聚集函数（例如min和max）有时候可以通过查看索引而不是扫描每个输入行来优化。如果可以这样优化聚集，可通过指定一个排序操作符来指明。基本的要求是该聚集必须得到该操作符产生的排序顺序中的第一个元素，换句话说：

  1. SELECT agg(col) FROM tab;

必须等效于：

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

进一步的假定是聚集函数会忽略NULL输入，并且它只在没有非空输入时才给出一个NULL结果。通常，一种数据类型的<操作符是用于MIN的正确排序操作符，而>是用于MAX的正确排序操作符。注意除非指定的操作符是B-树索引操作符类中的“小于”或者“大于”策略成员，那么这种优化将不会实际生效。

有序聚集

如果出现可选的条件ORDERED，则创建的聚集函数是一种有序聚集。这种情况下，不能指定预备聚集函数prefunc。

有序聚集使用下面的语法调用。

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

如果可选的ORDER BY被省略，则会使用一种系统定义的排序。有序聚集函数的转移函数sfunc会在其输入参数上以指定顺序在单个Segment上被调用。在pg_aggregate表中有一个新列aggordered来指定聚集函数是否被定义为有序聚集。

参数

name

要创建的聚集函数的名称（可以被方案限定）。

input_data_type

这个聚集函数在其上进行操作的输入数据类型。要创建一个零参数的聚集函数，在输入数据类型的列表位置写上*。这类聚集的例子是count(*)。

sfunc

为每一个输入行调用的状态转移函数的名称。对于一个N-参数的聚集函数，sfunc必须接收N+1个参数，第一个参数类型为state_data_type而其他的匹配该聚集声明的输入数据类型。该函数必须返回一个类型为state_data_type的值。这个函数会拿到当前状态值和当前的输入数据值，并且返回下一个状态值。

state_data_type

聚集状态值的数据类型。

prefunc

预备聚集函数的名称。这是一个有两个参数的函数，参数的类型都是state_data_type。它必须返回一个state_data_type类型的值。预备函数会拿到两个转移状态值并且返回一个新的转移状态值来表示组合的聚集。在Greenplum数据库中，如果聚集函数的结果是以分Segment的方式计算，预备聚集函数就会在一个个内部状态上调用以便把它们组合成一个结束内部状态。

注意在Segment内部的哈希聚集模式中也会调用这个函数。因此，如果用户调用这个没有预备函数的聚集函数，则绝不会选中哈希聚集。由于哈希聚集很有效，因此只要可能，还是应该考虑定义预备函数。

ffunc

在所有的输入行都已经被遍历过后，要调用来计算聚集结果的最终函数的名称。这个函数必须接收类型为state_data_type的单个参数。该聚集的返回数据被定义为这个函数的返回类型。如果没有指定ffunc，那么结束状态值会被用作聚集结果并且该返回类型为state_data_type。

initial_condition

状态值的初始设置。这必须是一个数据类型state_data_type可接受形式的字符串常量。如果没有指定，状态值会开始于NULL。

sort_operator

用于MIN-或者MAX类聚集函数的相关排序操作符。这只是一个操作符名称（可能被方案限定）。该操作符被假定为具有与聚集函数（必须是一个单参数聚集函数）相同的输入数据类型。

注解

用于定义新聚集函数的普通函数必须先被定义。注意在这个Greenplum数据库的发行中，要求用于创建聚集的sfunc、ffunc以及prefunc函数被定义为IMMUTABLE。

如果在窗口表达式中使用用户定义的聚集，必须为该聚集定义prefunc函数。

如果Greenplum数据库服务器配置参数gp_enable_multiphase_agg的值为off，只有单层聚集会被执行。

任何用于自定义函数的已编译代码（共享库文件）在用户的Greenplum数据库阵列（Master和所有Segment）中的每一台主机上都必须被放置在相同的位置。这个位置还必须在LD_LIBRARY_PATH中，这样服务器可以定位到文件。

示例

下面的简单例子创建一个计算两列总和的聚集函数。

在创建聚集函数之前，创建两个被用作该函数的SFUNC和PREFUNC函数的函数。

这个函数被指定为该聚集函数中的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;

这个函数被指定为该聚集函数中的PREFUNC函数。

  1. CREATE FUNCTION mypre_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.    PREFUNC = mypre_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

上级主题： SQL命令参考