PolarDBPostgreSQL有哪些聚集函数_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

聚集函数从一个输入值的集合计算出一个单一值。支持部分模式的聚合函数具备参与各种优化的条件，例如并行聚合。

通用聚集函数

`array_agg` ( `anynonarray` ) → `anyarray` 将所有输入值，包括空值，收集到一个数组中。	No
`array_agg` ( `anyarray` ) → `anyarray` 将所有输入数组连接到一个更高维度的数组中。（输入必须都具有相同的维度，并且不能为空的（empty）或空值（null））。	No
`avg` ( `smallint` ) → `numeric` `avg` ( `integer` ) → `numeric` `avg` ( `bigint` ) → `numeric` `avg` ( `numeric` ) → `numeric` `avg` ( `real` ) → `double precision` `avg` ( `double precision` ) → `double precision` `avg` ( `interval` ) → `interval` 计算所有非空输入值的平均值（算术平均值）。	Yes
`bit_and` ( `smallint` ) → `smallint` `bit_and` ( `integer` ) → `integer` `bit_and` ( `bigint` ) → `bigint` `bit_and` ( `bit` ) → `bit` 计算所有非空输入值的逐位AND。	Yes
`bit_or` ( `smallint` ) → `smallint` `bit_or` ( `integer` ) → `integer` `bit_or` ( `bigint` ) → `bigint` `bit_or` ( `bit` ) → `bit` 计算所有非空输入值的逐位OR。	Yes
`bool_and` ( `boolean` ) → `boolean` 如果全部非空输入值都为真则返回真，否则返回假。	Yes
`bool_or` ( `boolean` ) → `boolean` 如果任何非空输入值为真则返回真，否则返回假。	Yes
`count` ( `*` ) → `bigint` 计算输入行的数量。	Yes
`count` ( `"any"` ) → `bigint` 计算输入值不为空的输入行的数量。	Yes
`every` ( `boolean` ) → `boolean` 这是对应`bool_and`的SQL标准的等效物。	Yes
`json_agg` ( `anyelement` ) → `json` `jsonb_agg` ( `anyelement` ) → `jsonb` 收集所有输入值，包括空值，到一个JSON数组。根据`to_json`或`to_jsonb`将值转换为JSON。	No
`json_object_agg` ( `key` `"any"`, `value` `"any"` ) → `json` `jsonb_object_agg` ( `key` `"any"`, `value` `"any"` ) → `jsonb` 将所有键/值对收集到一个JSON对象中。关键参数强制转换为文本；值参数按照`to_json`或`to_jsonb`进行转换。值可以为空，但键不能（为空）。	No
`max` ( `see text` ) → `same as input type` 计算非空输入值的最大值。适用于任何数字、字符串、日期/时间或enum类型，以及`inet`, `interval`, `money`, `oid`, `pg_lsn`,`tid`和任何这些类型的数组。	Yes
`min` ( `see text` ) → `same as input type` 计算非空输入值的最小值。可用于任何数字、字符串、日期/时间或enum类型，以及`inet`, `interval`,`money`, `oid`, `pg_lsn`,`tid`和任何这些类型的数组。	Yes
`string_agg` ( `value` `text`, `delimiter` `text` ) → `text` `string_agg` ( `value` `bytea`, `delimiter` `bytea` ) → `bytea` 连接非空输入值到字符串中。第一个值之后的每个值前面都有相应的`分隔符（delimiter）`（如果它不为空）。	No
`sum` ( `smallint` ) → `bigint` `sum` ( `integer` ) → `bigint` `sum` ( `bigint` ) → `numeric` `sum` ( `numeric` ) → `numeric` `sum` ( `real` ) → `real` `sum` ( `double precision` ) → `double precision` `sum` ( `interval` ) → `interval` `sum` ( `money` ) → `money` 计算非空输入值的总和。	Yes
`xmlagg` ( `xml` ) → `xml` 连接非空的XML输入值。	No

