================ CREATE AGGREGATE ================ .. container:: refentry :name: SQL-CREATEAGGREGATE .. container:: titlepage .. container:: refnamediv .. rubric:: CREATE AGGREGATE :name: create-aggregate CREATE AGGREGATE — 定义一个新的聚集函数 .. container:: refsynopsisdiv .. rubric:: 大纲 :name: 大纲 .. code:: synopsis 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 ] [ , MINITCOND = minitial_condition ] [ , SORTOP = sort_operator ] [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ] ) 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 } ] [ , INITCOND = initial_condition ] [ , HYPOTHETICAL ] [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ] ) 或者旧的语法 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 ] ) .. container:: refsect1 :name: id-1.9.3.57.5 .. rubric:: 描述 :name: 描述 ``CREATE AGGREGATE``\ 定义一个新的聚集函数。\ ``CREATE OR REPLACE AGGREGATE``\ 将定义新的聚合函数或替换现有定义。 在发布中已经包括了一些基本的和常用的聚集函数；它们的文档请见\ `聚集函数 `__\ 。 如果要定义一个新类型或者需要一个还没有被提供的聚集函数，那么\ ``CREATE AGGREGATE``\ 就可以被用来提供想要的特性。 在替换现有定义时，参数类型、结果类型和直接参数的数量可能不会更改。 此外，新定义的类型（普通聚合、有序集聚合或假设集聚合）必须与旧定义相同。 如果给定了一个模式名（例如\ ``CREATE AGGREGATE myschema.myagg ...``\ ），那么该聚集会被创建在指定的模式中。否则它 会被创建在当前模式中。 一个聚集函数需要用它的名称和输入数据类型标识。同一个模式中的两个聚集 可以具有相同的名称，只要它们在不同的输入类型上操作即可。一个聚集的名称 和输入数据类型必须与同一模式中的每一个普通函数区分开。这种行为与普通函 数名的重载完全一样（见\ `CREATE FUNCTION `__\ ）。 一个简单的聚集函数由一个或者多个普通函数组成： 一个状态转移函数 ``sfunc``\ 和一个可选的最终 计算函数 ``ffunc``\ 。 它们被这样使用： .. code:: programlisting sfunc( internal-state, next-data-values ) ---> next-internal-state ffunc( internal-state ) ---> aggregate-value PostgreSQL创建一个数据类型 ``stype``\ 的临时变量来 保持聚集的当前内部状态。对每一个输入行，聚集参数值会被计算并且状态 转移函数会被调用，它用当前状态值和新参数值计算一个新的内部状态值。 等所有行都被处理完后，最终函数会被调用一次来计算该聚集的返回值。如果 没有最终函数，则最终的状态值会被返回。 一个聚集函数可以提供一个初始条件，即一个用于内部状态值的初始值。它被 作为一个类型\ ``text``\ 的值指定并且存储在数据库中，但是它必须 是状态值数据类型的一个常量的合法外部表示。如果没有提供，则状态值从空值 开始。 如果状态转移函数被声明为“strict”，那么不能用空值输入来 调用它。如果有这种转移函数，聚集将按照下面的行为执行。带有任何空值的 行会被忽略（函数不被调用并且之前的状态值被保持）。如果初始状态值就是 空值，那么碰到第一个没有空值的行时，状态值会被替换成第一个参数值，并且 对于每一个后续的没有空值的行都会调用该转移函数。这对实现 ``max``\ 这样的聚集很方便。注意只有当 ``state_data_type`` 和第一个 ``arg_data_type``\ 相同时， 这种行为才可用。当这些类型不同时，你必须提供一个非空初始条件或者使用 一个非严格转移函数。 如果状态转移函数不是严格的，那么在碰到每个输入行时都将会调用它，并且 它必须自行处理空值输入和空状态值。这允许聚集的作者完全控制该聚集如何 处理空值。 如果最终函数被声明为“strict”，那么当最终状态值为空时将 不会调用它，而是自动地返回一个空结果（当然，这只是严格函数的普通行为）。 在任何情况下最终函数都可以返回一个空值。例如，\ ``avg``\ 的最终函数会在看到零个 输入行时返回空。 有时候把最终函数声明成不仅采用状态值还采用对应于聚集输入值的额外参数 是有用的。这样做的主要原因是，如果最终函数是多态的，那么状态值的数据 类型将不适合于用来确定结果类型。这些额外的参数总是以 NULL 形式传递 （因此使用\ ``FINALFUNC_EXTRA``\ 选项时，最终函数不能是严格的）， 但尽管如此它们都是合法参数。例如，最终函数可以利用 ``get_fn_expr_argtype``\ 来标识当前调用中的实际参数类型。 一个聚集可以 选择支持\ *moving-aggregate mode*\ 。这要求指定 ``MSFUNC``\ 、\ ``MINVFUNC``\ 以及 ``MSTYPE``\ 参数，并且参数\ ``MSSPACE``\ 、 ``MFINALFUNC``\ 、\ ``MFINALFUNC_EXTRA`` 和\ ``MINITCOND``\ 是可选的。除了\ ``MINVFUNC``\ ， 这些参数的工作都和对应的不带\ ``M``\ 的简单聚集参数相似，它们 定义了包括一个逆向转移函数的聚集的一种独立实现。 在参数列表中带有\ ``ORDER BY``\ 的语法会创建一种被称为 *有序集聚集*\ 的特殊聚集类型。如果指定了 ``HYPOTHETICAL``\ ，则会创建一个 *假想集聚集*\ 。这些聚集以依赖排序的方法在排好序 的值上操作，因此指定一个输入排序顺序是调用过程的重要一环。还有，它们 可以有\ *直接*\ 参数，这类参数只对每次聚集计算一次，而不是对 每一个输入行计算一次。假想集聚集是有序集聚集的一个子类，其中一些直接 参数要求在数量和类型上都匹配被聚集的参数列。这允许这些直接参数的值被 当做一个附加的“假想”行被加入到聚集输入行的集合中。 一个聚集可以支持 *部分聚集*\ 。这要求指定\ ``COMBINEFUNC``\ 参数。 如果\ ``state_data_type`` 为\ ``internal``\ ，通常也可以提供\ ``SERIALFUNC``\ 和 ``DESERIALFUNC``\ 参数，这样可以让并行聚集成为可能。注意， 该聚集还必须被标记为\ ``PARALLEL SAFE``\ 以启用并行聚集。 行为与\ ``MIN``\ 或\ ``MAX``\ 相似的聚集有时可以通过 直接查看一个索引而不是扫描每一个输入行来优化。如果这个聚集可以被这样 优化，请通过指定一个\ *排序操作符*\ 来指出。基本要求是，该 聚集必须得出由该操作符产生的排序顺序中的第一个元素，换句话说： .. code:: programlisting SELECT agg(col) FROM tab; 必须等价于： .. code:: programlisting SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; 进一步的假定是该聚集忽略空输入，并且当且仅当没有非空输入时它才会返回 一个空结果。通常，一种数据类型的\ ``<``\ 操作符是 ``MIN``\ 的合适的排序操作符，而\ ``>``\ 是 ``MAX``\ 的合适的排序操作符。注意，除非指定的操作符是一个 B-树索引操作符类的“小于”或者“大于” 策略成员，优化将永远不会产生实际效果。 要能够创建一个聚集函数，你必须具有参数类型、状态类型和返回类型上的 ``USAGE``\ 特权，还有在支持函数上的 ``EXECUTE``\ 特权。 .. container:: refsect1 :name: id-1.9.3.57.6 .. rubric:: 参数 :name: 参数 .. container:: variablelist ``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``\ 的默认估计值。规划器 使用这个值来估计一个分组聚集查询所需的内存。只有估计哈希表能够放在 `work_mem `__\ 大小的内存中时，规划器才会对这类查询 使用哈希聚集。因此，对这个参数设置大的值会阻止使用哈希聚集。 ``ffunc`` 最终函数的名称，该函数在所有输入行都被遍历之后被调用来计算聚集的结果。 对于一个常规聚集，这个函数必须只接受一个类型为\ ``state_data_type``\ 的单一参数。该聚集 的返回数据类型被定义为这个函数的返回类型。如果没有指定 ``ffunc``\ ，则结束状态值 被用作聚集的结果，并且返回类型为\ ``state_data_type``\ 。 对于有序集（包括假想集）聚集，最终函数不仅接收最终状态值，还会接收所 有直接参数的值。 如果指定了\ ``FINALFUNC_EXTRA``\ ，则除了最终状态值和任何直接 参数之外，最终函数还接收额外的对应于该聚集的常规（聚集）参数的 NULL 值。 这主要用于在定义了一个多态聚集时允许正确地决定聚集的结果类型。 ``FINALFUNC_MODIFY`` = { ``READ_ONLY`` \| ``SHAREABLE`` \| ``READ_WRITE`` } 此选项指定最终函数是否为不会修改参数的纯函数。\ ``READ_ONLY``\ 表示它不会修改；其他两个值表示它可能会更改迁移状态值。请参见\ `注解 `__ 以获取更多详细信息。除了有序集合的聚合使用默认值\ ``READ_WRITE``\ ，其他默认值均为\ ``READ_ONLY``\ 。 ``combinefunc`` ``combinefunc``\ 函数可以被 有选择地指定以允许聚集函数支持部分聚集。如果提供这个函数， ``combinefunc``\ 必须组合两个 ``state_data_type``\ 值，每一个 都包含在输入值某个子集上的聚集结果，它会产生一个新的 ``state_data_type``\ 来表示在 两个输入集上的聚集结果。这个函数可以被看做是一个 ``sfunc``\ ，和后者在一个个体 输入行上操作并且把它加到运行聚集状态上不同，这个函数是把另一个聚集状态加 到运行状态上。 ``combinefunc``\ 必须被声明为 有两个\ ``state_data_type``\ 参数 并且返回一个\ ``state_data_type`` 值。这个函数可以有选择性地被标记为“strict”。在被标记的情况下， 当任何一个输入状态为空时，将不会调用该函数，而是把另一个状态当作正确的结果。 对于\ ``state_data_type``\ 为 ``internal``\ 的聚集函数， ``combinefunc``\ 不能为 strict。这种情况下， ``combinefunc``\ 必须确保 正确地处理空状态并且被返回的状态能被恰当地存储在聚集内存上下文中。 ``serialfunc`` ``state_data_type``\ 为 ``internal``\ 的一个聚集函数可以参与到并行聚集中，当且仅当它具有一个 ``serialfunc``\ 函数，该函数 必须把聚集状态序列化成一个\ ``bytea``\ 值以传送给另一个进程。这个函数 必须有一个单一的\ ``internal``\ 类型参数并且返回类型\ ``bytea``\ 。 相应地也需要一个\ ``deserialfunc``\ 。 ``deserialfunc`` 把一个之前序列化后的聚集状态反序列化为 ``state_data_type``\ 。这个函数 必须有两个类型分别为\ ``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`` 使用移动聚集模式时，状态值的初始设置。它的作用和 ``initial_condition``\ 一样。 ``sort_operator`` 一个\ ``MIN``- 类或者\ ``MAX``-类聚集的相关 排序操作符。这只是一个操作符名称（可能被模式限定）。这个操作符被 假定为具有和该聚集（必须是一个单一参数的常规聚集）相同的输入数据 类型。 ``PARALLEL =`` { ``SAFE`` \| ``RESTRICTED`` \| ``UNSAFE`` } ``PARALLEL SAFE``\ 、\ ``PARALLEL RESTRICTED``\ 和\ ``PARALLEL UNSAFE``\ 的含义和 `CREATE FUNCTION `__\ 中的相同。如果一个聚集被标记为 ``PARALLEL UNSAFE``\ （默认）或者 ``PARALLEL RESTRICTED``\ ，将不会考虑将它并行化。注意 规划器不会参考聚集的支持函数的并行安全性标记，它只会考虑聚集本身 的这类标记。 ``HYPOTHETICAL`` 只用于有序集聚集，这个标志指定聚集参数会被根据假想集聚集的要求进行处理： 即后面的直接参数必须匹配聚集（\ ``WITHIN GROUP``\ ）参数的数据 类型。\ ``HYPOTHETICAL``\ 标志在运行时没有任何效果，它只在 命令解析期间对确定数据类型和聚集参数的排序规则有用。 ``CREATE AGGREGATE``\ 的参数可以用任意顺序书写， 而无需遵照以上说明的顺序。 .. container:: refsect1 :name: SQL-CREATEAGGREGATE-NOTES .. rubric:: 注解 :name: 注解 在指定支持函数名的参数中，如果需要你可以写一个模式名，例如 ``SFUNC = public.sum``\ 。在这里不能写参数类型 — 支持函数 的参数类型是根据其他参数决定的。 通常，PostgreSQL函数是不要修改输入值的真函数。然而，一个聚合迁移函数\ *在聚合上下文中使用时*\ 被允许诈欺并修改已在迁移状态的参数。因为与每次创建一个迁移状态的新的拷贝相比，这样可以提供实质的性能提升。      同样，虽然人们一般不期望聚合最终函数修改它的输入值，但有时回避修改迁移态参数是不切实际的。这种行为必须使用\ ``FINALFUNC_MODIFY``\ 参数声明。\ ``READ_WRITE``\ 值表示最终函数以某种未指定的方式修改了迁移状态值。这个值防止将聚合用作窗口函数，并且还可以防止因共用相同的输入值和迁移函数的聚合调用而合并迁移状态。\ ``SHAREABLE``\ 值表示过渡函数不能在最终功能之后使用用，但多重最终函数调用可以对最终的迁移状态值执行调用。这个值阻止将聚合用作窗口函数，但允许合并过渡状态。（也就是说，此处所关注的优化不是重复地使用相同的最终函数，而是把不同的最终函数应用到相同的最终迁移状态值。只要所有最终功能都没有标记为\ ``READ_WRITE``\ 就被允许。） 如果一个聚集支持移动聚集模式，当该聚集被用于一个具有移动帧起点（即帧起点 模式不是\ ``UNBOUNDED PRECEDING``\ ）的窗口函数时，它将提升计 算效率。在概念上，当从底部进入窗口帧时前向转移函数会把输入值加到聚集的状 态上，而逆向转移函数会在从顶部离开帧时再次移除输入值。因此，当值被移除时， 它们总是按照被加入的相同顺序被移除。无论何时调用逆向转移函数，它都将因此 接收最近增加但是还未被移除的参数值。逆向转移函数可以假定在它移除最旧的行 之后至少有一行保留在当前状态中（当情况不是这样时，窗口函数机制会简单地开 始一次新的聚集，而不是使用逆向转移函数）。 用于移动聚集模式的前向转移函数不允许返回 NULL 作为新的状态值。如果逆向 转移函数返回 NULL，这表明逆向函数无法为这个特定的输入逆转状态计算，并且 该聚集计算因此必须从当前帧的开始位置“从零开始”重新被计算。在一些少见的情 况中，逆转正在计算中的状态值是不切实际的，这种习惯可以允许在此类情形中使用 移动聚集模式。 如果没有提供移动聚集实现，聚集仍然可以被用于移动帧，但是 只要帧起点移动，PostgreSQL都将会重新计算 整个聚集。注意不管聚集有没有支持移动聚集模式， PostgreSQL都能处理一个移动帧结束而无需重 新计算，这可以通过增加新值到聚集状态完成。这就是为什么使用聚合作为窗口函数需要最终函数只读的原因。人们认为最终函数不能破坏聚集的状 态值，这样即使已经为一组帧边界得到了一个聚集结果值，该聚集也能继续下去。 有序集聚集的语法允许为最后一个直接参数以及最后一个聚集（ ``WITHIN GROUP``\ ）参数指定\ ``VARIADIC``\ 。但是，当前的 实现限制只能以两种方式使用\ ``VARIADIC``\ 。第一种，有续集聚集只能 使用\ ``VARIADIC "any"``\ ，不能使用其他可变数组类型。第二种，如果最 后一个直接参数是\ ``VARIADIC "any"``\ ，那么只能有一个聚集参数并且它 也必须是\ ``VARIADIC "any"``\ （在系统目录中使用的表示中，这两个参数 会被合并为一个单一的\ ``VARIADIC "any"``\ 项，因为 ``pg_proc``\ 无法表示具有超过一个\ ``VARIADIC``\ 参数的 函数）。如果该聚集是一个假想集聚集，匹配\ ``VARIADIC "any"``\ 参数的 直接参数就是假想参数。任何在前面的参数表示额外的直接参数，它们不被约束为需 要匹配聚集参数。 当前，有序集聚集无须支持移动聚集模式，因为它们不能被用作窗口函数。 部分（包括并行）聚集当前不被有续集聚集支持。还有，包括 ``DISTINCT``\ 或者\ ``ORDER BY``\ 子句的聚集调用将不会使用 部分聚集，因为在部分聚集时无法支持那些语义。 .. container:: refsect1 :name: id-1.9.3.57.9 .. rubric:: 兼容性 :name: 兼容性 ``CREATE AGGREGATE``\ 是一种 OushuDB 的语言扩展。SQL 标准没有提供 用户定义的聚集函数。 .. container:: refsect1 :name: id-1.9.3.57.10 .. rubric:: 另见 :name: 另见 `ALTER AGGREGATE `__, `DROP AGGREGATE `__