===============  聚集函数 =============== .. container:: sect1 :name: FUNCTIONS-AGGREGATE .. container:: titlepage **聚集函数** 从一个输入值的集合计算出一个单一值。内建的通用聚集函数在\ `表 通用聚集函数 `__\ 中列出，而统计性聚集在\ `表 用于统计的聚集函数 `__\ 中列出。内建的组内有序集聚集函数在\ `表 有序集聚集函数 `__\ 中列出，而内建的组内假想集聚集在\ `表 假想集聚集函数 `__\ 中列出。与聚集函数紧密相关的分组操作在\ `表 分组操作函数 `__\ 中列出。 .. list-table:: 通用聚集函数 :header-rows: 1 :widths: 20 20 20 5 20 :class: longtable * - 函数 - 参数类型 - 返回类型 - 部分模式 - 描述 * - ``array_agg`` ``(expression)`` - 任何非数组类型 - 参数类型的数组 - No - 输入值（包括空）被连接到一个数组行和数组比较 * - ``array_agg`` ``(expression)`` - 任意数组类型 - 和参数数据类型相同 - No - 输入数组被串接到一个更高维度的数组中（输入必须都具有相同的维度并且不能为空或者NULL） * - ``avg`` ``(expression)`` - ``smallint``, ``int``, ``bigint``, ``real``, ``double precision``, ``numeric`` 或 ``interval`` - 对于任何整数类型参数是 ``numeric`` ，对于一个浮点参数是 ``double precision`` ，否则和参数数据类型相同 - Yes - 所有非空输入值的平均值（算术平均） * - ``bit_and`` ``(expression)`` - ``smallint``, ``int``, ``bigint``, 或 ``bit`` - 与参数数据类型相同 - Yes - 所有非空输入值的按位与，如果没有非空值则结果是空值 * - ``bit_or`` ``(expression)`` - ``smallint``, ``int``, ``bigint``, 或 ``bit`` - 与参数数据类型相同 - Yes - 所有非空输入值的按位或，如果没有非空值则结果是空值 * - ``bool_and`` ``(expression)`` - ``bool`` - ``bool`` - Yes - 如果所有输入值为真则结果为真，否则为假 * - ``bool_or`` ``(expression)`` - ``bool`` - ``bool`` - Yes - 至少一个输入值为真时结果为真，否则为假 * - ``count(*)`` - ``-`` - ``bigint`` - Yes - 输入的行数 * - ``count`` ``(expression)`` - ``any`` - ``bigint`` - Yes - ``expression`` 值非空的输入行的数目 * - ``every`` ``(expression)`` - ``bool`` - ``bool`` - Yes - 等价于 ``bool_and`` * - ``json_agg`` ``(expression)`` - ``any`` - ``json`` - No - 将值，包含空值，聚集成一个 JSON 数组 * - ``jsonb_agg`` ``(expression)`` - ``any`` - ``jsonb`` - No - 把值，包含空值，聚合成一个 JSON 数组 * - ``json_object_agg`` ``(name, value)`` - ``(any, any)`` - ``json`` - No - 将名字/值对聚集成一个 JSON 对象，值可以为空，但不能是名字。 * - ``jsonb_object_agg`` ``(name, value)`` - ``(any, any)`` - ``jsonb`` - No - 把名字/值对聚合成一个 JSON 对象，值可以为空，但不能是名字。 * - ``max(expression)`` - 任意数组、数字、串、日期/时间、网络或者枚举类型，或者这些类型的数组 - 与参数数据类型相同 - Yes - 所有非空输入值中 ``expression`` 的最大值 * - ``min(expression)`` - 任意数组、数字、串、日期/时间、网络或者枚举类型，或者这些类型的数组 - 与参数数据类型相同 - Yes - 所有非空输入值中 ``expression`` 的最小值 * - ``string_agg`` ``(expression,`` ``delimiter)`` - ``(text, text)`` 或 ``(bytea, bytea)`` - 与参数数据类型相同 - No - 非空输入值连接成一个串，用定界符分隔 * - ``sum`` ``(expression)`` - ``smallint``, ``int``, ``bigint``, ``real``, ``double precision``, ``numeric``, ``interval`` 或 ``money`` - 对 ``smallint`` 或 ``int`` 参数是 ``bigint`` ，对 ``bigint`` 参数是 ``numeric`` ，否则和参数数据类型相同 - Yes - 所有非空输入值的 ``expression`` 的和 * - ``xmlagg`` ``(expression)`` - ``xml`` - ``xml`` - No - 连接非空XML值 请注意，除了\ ``count``\ 以外，这些函数在没有行被选中时返回控制。尤其是\ ``sum``\ 函数在没有输入行时返回空值，而不是零，并且\ ``array_agg``\ 在这种情况返回空值而不是一个空数组。必要时可以用\ ``coalesce``\ 把空值替换成零或一个空数组。 支持\ **部分模式**\ 的聚集函数有资格参与到各种优化中，例如并行聚集。 .. note:: .. rubric:: 注意 :name: 注意 :class: title 布尔聚集\ ``bool_and``\ 和\ ``bool_or``\ 对应于标准的 SQL 聚集\ ``every``\ 和\ ``any``\ 或\ ``some``\ 。而对于\ ``any`` 和\ ``some``\ ，似乎在标准语法中有一个歧义： .. code:: programlisting SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...; 如果子查询返回一行有一个布尔值的结果，这里的\ ``ANY``\ 可以被认为是引入一个子查询，或者是作为一个聚集函数。因而标准的名称不能指定给这些聚集。 .. note:: .. rubric:: 注意 :name: 注意-1 :class: title 在把\ ``count``\ 聚集应用到整个表上时，习惯于使用其他 SQL 数据管理系统的用户可能会对它的性能感到失望。一个如下的查询： .. code:: programlisting SELECT count(*) FROM sometable; 将会要求与整个表大小成比例的工作：OushuDB 将需要扫描整个表或者整个包含表中所有行的索引。 与相似的用户定义的聚集函数一样，聚集函数\ ``array_agg``\ 、 ``json_agg``\ 、\ ``jsonb_agg``\ 、\ ``json_object_agg``\ 、\ ``jsonb_object_agg``\ 、\ ``string_agg``\ 和\ ``xmlagg``\ 会依赖输入值的顺序产生有意义的不同结果值。这个顺序默认是不用指定的，但是可以在聚集调用时使用\ ``ORDER BY``\ 子句进行控制。作为一种选择，从一个排序号的子查询来提供输入值通常会有帮助。例如： .. code:: screen SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; 注意如果外面的查询层次包含额外的处理（例如连接），这种方法可能会失败，因为这可能导致子查询的输出在计算聚集之前被重新排序。 `表 用于统计的聚集函数 `__\ 展示了通常被用在统计分析中的聚集函数（这些被隔离出来是为了不和常用聚集混淆）。其中描述提到了\ ``N``\ ，它表示对应于所有非空输入表达式的输入行的数目。在所有情况中，如果计算是无意义的，将会返回空值，例如当\ ``N``\ 为零。 .. list-table:: 用于统计的聚集函数 :widths: 10 20 :header-rows: 1 :class: longtable * - 函数 - 参数类型 - 返回类型 - 部分模式 - 描述 * - ``corr(Y, X)`` - ``double precision`` - ``double precision`` - Yes - 相关系数 * - ``covar_pop`` ``(Y``, ``X`` ``)`` - ``double precision`` - ``double precision`` - Yes - 总体协方差 * - ``covar_samp`` ``(Y``, ``X`` ``)`` - ``double precision`` - ``double precision`` - Yes - 样本协方差 * - ``regr_avgx`` ``(Y``, ``X`` ``)`` - ``double precision`` - ``double precision`` - Yes - 自变量的平均值 (``sum(X)``/ ``N``) * - ``regr_avgy`` ``(Y``, ``X`` ``)`` - ``double precision`` - ``double precision`` - Yes - 因变量的平均值 (``sum(Y)``/ ``N``) * - ``regr_count`` ``(Y``, ``X`` ``)`` - ``double precision`` - ``bigint`` - Yes - 两个表达式都不为空的输入行的数目 * - ``regr_intercept`` ``(Y``, ``X`` ``)`` - ``double precision`` - ``double precision`` - Yes - 由(``X``, ``Y``)对决定的最小二乘拟合的线性方程的y截距 * - ``regr_r2`` ``(Y``, ``X`` ``)`` - ``double precision`` - ``double precision`` - Yes - 相关系数的平方 * - ``regr_slope`` ``(Y``, ``X`` ``)`` - ``double precision`` - ``double precision`` - Yes - 由（ ``X``, ``Y``）对决定的最小二乘拟合的线性方程的斜率 * - ``regr_sxx`` ``(Y``, ``X`` ``)`` - ``double precision`` - ``double precision`` - Yes - ``sum(X^2) - sum(X)^2/`` ``N`` ``（自变量的“平方和”）`` * - ``regr_sxy`` ``(Y``, ``X``) - ``double precision`` - ``double precision`` - Yes - ``sum(X * Y) - sum(X) * sum(Y)/`` ``N`` （自变量乘以因变量的“积之合”） * - ``regr_syy`` ``(Y``, ``X`` ``)`` - ``double precision`` - ``double precision`` - Yes - ``sum(Y^2) - sum(Y)^2/`` ``N`` （因变量的“平方和”） * - ``stddev`` ``(expression)`` - ``smallint``, ``int``, ``bigint``, ``real``, ``double precision``, ``numeric`` - 浮点参数为 ``double precision``，否则为 ``numeric`` - Yes - ``stddev_samp`` 的历史别名 * - ``stddev_pop(expression)`` - ``smallint``, ``int``, ``bigint``, ``real``, ``double precision``, ``numeric`` - 浮点参数为 ``double precision``，否则为 ``numeric`` - Yes - 输入值的总体标准偏差 * - ``stddev_samp`` ``(expression)`` - ``smallint``, ``int``, ``bigint``, ``real``, ``double precision``, ``numeric`` - 浮点参数为 ``double precision``，否则为 ``numeric`` - Yes - 输入值的样本标准偏差 * - ``variance`` ``(expression)`` - ``smallint``, ``int``, ``bigint``, ``real``, ``double precision``, ``numeric`` - 浮点参数为 ``double precision``，否则为 ``numeric`` - Yes - ``var_samp`` 的历史别名 * - ``var_pop`` ``(expression)`` - ``smallint``, ``int``, ``bigint``, ``real``, ``double precision``, ``numeric`` - 浮点参数为 ``double precision``，否则为 ``numeric`` - Yes - 输入值的总体方差（总体标准偏差的平方） * - ``var_samp`` ``(expression)`` - ``smallint``, ``int``, ``bigint``, ``real``, ``double precision``, ``numeric`` - 浮点参数为 ``double precision``，否则为 ``numeric`` - Yes - 输入值的样本方差（样本标准偏差的平方） `表 有序集聚集函数 `__\ 展示了一些使用\ **有序集聚集**\ 语法的聚集函数。这些函数有时也被称为 “逆分布”函数。 .. list-table:: 有序集聚集函数 :header-rows: 1 :widths: 10 8 5 5 5 10 :class: longtable * - 函数 - 直接参数类型 - 聚集参数类型 - 返回类型 - 部分模式 - 描述 * - ``mode()`` ``WITHIN`` ``GROUP`` ``(ORDER BY`` ``sort_expression)`` - - 任何可排序类型 - 与排序表达式相同 - No - 返回最频繁的输入值（如果有多个频度相同的值就选第一个） * - ``percentile_cont`` ``(fraction)`` ``WITHIN GROUP (ORDER BY sort_expression)`` - ``double precision`` - ``double precision`` 或者 ``interval`` - 与排序表达式相同 - No - 连续百分率：返回一个对应于排序中指定分数的值，如果有必要就在相邻的输入项之间插值 * - ``percentile_cont`` ``(fractions)`` ``WITHIN GROUP`` ``(ORDER BY`` ``sort_expression)`` - ``double precision[]`` - ``double precision`` 或者 ``interval`` - 排序表达式的类型的数组 - No - 多重连续百分率：返回一个匹配 ``fractions`` 参数形状的结果数组，其中每一个非空元素都用对应于那个百分率的值替换 * - ``percentile_disc`` ``(fraction)`` ``WITHIN GROUP`` ``(ORDER BY`` ``sort_expression)`` - ``double precision`` - 一种可排序类型 - 与排序表达式相同 - No - 离散百分率：返回第一个在排序中位置等于或者超过指定分数的输入值 * - ``percentile_disc`` ``(fractions)`` ``WITHIN GROUP`` ``(ORDER BY`` ``sort_expression)`` - ``double precision[]`` - 任何可排序类型 - 排序表达式的类型的数组 - No - 多重离散百分率：返回一个匹配 ``fractions`` 参数形状的结果数组，其中每一个非空元素都用对应于那个百分率的输入值替换 所有列在\ `表 有序集聚集函数 `__\ 中的聚集会忽略它们的已 排序输入中的空值。对那些有一个\ ``fraction``\ 参数的聚 集来说，该分数值必须位于 0 和 1 之间，否则会抛出错误。不过，一个空分数值会 产生一个空结果。 每个列在\ `表 假想集聚集函数 `__\ 中的聚集都与一个定义在 `窗口函数 `__\ 中的同名窗口函数相关联。在每种情况中，聚集 结果的计算方法是：假设根据\ ``args``\ 构建的“假想”行已 经被增加到从\ ``sorted_args``\ 计算得到的已排序行分组中， 然后用相关联的窗口函数针对该行返回的值就是聚集的结果。 .. list-table:: 假想集聚集函数 :header-rows: 1 :widths: 10 8 5 5 5 10 :class: longtable * - 函数 - 直接参数类型 - 聚集参数类型 - 返回类型 - 部分模式 - 描述 * - ``rank(args)`` ``WITHIN`` ``GROUP (ORDER`` ``BY sorted_args)`` - ``VARIADIC`` ``any`` - ``VARIADIC`` ``any`` - ``bigint`` - No - 假想行的排名，为重复的行留下间隔 * - ``dense_rank`` ``(args)`` ``WITHIN`` ``GROUP (ORDER BY`` ``sorted_args)`` - ``VARIADIC`` ``any`` - ``VARIADIC`` ``any`` - ``bigint`` - No - 假想行的排名，不留间隔 * - ``percent_rank`` ``(args)`` ``WITHIN`` ``GROUP (ORDER BY`` ``sorted_args)`` - ``VARIADIC`` ``any`` - ``VARIADIC`` ``any`` - ``double precision`` - No - 假想行的相对排名，范围从 0 到 1 * - ``cume_dist`` ``(args)`` ``WITHIN GROUP`` ``(ORDER BY`` ``sorted_args)`` - ``VARIADIC`` ``any`` - ``VARIADIC`` ``any`` - ``double precision`` - No - 假想行的相对排名，范围从 1 / *N* 到 1 对于这些假想集聚集的每一个，\ ``args``\ 中给定的直接参数 列表必须匹配\ ``sorted_args``\ 中给定的聚集参数的 数量和类型。与大部分的内建聚集不同，这些聚集并不严格，即它们不会丢弃包含空值 的输入行。空值的排序根据\ ``ORDER BY``\ 子句中指定的规则进行。 .. list-table:: 分组操作函数 :header-rows: 1 :widths: auto * - 函数 - 返回类型 - 描述 * - ``GROUPING(args...)`` - ``integer`` - 整数位掩码指示哪些参数不被包括在当前分组集合中 分组操作用来与分组集合共同来 区分结果行。\ ``GROUPING``\ 操作的参数并不会被实际计算，但是它们必 须准确地匹配在相关查询层次的\ ``GROUP BY``\ 子句中给定的表达式。 最右边参数指派的位是最低有效位，如果对应的表达式被包括在产生结果行的分组 集合的分组条件中则每一位是 0，否则是 1。例如： .. code:: screen => SELECT * FROM items_sold; make | model | sales -------+-------+------- Foo | GT | 10 Foo | Tour | 20 Bar | City | 15 Bar | Sport | 5 (4 rows) => SELECT make, model, GROUPING(make,model), sum(sales) FROM items_sold GROUP BY ROLLUP(make,model); make | model | grouping | sum -------+-------+----------+----- Foo | GT | 0 | 10 Foo | Tour | 0 | 20 Bar | City | 0 | 15 Bar | Sport | 0 | 5 Foo | | 1 | 30 Bar | | 1 | 20 | | 3 | 50 (7 rows)