应该注意的是，除了count之外，这些函数在没有选择行时返回空值。特别地，行数的sum返回空（null），而不是预期的零，array_agg在没有输入行时返回空（null）而不是空数组。 coalesce函数可以在必要时用零或空数组代替空（null）。

聚合函数 array_agg,json_agg, jsonb_agg,json_object_agg, jsonb_object_agg, string_agg,和 xmlagg,以及类似的用户定义的聚合函数，根据输入值的顺序产生富有意义的不同的结果值。默认情况下，这种排序是不指定的，但可以通过在聚合调用中写入ORDER BY子句来控制。或者，从排序的子查询提供输入值通常也可以。例如：

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

注意，如果外部查询级别包含其他处理，例如关联，则此方法可能会失败，因为这可能导致子查询的输出在计算聚合之前重新排序。

### 注意

<span id="id-1.4.8.27.8.1" class="indexterm"></span><span id="id-1.4.8.27.8.2" class="indexterm"></span>

布尔聚合 `bool_and` 和 `bool_or` 对应于标准SQL聚合 `every` 和 `any` 或 `some`. 本数据库 支持 `every`, 但不支持 `any` 或 `some`, 因为标准语法中存在模糊性:

```sql
    SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;

这里ANY可以被认为是引入子查询，或者是聚合函数，如果子查询返回一行布尔值。因此，不能为这些聚合提供标准名称。

注意事项

习惯使用其他 SQL 数据库管理系统的用户可能会对count聚合应用于整个表时的性能感到失望。一个类似下面的查询：

    SELECT count(*) FROM sometable;

将需要与表大小成比例的工作：本数据库将需要扫描整个表或包含表中所有行的索引。

用于统计的聚集函数表显示了统计分析中常用的聚合函数。（这些被分离出来仅仅是为了避免使更常用的聚合列表混乱）。显示为接受numeric_type的函数可用于所有类型smallint, integer,bigint, numeric, real, 和 double precision。在描述中提及N时，它意味着所有输入表达式都非空的输入行数。在所有情况下，如果计算没有意义，则返回 null，例如当N为 0 时。

用于统计的聚集函数

`corr` ( `Y` `double precision`, `X` `double precision` ) → `double precision` 计算相关系数。	Yes
`covar_pop` ( `Y` `double precision`, `X` `double precision` ) → `double precision` 计算总体协方差。	Yes
`covar_samp` ( `Y` `double precision`, `X` `double precision` ) → `double precision` 计算样本协方差。	Yes
`regr_avgx` ( `Y` `double precision`, `X` `double precision` ) → `double precision` 计算自变量的平均值,`sum(X)/N`.	Yes
`regr_avgy` ( `Y` `double precision`, `X` `double precision` ) → `double precision` 计算因变量的平均值，`sum(Y)/N`.	Yes
`regr_count` ( `Y` `double precision`, `X` `double precision` ) → `bigint` 计算两个输入都非空的行数。	Yes
`regr_intercept` ( `Y` `double precision`, `X` `double precision` ) → `double precision` 计算由（`X`，`Y`）对决定的最小二乘拟合的线性方程的Y-截距。	Yes
`regr_r2` ( `Y` `double precision`, `X` `double precision` ) → `double precision` 计算相关系数的平方。	Yes
`regr_slope` ( `Y` `double precision`, `X` `double precision` ) → `double precision` 计算由（`X`, `Y`）对决定的最小二乘拟合的线性方程的斜率。	Yes
`regr_sxx` ( `Y` `double precision`, `X` `double precision` ) → `double precision` 计算自变量的“平方和” `sum(X^2) - sum(X)^2/N`.	Yes
`regr_sxy` ( `Y` `double precision`, `X` `double precision` ) → `double precision` 计算独立变量乘以因变量的“sum of products”， `sum(XY) - sum(X) sum(Y)/N`.	Yes
`regr_syy` ( `Y` `double precision`, `X` `double precision` ) → `double precision` 计算因变量的“平方和”， `sum(Y^2) - sum(Y)^2/N`.	Yes
`stddev` ( `numeric_type` ) → `double precision` for `real` or `double precision`, otherwise `numeric` 这是`stddev_samp`的一个历史别称。	Yes
`stddev_pop` ( `numeric_type` ) → `double precision` for `real` or `double precision`, otherwise `numeric` 计算输入值的总体标准差。	Yes
`stddev_samp` ( `numeric_type` ) → `double precision` for `real` or `double precision`, otherwise `numeric` 计算输入值的样本标准差。	Yes
`variance` ( `numeric_type` ) → `double precision` for `real` or `double precision`, otherwise `numeric` 这是 `var_samp` 的一个历史别称。	Yes
`var_pop` ( `numeric_type` ) → `double precision` for `real` or `double precision`, otherwise `numeric` 计算输入值的总体方差（总体标准差的平方）。	Yes
`var_samp` ( `numeric_type` ) → `double precision` for `real` or `double precision`, otherwise `numeric` 计算输入值的样本方差（样本标准差的平方）。	Yes

有序集聚集函数表显示了一些使用ordered-set aggregate语法的聚合函数。这些函数有时被称为“inverse distribution”函数。它们的聚合输入是通过ORDER BY引入的，它们还可以接受未聚合的direct argument，但只计算一次。所有这些函数在其聚合的输入中都忽略空（null）值。对于使用fraction(fraction)参数的函数，分数值必须在 0 到 1 之间;否则将抛出一个错误。但是，空分数值简单地产生一个空结果。

有序集聚集函数

`mode` () `WITHIN GROUP` ( `ORDER BY` `anyelement` ) → `anyelement` 计算mode，即聚合参数最频繁的值（如果有多个相同频繁的值，第一个可以任意选择）。聚合参数必须是可排序类型。	No
`percentile_cont` ( `fraction` `double precision` ) `WITHIN GROUP` ( `ORDER BY` `double precision` ) → `double precision` `percentile_cont` ( `fraction` `double precision` ) `WITHIN GROUP` ( `ORDER BY` `interval` ) → `interval` 计算continuous percentile，该值对应于聚合参数值的有序集合中的指定`分数（fraction）`。如果需要，这将在相邻的输入项之间插入。	No
`percentile_cont` ( `fractions` `double precision[]` ) `WITHIN GROUP` ( `ORDER BY` `double precision` ) → `double precision[]` `percentile_cont` ( `fractions` `double precision[]` ) `WITHIN GROUP` ( `ORDER BY` `interval` ) → `interval[]` 计算多个连续的百分位数。结果是一个与`分数（fractions）`参数具有相同维数的数组，每个非空元素都被对应于该百分位的（可能插值的）值所替换。	No
`percentile_disc` ( `fraction` `double precision` ) `WITHIN GROUP` ( `ORDER BY` `anyelement` ) → `anyelement` 计算离散百分比（discrete percentile），即聚合参数值的有序集合中的第一个值，该值在排序中的位置等于或超过指定的`fraction`。聚合参数必须是可排序类型。	No
`percentile_disc` ( `fractions` `double precision[]` ) `WITHIN GROUP` ( `ORDER BY` `anyelement` ) → `anyarray` 计算多个离散百分位数。结果是一个与`fractions`参数具有相同维数的数组，每个非空元素都被对应于该百分位的输入值替换。聚合参数必须是可排序类型。	No

列在假想集聚集函数表中的每个“hypothetical-set”聚合都与同名窗口函数相关联。在每种情况下，聚合的结果都是相关的窗口函数将为由args构造的“hypothetical”行返回的值，如果将这样的行添加到sorted_args表示的已排序行组中。对于这些函数中的每一个，args中给出的直接参数列表必须与sorted_args中给出的聚合参数的数量和类型匹配。与大多数内置聚合不同，这些聚合不是严格的，也就是说它们不会删除包含空值的输入行。空值根据ORDER BY子句中指定的规则排序。

假想集聚集函数

`rank` ( `args` ) `WITHIN GROUP` ( `ORDER BY` `sorted_args` ) → `bigint` 计算假设行的排名，包括间隔，就是说在它的对等组中第一行的行号。	No
`dense_rank` ( `args` ) `WITHIN GROUP` ( `ORDER BY` `sorted_args` ) → `bigint` 计算假设行的排名，没有间隔；这个功能有效地计数对等组。	No
`percent_rank` ( `args` ) `WITHIN GROUP` ( `ORDER BY` `sorted_args` ) → `double precision` 计算假设行的相关排行，也就是（`rank` - 1)）/ （total rows - 1）。取值范围为 0 到 1（含）。	No
`cume_dist` ( `args` ) `WITHIN GROUP` ( `ORDER BY` `sorted_args` ) → `double precision` 计算累积分布，也就是（前面或具有假设行的对等行数）/（总行数）。取值范围为 1/`N` 到 1。	No

分组操作

GROUPING ( group_by_expression(s) ) → integer

返回一个位掩码以指示哪个GROUP BY表达式没有包含在当前分组集中。比特位被分配给最右边的参数对应于最低有效位;如果对应的表达式包含在生成当前结果行的分组集的分组条件中，则每个位为0，如果不包含则为1。

GROUPING函数的参数实际上并不求值，但它们必须与相关查询级别的GROUP BY子句中给出的表达式完全匹配。例如：

    => 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)

在这里，前四行中的grouping值0表明这些已经正常分组，在两个分组列上。值1表示model没有在倒数两行中分组，值3表示无论是make还是model都没有在最后一行中分组（因此，这是所有输入行的聚合）。

MEDIAN

描述

该函数是一个假设连续分布模型的逆分布函数。它采用一个数字或日期时间值，并返回中间值或插值，该值在值排序后将成为中间值。在计算中忽略NULL。

语法

MEDIAN(expr) [ OVER (query_partition_clause) ]

参数

参数	说明
expr	可以为任何数字数据类型或者能够隐式转换为数字数据类型的非数字数据类型作为参数。

返回类型

如果只指定expr，则函数返回的数据类型与参数的数字数据类型相同。如果指定OVER子句，则数据库将确定具有最高数字优先级的参数，将其余参数隐式转换为该数据类型，并返回该数据类型。

示例

create table median_test_t_1(i interval);
insert into median_test_t_1 values(interval '1 day 1 second');
insert into median_test_t_1 values(interval '2 day 1 second');
insert into median_test_t_1 values(interval '2 day 2 second');
select median(i) from median_test_t_1;
   median    
-------------
 02 00:00:01

GROUPING_ID

描述

该函数返回与一行相关联的GROUPING位向量对应的数字。

GROUPING_ID仅适用于包含GROUP BY扩展名（如ROLLUP或CUBE）和GROUPING函数的SELECT语句。
在具有许多GROUP BY表达式的查询中，确定特定行的GROUP BY级别需要许多GROUPING函数，这会导致繁琐的SQL。GROUPING_ID在这些情况下很有用。
GROUPING_ID在功能上等效于获取多个GROUPING函数的结果，并将它们连接到一个位向量（一个由1和0组成的字符串）中。通过使用GROUPING_ID，可以避免使用多个GROUPING函数，并使行筛选条件更易于表达。使用GROUPING_ID可以更容易地进行行筛选，因为所需的行可以用GROUPING_ID=n的单个条件来标识。当在单个表中存储多个聚合级别时，该函数特别有用。

语法

GROUPING_ID(expr[, expr]...)

参数

参数	说明
expr	为`GROUP BY`子句中的一个表达式。

返回类型

返回 NUMBER数据类型。

示例

create table aggregate_functions_tb_agg(VARCHAR2_1 VARCHAR2(100),INTEGER_1 INTEGER);
insert into aggregate_functions_tb_agg(VARCHAR2_1,INTEGER_1) values('aggregate_functions_tb_agg',10);
select sum(INTEGER_1),grouping_id(VARCHAR2_1) from aggregate_functions_tb_agg group by VARCHAR2_1;
 sum | grouping 
-----+----------
  10 |        0

GROUPING

描述

该函数将超级聚合行与常规分组行区分开来。ROLLUP和CUBE等GROUP BY扩展生成超级聚合行，其中所有值的集合由null表示。使用GROUPING函数，可以将表示超级聚合行中所有值集的null与常规行中的null区分开来。

GROUPING函数中的表达式必须与GROUP BY子句中的某个表达式匹配。

语法

GROUPING(expr)

参数

参数	说明
expr	为`GROUP BY`中的一个表达式

返回类型

返回NUMBER类型数据。

如果行中expr的值是表示所有值的集合的NULL，则函数返回值1。
否则，返回零。

示例

create temp view gstest1(a,b,v)
  as values (1,1,10),(1,1,11),(1,2,12),(1,2,13),(1,3,14),
            (2,3,15),
            (3,3,16),(3,4,17),
            (4,1,18),(4,1,19);

select a, b, grouping(a,b), sum(v), count(*), max(v)
  from gstest1 group by rollup (a,b);
 a | b | grouping | sum | count | max 
---+---+----------+-----+-------+-----
 1 | 1 |        0 |  21 |     2 |  11
 1 | 2 |        0 |  25 |     2 |  13
 1 | 3 |        0 |  14 |     1 |  14
 1 |   |        1 |  60 |     5 |  14
 2 | 3 |        0 |  15 |     1 |  15
 2 |   |        1 |  15 |     1 |  15
 3 | 3 |        0 |  16 |     1 |  16
 3 | 4 |        0 |  17 |     1 |  17
 3 |   |        1 |  33 |     2 |  17
 4 | 1 |        0 |  37 |     2 |  19
 4 |   |        1 |  37 |     2 |  19
   |   |        3 | 145 |    10 |  19

GRUOP_ID

描述

该函数区分由GROUP BY规范产生的重复组。它在从查询结果中筛选出重复分组时非常有用。它返回一个NUMBER类型的值来唯一标识重复的组。此函数仅适用于包含GROUP BY子句的SELECT语句。

如果某个特定分组存在n个重复项，则GROUP_ID返回0到n-1范围内的数字。

语法

GROUP_ID()

返回类型

返回NUMBER类型数据。

示例

create table t(a int, b int, c int);
insert into t(a, b, c) values(1, 2, 3);
select a, b, c, grouping(a,b,c), group_id() from t group by cube(a,b,c) order by a, b, c, grouping(a, b, c), group_id();
 a | b | c | grouping | group_id 
---+---+---+----------+----------
 1 | 2 | 3 |        0 |        0
 1 | 2 |   |        1 |        0
 1 |   | 3 |        2 |        0
 1 |   |   |        3 |        0
   | 2 | 3 |        4 |        0
   | 2 |   |        5 |        0
   |   | 3 |        6 |        0
   |   |   |        7 |        0

COLLECT

描述

该函数将任何类型的列作为其参数，并从所选行中创建输入类型的嵌套表。要获得该函数的结果，必须在CAST函数中使用它。

若列本身是集合，则COLLECT的输出是集合的嵌套表。

语法

COLLECT(column)

参数

参数	说明
column	可以为任何类型的列。

返回类型

返回结果是输入类型的嵌套表。

示例

CREATE TABLE collect_warehouses
    ( warehouse_id       NUMBER(3) 
    , warehouse_name     VARCHAR2(35)
    , location_id        NUMBER(4)
    );
CREATE TYPE collect_warehouse_name_t AS TABLE OF VARCHAR2(35);
INSERT INTO collect_warehouses VALUES (1,'Southlake, Texas',1400);
INSERT INTO collect_warehouses VALUES (2,'San Francisco',1500);
INSERT INTO collect_warehouses VALUES (3,'New Jersey',1600);
INSERT INTO collect_warehouses VALUES (4,'Seattle, Washington',1700);
SELECT CAST(COLLECT(warehouse_name) AS collect_warehouse_name_t) "Warehouses" FROM collect_warehouses;
                               Warehouses                                
-------------------------------------------------------------------------
 {"Southlake, Texas","San Francisco","New Jersey","Seattle, Washington"}