GaussDB-聚集函数

jerrywang1983
101 阅读22分钟

GaussDB-聚集函数

聚集函数

  • sum(expression)

    描述：所有输入行的expression总和。

    返回类型：

    通常情况下输入数据类型和输出数据类型是相同的，但以下情况会发生类型转换：

    • 对于SMALLINT或INT输入，输出类型为BIGINT。
    • 对于BIGINT输入，输出类型为NUMBER 。
    • 对于浮点数输入，输出类型为DOUBLE PRECISION。

    示例：

    | ``` gaussdb=# CREATE TABLE tab(a int); CREATE TABLE gaussdb=# INSERT INTO tab values(1); INSERT 0 1 gaussdb=# INSERT INTO tab values(2); INSERT 0 1 gaussdb=# SELECT sum(a) FROM tab; sum ----- 3 (1 row) gaussdb=# DROP TABLE tab;

    | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • max(expression)

    描述：所有输入行中expression的最大值。

    参数类型：任意数组、数值、字符串、日期/时间类型。

    返回类型：与参数数据类型相同

    示例：

    | ``` gaussdb=# CREATE TABLE max_t1(a int, b int); gaussdb=# INSERT INTO max_t1 VALUES(1,2),(2,3),(3,4),(4,5); gaussdb=# SELECT MAX(a) FROM max_t1; max ----- 4 (1 row) gaussdb=# DROP TABLE max_t1;

    | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • min(expression)

    描述：所有输入行中expression的最小值。

    参数类型：任意数组、数值、字符串、日期/时间类型。

    返回类型：与参数数据类型相同

    示例：

    | ``` gaussdb=# CREATE TABLE min_t1(a int, b int); gaussdb=# INSERT INTO min_t1 VALUES(1,2),(2,3),(3,4),(4,5); gaussdb=# SELECT MIN(a) FROM min_t1; min ----- 1 (1 row) gaussdb=# DROP TABLE min_t1;

    | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • avg(expression)

    描述：所有输入值的均值（算术平均）。

    返回类型：

    对于任何整数类型输入，结果都是NUMBER类型。

    对于任何浮点输入，结果都是DOUBLE PRECISION类型。

    否则和输入数据类型相同。

    示例：

    | ``` gaussdb=# CREATE TABLE avg_t1(a int, b int); gaussdb=# INSERT INTO avg_t1 VALUES(1,2),(2,3),(3,4),(4,5); gaussdb=# SELECT AVG(a) FROM avg_t1; avg -------------------- 2.5000000000000000 (1 row) gaussdb=# DROP TABLE avg_t1;

    | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |

  • count(expression)

    描述：返回表中满足expression不为NULL的行数。

    返回类型：BIGINT

    支持对XML类型数据操作。

    示例：

    | ``` gaussdb=# CREATE TABLE count_t1(a int, b int); gaussdb=# INSERT INTO count_t1 VALUES (NULL,1),(1,2),(2,3),(3,4),(4,5); gaussdb=# SELECT COUNT(a) FROM count_t1; count ------- 4 (1 row) gaussdb=# DROP TABLE count_t1;

    | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • count(*)

    描述：返回表中的记录行数。

    返回类型：BIGINT

    支持对XML类型数据操作。

    示例：

    | ``` gaussdb=# CREATE TABLE count_t1(a int, b int); gaussdb=# INSERT INTO count_t1 VALUES (NULL,1),(1,2),(2,3),(3,4),(4,5); gaussdb=# SELECT COUNT(*) FROM count_t1; count ------- 5 (1 row) gaussdb=# DROP TABLE count_t1;

    | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • default(column_name)

    描述：获取表字段的默认值。

    返回值类型：text

    示例：

    | ``` --创建兼容性MYSQL数据库。 gaussdb=# CREATE DATABASE gaussdb_m WITH DBCOMPATIBILITY 'MYSQL'; gaussdb=# \c gaussdb_m gaussdb_m=# CREATE TABLE t1(id int DEFAULT 100, name varchar(20) DEFAULT 'tt'); gaussdb_m=# INSERT INTO t1 VALUES(1,'test'); --执行查询。 gaussdb_m=# SELECT default(id), default(name) FROM t1; default | default ---------+--------- 100 | tt (1 row) --删除数据库。 gaussdb_m=# \c postgres gaussdb=# DROP DATABASE gaussdb_m;

    | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

![](https://p6-xtjj-sign.byteimg.com/tos-cn-i-73owjymdk6/d1b5e73122f94903bf2a627199c456f3~tplv-73owjymdk6-jj-mark-v1:0:0:0:0:5o6Y6YeR5oqA5pyv56S-5Yy6IEAgamVycnl3YW5nMTk4Mw==:q75.awebp?rk3s=f64ab15b&x-expires=1771403304&x-signature=F%2BLZ3VFOjUxyqvfT3m1EFHKI2O0%3D)

-   default函数仅在参数sql_compatibility='MYSQL'时生效。
-   表字段中不存在默认值时，default函数返回空值。
-   表字段是隐藏列（如xmin、cmin）时，default函数返回空值。
-   表字段是自增列时，default函数返回0。
-   GaussDB支持分区表、临时表、多表连接查询默认值。
-   GaussDB支持查询列名包含字符串值节点（表示名称）和A_Star节点（表示出现“*”），如default(tt.t4.id)和default(tt.t4.*)。
-   GaussDB创建字段默认值时，如果没有检验字段类型的范围，使用default函数可能会报错。
-   字段的默认值是函数表达式时，GaussDB的default函数返回建表时字段的default表达式的计算值。

  • array_agg(expression)

    描述：将所有输入值（包括空）连接成一个数组。

    返回类型：参数类型的数组

    支持对XML类型数据操作。

    示例：

    | ``` gaussdb=# CREATE TABLE array_agg_t1(a int, b int); gaussdb=# INSERT INTO array_agg_t1 VALUES (NULL,1),(1,2),(2,3),(3,4),(4,5); gaussdb=# SELECT ARRAY_AGG(a) FROM array_agg_t1; array_agg ---------------- {NULL,1,2,3,4} (1 row) gaussdb=# DROP TABLE array_agg_t1;

    | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • string_agg(expression, delimiter)

    描述：将输入值连接成为一个字符串，用分隔符分开。

    返回类型：和参数数据类型相同。

    支持对显示转换成字符类型后的XML类型数据操作。

    示例：

    | ``` gaussdb=# CREATE TABLE string_agg_t1(a int, b int); gaussdb=# INSERT INTO string_agg_t1 VALUES (NULL,1),(1,2),(2,3),(3,4),(4,5); gaussdb=# SELECT STRING_AGG(a,';') FROM string_agg_t1; string_agg ------------ 1;2;3;4 (1 row) gaussdb=# DROP TABLE string_agg_t1;

    | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • listagg(expression [, delimiter]) WITHIN GROUP(ORDER BY order-list)

    描述：将聚集列数据按WITHIN GROUP指定的排序方式排列，并用delimiter指定的分隔符拼接成一个字符串。

    • expression：必选。指定聚集列名或基于列的有效表达式，不支持DISTINCT关键字和VARIADIC参数。
    • delimiter：可选。指定分隔符，可以是字符串常数或基于分组列的确定性表达式，缺省时表示分隔符为空。
    • order-list：必选。指定分组内的排序方式。

    返回类型：text

    listagg是兼容Oracle 11g2的列转行聚集函数，可以指定OVER子句用作窗口函数。为了避免与函数本身WITHIN GROUP子句的ORDER BY造成二义性，listagg用作窗口函数时，OVER子句不支持ORDER BY的窗口排序或窗口框架。

    示例：

    聚集列是文本字符集类型。

    | ``` gaussdb=# CREATE TABLE listagg_t1(a int, b text); gaussdb=# INSERT INTO listagg_t1 VALUES (NULL,'a1'),(1,'b2'),(1,'c3'),(2,'d4'),(2,'e5'),(3,'f6'); gaussdb=# SELECT a,LISTAGG(b,';') WITHIN GROUP(ORDER BY b) FROM listagg_t1 group by a; a | listagg ---+--------- 1 | b2;c3 2 | d4;e5 3 | f6 | a1 (4 rows) gaussdb=# DROP TABLE listagg_t1;

    | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |

聚集列是整型。

| ```
gaussdb=# CREATE TABLE listagg_t1(a int, b int);  gaussdb=# INSERT INTO listagg_t1 VALUES (NULL,1),(1,2),(1,3),(2,4),(2,5),(3,6);  gaussdb=# SELECT a,LISTAGG(b,';') WITHIN GROUP(ORDER BY b) FROM listagg_t1 group by a;  a | listagg  ---+---------  1 | 2;3  2 | 4;5  3 | 6    | 1 (4 rows)  gaussdb=# DROP TABLE listagg_t1; 
``` |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

聚集列是浮点类型。

| ```
gaussdb=# CREATE TABLE listagg_t1(a int, b float);  gaussdb=# INSERT INTO listagg_t1 VALUES (NULL,1.111),(1,2.222),(1,3.333),(2,4.444),(2,5.555),(3,6.666);  gaussdb=# SELECT a,LISTAGG(b,';') WITHIN GROUP(ORDER BY b) FROM listagg_t1 group by a;  a |      listagg       ---+-------------------  1 | 2.222000;3.333000  2 | 4.444000;5.555000  3 | 6.666000    | 1.111000 (4 rows)  gaussdb=# DROP TABLE listagg_t1; 
``` |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

聚集列是时间类型。

| ```
gaussdb=# CREATE TABLE listagg_t1(a int, b timestamp);  gaussdb=# INSERT INTO listagg_t1 VALUES (NULL,'2000-01-01'),(1,'2000-02-02'),(1,'2000-03-03'),(2,'2000-04-04'),(2,'2000-05-05'),(3,'2000-06-06');  gaussdb=# SELECT a,LISTAGG(b,';') WITHIN GROUP(ORDER BY b) FROM listagg_t1 group by a;  a |                 listagg                  ---+-----------------------------------------  1 | 2000-02-02 00:00:00;2000-03-03 00:00:00  2 | 2000-04-04 00:00:00;2000-05-05 00:00:00  3 | 2000-06-06 00:00:00    | 2000-01-01 00:00:00 (4 rows)  gaussdb=# DROP TABLE listagg_t1; 
``` |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

聚集列是时间间隔类型。

| ```
gaussdb=# CREATE TABLE listagg_t1(a int, b interval);  gaussdb=# INSERT INTO listagg_t1 VALUES (NULL,'1 days'),(1,'2 days'),(1,'3 days'),(2,'4 days'),(2,'5 days'),(3,'6 days');  gaussdb=# SELECT a,LISTAGG(b,';') WITHIN GROUP(ORDER BY b) FROM listagg_t1 group by a;  a |    listagg     ---+---------------  1 | 2 days;3 days  2 | 4 days;5 days  3 | 6 days    | 1 day (4 rows)  gaussdb=# DROP TABLE listagg_t1; 
``` |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

分隔符缺省时，默认为空。

| ```
gaussdb=# CREATE TABLE listagg_t1(a int, b interval);  gaussdb=# INSERT INTO listagg_t1 VALUES (NULL,'1 days'),(1,'2 days'),(1,'3 days'),(2,'4 days'),(2,'5 days'),(3,'6 days');  gaussdb=# SELECT a,LISTAGG(b) WITHIN GROUP(ORDER BY b) FROM listagg_t1 group by a;  a |   listagg     ---+--------------  1 | 2 days3 days  2 | 4 days5 days  3 | 6 days    | 1 day (4 rows)  gaussdb=# DROP TABLE listagg_t1; 
``` |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

listagg作为窗口函数时，OVER子句不支持ORDER BY的窗口排序，listagg列为对应分组的有序聚集。

| ```
gaussdb=# CREATE TABLE listagg_t1(a int, b interval);  gaussdb=# INSERT INTO listagg_t1 VALUES (NULL,'1 days'),(1,'2 days'),(1,'3 days'),(2,'4 days'),(2,'5 days'),(3,'6 days');  gaussdb=# SELECT a,LISTAGG(b) WITHIN GROUP(ORDER BY b) OVER(PARTITION BY a) FROM listagg_t1;  a |   listagg     ---+--------------  1 | 2 days3 days  1 | 2 days3 days  2 | 4 days5 days  2 | 4 days5 days  3 | 6 days    | 1 day (6 rows)  gaussdb=# DROP TABLE listagg_t1; 
``` |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • group_concat([DISTINCT | ALL] expression [,expression ...] [ORDER BY { expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ] } [,...]] [SEPARATOR str_val])

    描述：参数数量不定，可对多列进行拼接，将聚集列数据按照ORDER BY指定的排序方式排列，并用separator指定的分隔符拼接成一个字符串, 不支持作为窗口函数使用。

    • DISTINCT：可选，表示对每行拼接后结果进行去重。
    • expression：必选，指定聚集列名或基于列的有效表达式。
    • ORDER BY: 可选，后跟可变数量表达式及排序规则。group_concat函数中不支持（ORDER BY + 数字）形式。
    • SEPARATOR子句: 可选，后跟字符或字符串，分组中相邻两行表达式结果使用此分隔符拼接。若不指定，默认使用英文逗号‘,’。
    • 当同时指定DISTINCT和ORDER BY时，ORDER BY表达式必须在distinct表达式中，否则报错。

    返回类型：text

    示例：

    使用separator指定分隔符为';'。

    | ``` gaussdb=# CREATE TABLE group_concat_t1(a int, b int); gaussdb=# INSERT INTO group_concat_t1 VALUES (NULL,1),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT a,group_concat(b separator ';') FROM group_concat_t1 GROUP BY a ORDER BY a; a | group_concat ---+-------------- 1 | 2;3 2 | 4;5 3 | 6 | 1 (4 rows) gaussdb=# DROP TABLE group_concat_t1;

    | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |

分隔符缺省时，默认为','。

| ```
gaussdb=# CREATE TABLE group_concat_t1(a int, b int);  gaussdb=# INSERT INTO group_concat_t1 VALUES (NULL,1),(1,2),(1,3),(2,4),(2,5),(3,6);  gaussdb=# SELECT a,group_concat(a,b) FROM group_concat_t1 GROUP BY a ORDER BY a;  a | group_concat  ---+--------------  1 | 12,13  2 | 24,25  3 | 36    |  (4 rows)  gaussdb=# DROP TABLE group_concat_t1; 
``` |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

聚集列是文本字符集类型。

| ```
gaussdb=# CREATE TABLE group_concat_t1(a int, b text);  gaussdb=# INSERT INTO group_concat_t1 VALUES (NULL,'a1'),(1,'b2'),(1,'c3'),(2,'d4'),(2,'e5'),(3,'f6');  gaussdb=# SELECT a,group_concat(a,b) FROM group_concat_t1 GROUP BY a ORDER BY a;  a | group_concat  ---+--------------  1 | 1b2,1c3  2 | 2d4,2e5  3 | 3f6    |  (4 rows)  gaussdb=# DROP TABLE group_concat_t1; 
``` |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

聚集列是整型。

| ```
gaussdb=# CREATE TABLE group_concat_t1(a int, b int);  gaussdb=# INSERT INTO group_concat_t1 VALUES (NULL,1),(1, 2),(1, 3),(2, 4),(2, 5),(3,6);  gaussdb=# SELECT a,group_concat(b) FROM group_concat_t1 GROUP BY a ORDER BY a;  a | group_concat  ---+--------------  1 | 2,3  2 | 4,5  3 | 6    | 1 (4 rows)  gaussdb=# DROP TABLE group_concat_t1; 
``` |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

聚集列是浮点类型。

| ```
gaussdb=# CREATE TABLE group_concat_t1(a int, b float);  gaussdb=# INSERT INTO group_concat_t1 VALUES (NULL,1.11),(1,2.22),(1,3.33),(2,4.44),(2,5.55),(3,6.66);  gaussdb=# SELECT a,group_concat(b) FROM group_concat_t1 GROUP BY a ORDER BY a;  a | group_concat  ---+--------------  1 | 3,2,2,3  2 | 6,4,5,4  3 | 7,6    | 1,1 (4 rows)  gaussdb=# DROP TABLE group_concat_t1; 
``` |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |

聚集列是时间类型。

| ```
gaussdb=# CREATE TABLE group_concat_t1(a int, b timestamp);  gaussdb=# INSERT INTO group_concat_t1 VALUES (NULL,'2000-01-01'),(1,'2000-02-02'),(1,'2000-03-03'),(2,'2000-04-04'),(2,'2000-05-05'),(3,'2000-06-06');  gaussdb=# SELECT a,group_concat(b) FROM group_concat_t1 GROUP BY a ORDER BY a;  a |              group_concat                ---+-----------------------------------------  1 | 2000-02-02 00:00:00,2000-03-03 00:00:00  2 | 2000-04-04 00:00:00,2000-05-05 00:00:00  3 | 2000-06-06 00:00:00    | 2000-01-01 00:00:00 (4 rows)  gaussdb=# DROP TABLE group_concat_t1; 
``` |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

聚集列是二进制类型。

| ```
gaussdb=# CREATE TABLE group_concat_t1(a int, b bytea);  gaussdb=# INSERT INTO group_concat_t1 VALUES (NULL,'1'),(1,'2'),(1,'3'),(2,'4'),(2,'5'),(3,'6');  gaussdb=# SELECT a,group_concat(b) FROM group_concat_t1 GROUP BY a ORDER BY a;  a | group_concat  ---+--------------  1 | \x32,\x33  2 | \x34,\x35  3 | \x36    | \x31 (4 rows)  gaussdb=# DROP TABLE group_concat_t1; 
``` |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |

聚集列是时间间隔类型。

| ```
gaussdb=# CREATE TABLE group_concat_t1(a int, b interval);  gaussdb=# INSERT INTO group_concat_t1 VALUES (NULL,'1 days'),(1,'2 days'),(1,'3 days'),(2,'4 days'),(2,'5 days'),(3,'6 days');  gaussdb=# SELECT a,group_concat(b) FROM group_concat_t1 GROUP BY a ORDER BY a;  a | group_concat   ---+---------------  1 | 2 days,3 days  2 | 4 days,5 days  3 | 6 days    | 1 day (4 rows)  gaussdb=# DROP TABLE group_concat_t1; 
``` |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

使用distinct去重。

| ```
gaussdb=# CREATE TABLE group_concat_t1(a int, b interval);  gaussdb=# INSERT INTO group_concat_t1 VALUES (NULL,'1 days'),(1,'2 days'),(1,'2 days'),(1,'3 days'),(1,'3 days'),(2,'4 days'),(2,'5 days'),(3,'6 days');  gaussdb=# SELECT a,group_concat(distinct b) FROM group_concat_t1 GROUP BY a ORDER BY a;  a | group_concat   ---+---------------  1 | 2 days,3 days  2 | 4 days,5 days  3 | 6 days    | 1 day (4 rows)  gaussdb=# DROP TABLE group_concat_t1; 
``` |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

使用ORDER BY排序。

| ```
gaussdb=# CREATE TABLE group_concat_t1(a int, b interval);  gaussdb=# INSERT INTO group_concat_t1 VALUES (NULL,'1 days'),(1,'2 days'),(1,'3 days'),(2,'4 days'),(2,'5 days'),(3,'6 days');  gaussdb=# SELECT a,group_concat(b ORDER BY b desc) FROM group_concat_t1 GROUP BY a ORDER BY a;  a | group_concat   ---+---------------  1 | 3 days,2 days  2 | 5 days,4 days  3 | 6 days    | 1 day (4 rows)  gaussdb=# DROP TABLE group_concat_t1; 
``` |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • wm_concat(expression)

    描述：将列数据连接成为一个字符串，用','进行分隔。

    返回类型：和参数数据类型相同。

    wm_concat是ORA兼容性需求，目前ORA最新版本已经取消此函数，在ORA中目前使用listagg函数对功能进行替代。目前此函数功能可使用listagg函数或string_agg进行替代，使用具体方法见上述两函数描述。

  • covar_pop(Y, X)

    描述：总体协方差。

    返回类型：double precision

    示例：

    | ``` gaussdb=# CREATE TABLE covar_pop_t1(a int, b int); gaussdb=# INSERT INTO covar_pop_t1 VALUES (NULL,11),(11,21),(11,31),(21,41),(21,51),(31,61); gaussdb=# SELECT COVAR_POP(a,b) FROM covar_pop_t1; covar_pop ----------- 100 (1 row) gaussdb=# DROP TABLE covar_pop_t1;

    | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • covar_samp(Y, X)

    描述：样本协方差。

    返回类型：double precision

    示例：

    | ``` gaussdb=# CREATE TABLE covar_samp_t1(a int, b int); gaussdb=# INSERT INTO covar_samp_t1 VALUES (NULL,11),(11,21),(11,31),(21,41),(21,51),(31,61); gaussdb=# SELECT COVAR_SAMP(a,b) FROM covar_samp_t1; covar_samp ------------ 125 (1 row) gaussdb=# DROP TABLE covar_samp_t1;

    | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • stddev_pop(expression)

    描述：总体标准差。

    返回类型：对于浮点类型的输入返回double precision，其他输入返回numeric。

    示例：

    | ``` gaussdb=# CREATE TABLE stddev_pop_t1(a int, b int); gaussdb=# INSERT INTO stddev_pop_t1 VALUES (NULL,11),(11,21),(11,31),(21,41),(21,51),(31,61); gaussdb=# SELECT STDDEV_POP(a) FROM stddev_pop_t1; stddev_pop -------------------- 7.4833147735478828 (1 row) gaussdb=# DROP TABLE stddev_pop_t1;

    | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • stddev_samp(expression)

    描述：样本标准差。

    返回类型：对于浮点类型的输入返回double precision，其他输入返回numeric。

    示例：

    | ``` gaussdb=# CREATE TABLE stddev_samp_t1(a int, b int); gaussdb=# INSERT INTO stddev_samp_t1 VALUES (NULL,11),(11,21),(11,31),(21,41),(21,51),(31,61); gaussdb=# SELECT STDDEV_SAMP(a) FROM stddev_samp_t1; stddev_samp -------------------- 8.3666002653407555 (1 row) gaussdb=# DROP TABLE stddev_samp_t1;

    | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • var_pop(expression)

    描述：总体方差（总体标准差的平方）。

    返回类型：对于浮点类型的输入返回double precision类型，其他输入返回numeric类型。

    示例：

    | ``` gaussdb=# CREATE TABLE var_pop_t1(a int, b int); gaussdb=# INSERT INTO var_pop_t1 VALUES (NULL,11),(11,21),(11,31),(21,41),(21,51),(31,61); gaussdb=# SELECT VAR_POP(a) FROM var_pop_t1; var_pop --------------------- 56.0000000000000000 (1 row) gaussdb=# DROP TABLE var_pop_t1;

    | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • var_samp(expression)

    描述：样本方差（样本标准差的平方）。

    返回类型：对于浮点类型的输入返回double precision类型，其他输入返回numeric类型。

    示例：

    | ``` gaussdb=# CREATE TABLE var_samp_t1(a int, b int); gaussdb=# INSERT INTO var_samp_t1 VALUES (NULL,11),(11,21),(11,31),(21,41),(21,51),(31,61); gaussdb=# SELECT VAR_SAMP(a) FROM var_samp_t1; var_samp --------------------- 70.0000000000000000 (1 row) gaussdb=# DROP TABLE var_samp_t1;

    | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • bit_and(expression)

    描述：所有非NULL输入值的按位与(AND)，如果全部输入值皆为NULL，那么结果也为NULL 。

    返回类型：和参数数据类型相同。

    示例：

    | ``` gaussdb=# CREATE TABLE bit_and_t1(a int, b int); gaussdb=# INSERT INTO bit_and_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT BIT_AND(a) FROM bit_and_t1; bit_and --------- 0 (1 row) gaussdb=# DROP TABLE bit_and_t1;

    | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • bit_or(expression)

    描述：所有非NULL输入值的按位或(OR)，如果全部输入值皆为NULL，那么结果也为NULL。

    返回类型：和参数数据类型相同

    示例：

    | ``` gaussdb=# CREATE TABLE bit_or_t1(a int, b int); gaussdb=# INSERT INTO bit_or_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT BIT_OR(a) FROM bit_or_t1; bit_or -------- 3 (1 row) gaussdb=# DROP TABLE bit_or_t1;

    | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • bool_and(expression)

    描述：如果所有输入值都是真，则为真，否则为假。

    返回类型：bool

    示例：

    | ``` gaussdb=# SELECT bool_and(100 <2500); bool_and ---------- t (1 row)

    | ------------------------------------------------------------------------------ |

  • bool_or(expression)

    描述：如果所有输入值只要有一个为真，则为真，否则为假。

    返回类型：bool

    示例：

    | ``` gaussdb=# SELECT bool_or(100 <2500); bool_or ---------- t (1 row)

    | ---------------------------------------------------------------------------- |

  • corr(Y, X)

    描述：相关系数。

    返回类型：double precision

    示例：

    | ``` gaussdb=# CREATE TABLE corr_t1(a int, b int); gaussdb=# INSERT INTO corr_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT CORR(a,b) FROM corr_t1; corr ------------------ .944911182523068 (1 row) gaussdb=# DROP TABLE corr_t1;

    | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |

  • every(expression)

    描述：等效于bool_and。

    返回类型：bool

    示例：

    | ``` gaussdb=# SELECT every(100 <2500); every ------- t (1 row)

    | --------------------------------------------------------------------- |

  • rank(expression)

    描述：根据expression对不同组内的元组进行跳跃排序。

    返回类型：BIGINT

    示例：

    | ``` gaussdb=# CREATE TABLE rank_t1(a int, b int); gaussdb=# INSERT INTO rank_t1 VALUES(NULL,1),(1, 2),(1, 3),(2, 4),(2, 5),(3,6); gaussdb=# SELECT a,b,RANK() OVER(PARTITION BY a ORDER BY b) FROM rank_t1; a | b | rank ---+---+------ 1 | 2 | 1 1 | 3 | 2 2 | 4 | 1 2 | 5 | 2 3 | 6 | 1 | 1 | 1 (6 rows) gaussdb=# DROP TABLE rank_t1;

    | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • regr_avgx(Y, X)

    描述：自变量的平均值 (sum(X)/Y)。

    返回类型：double precision

    示例：

    | ``` gaussdb=# CREATE TABLE regr_t1(a int, b int); gaussdb=# INSERT INTO regr_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT REGR_AVGX(a,b) FROM regr_t1; regr_avgx ----------- 4 (1 row) gaussdb=# DROP TABLE regr_t1;

    | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • regr_avgy(Y, X)

    描述：因变量的平均值 (sum(Y)/X)。

    返回类型：double precision

    示例：

    | ``` gaussdb=# CREATE TABLE regr_avgy_t1(a int, b int); gaussdb=# INSERT INTO regr_avgy_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT REGR_AVGY(a,b) FROM regr_avgy_t1; regr_avgy ----------- 1.8 (1 row) gaussdb=# DROP TABLE regr_avgy_t1;

    | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • regr_count(Y, X)

    描述：两个表达式都不为NULL的输入行数。

    返回类型：bigint

    示例：

    | ``` gaussdb=# CREATE TABLE regr_count_t1(a int, b int); gaussdb=# INSERT INTO regr_count_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT REGR_COUNT(a,b) FROM regr_count_t1; regr_count ------------ 5 (1 row) gaussdb=# DROP TABLE regr_count_t1;

    | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |

  • regr_intercept(Y, X)

    描述：根据所有输入的点(X, Y)按照最小二乘法拟合成一个线性方程，然后返回该直线的Y轴截距。

    返回类型：double precision

    示例：

    | ``` gaussdb=# CREATE TABLE regr_intercept_t1(a int, b int); gaussdb=# INSERT INTO regr_intercept_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT REGR_INTERCEPT(b,a) FROM regr_intercept_t1; regr_intercept ------------------ .785714285714286 (1 row) gaussdb=# DROP TABLE regr_intercept_t1;

    | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • regr_r2(Y, X)

    描述：相关系数的平方。

    返回类型：double precision

    示例：

    | ``` gaussdb=# CREATE TABLE regr_r2_t1(a int, b int); gaussdb=# INSERT INTO regr_r2_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT REGR_R2(b,a) FROM regr_r2_t1; regr_r2 ------------------ .892857142857143 (1 row) gaussdb=# DROP TABLE regr_r2_t1;

    | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • regr_slope(Y, X)

    描述：根据所有输入的点(X, Y)按照最小二乘法拟合成一个线性方程， 然后返回该直线的斜率。

    返回类型：double precision

    示例：

    | ``` gaussdb=# CREATE TABLE regr_slope_t1(a int, b int); gaussdb=# INSERT INTO regr_slope_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT REGR_SLOPE(b,a) FROM regr_slope_t1; regr_slope ------------------ 1.78571428571429 (1 row) gaussdb=# DROP TABLE regr_slope_t1;

    | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |

  • regr_sxx(Y, X)

    描述：sum(Y^2) - sum(X)^2/N （自变量的“平方和”）。

    返回类型：double precision

    示例：

    | ``` gaussdb=# CREATE TABLE regr_sxx_t1(a int, b int); gaussdb=# INSERT INTO regr_sxx_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT REGR_SXX(b,a) FROM regr_sxx_t1; regr_sxx ---------- 2.8 (1 row) gaussdb=# DROP TABLE regr_sxx_t1;

    | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • regr_sxy(Y, X)

    描述：sum(X*Y) - sum(X) * sum(Y)/N （自变量和因变量的“乘方积”）。

    返回类型：double precision

    示例：

    | ``` gaussdb=# CREATE TABLE regr_sxy_t1(a int, b int); gaussdb=# INSERT INTO regr_sxy_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT REGR_SXY(b,a) FROM regr_sxy_t1; regr_sxy ---------- 5 (1 row) gaussdb=# DROP TABLE regr_sxy_t1;

    | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • regr_syy(Y, X)

    描述：sum(Y^2) - sum(X)^2/N（因变量的"平方和"）

    返回类型：double precision

    示例：

    | ``` gaussdb=# CREATE TABLE regr_syy_t1(a int, b int); gaussdb=# INSERT INTO regr_syy_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT REGR_SYY(b,a) FROM regr_syy_t1; regr_syy ---------- 10 (1 row) gaussdb=# DROP TABLE regr_syy_t1;

    | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • stddev(expression)

    描述：stddev_samp的别名。

    返回类型：对于浮点类型的输入返回double precision，其他输入返回numeric。

    示例：

    | ``` gaussdb=# CREATE TABLE stddev_t1(a int, b int); gaussdb=# INSERT INTO stddev_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT STDDEV(a) FROM stddev_t1; stddev ----------------------- .83666002653407554798 (1 row) gaussdb=# DROP TABLE stddev_t1;

    | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • variance(expexpression,ression)

    描述：var_samp的别名。

    返回类型：对于浮点类型的输入返回double precision类型，其他输入返回numeric类型。

    示例：

    | ``` gaussdb=# CREATE TABLE variance_t1(a int, b int); gaussdb=# INSERT INTO variance_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT VARIANCE(a) FROM variance_t1; variance ----------------------- .70000000000000000000 (1 row) gaussdb=# DROP TABLE variance_t1;

    | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

  • spread()

    描述：该函数用于计算某段时间内最大和最小值差值。

    参数：real

    返回值类型：real

  • pivot_func(anyelement)

    描述：返回某列中唯一不为NULL的值，如果有超过两个非NULL值则报错。其中value为输入参数，可以为任意类型。

    返回类型：与输入参数类型相同。

    该聚合函数主要作为pivot语法内部使用。

    gaussdb=# CREATE TABLE pivot_func_t1(a int, b int);

gaussdb=# INSERT INTO pivot_func_t1 VALUES (NULL,11),(1,2);

gaussdb=# SELECT PIVOT_FUNC(a) FROM pivot_func_t1;
 pivot_func 
------------
          1
(1 row)

gaussdb=# DROP TABLE pivot_func_t1;

  • checksum(expression)

    描述：返回所有输入值的CHECKSUM值。使用该函数可以用来验证GaussDB数据库（不支持GaussDB之外的其他数据库）的备份恢复或者数据迁移操作前后表中的数据是否相同。在备份恢复或者数据迁移操作前后都需要用户通过手工执行SQL命令的方式获取执行结果，通过对比获取的执行结果判断操作前后表中的数据是否相同。

    • 对于大表，CHECKSUM函数可能会需要很长时间。
    • 如果某两表的CHECKSUM值不同，则表明两表的内容是不同的。由于CHECKSUM函数中使用散列函数不能保证无冲突，因此两个不同内容的表可能会得到相同的CHECKSUM值，存在这种情况的可能性较小。对于列进行的CHECKSUM也存在相同的情况。
    • 对于时间类型timestamp, timestamptz和smalldatetime，计算CHECKSUM值时请确保时区设置一致。
    • 若计算某列的CHECKSUM值，且该列类型可以默认转为TEXT类型，则expression为列名。
    • 若计算某列的CHECKSUM值，且该列类型不能默认转为TEXT类型，则expression为列名::TEXT。
    • 若计算所有列的CHECKSUM值，则expression为表名::TEXT。

    可以默认转换为TEXT类型的类型包括：char, name, int8, int2, int1, int4, raw, pg_node_tree, float4, float8, bpchar, varchar, nvarchar2, date, timestamp, timestamptz, numeric, smalldatetime，其他类型需要强制转换为TEXT，例如XML类型。

    返回类型：numeric。

    示例：

    表中可以默认转为TEXT类型的某列的CHECKSUM值。

    gaussdb=# CREATE TABLE checksum_t1(a int, b int);

gaussdb=# INSERT INTO checksum_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6);

gaussdb=# SELECT CHECKSUM(a) FROM checksum_t1;
  checksum   
-------------
 18126842830
(1 row)

gaussdb=# DROP TABLE checksum_t1;

    表中不能默认转为TEXT类型的某列的CHECKSUM值。注意此时CHECKSUM参数是列名::TEXT。

    | ``` gaussdb=# CREATE TABLE checksum_t1(a int, b int); gaussdb=# INSERT INTO checksum_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6); gaussdb=# SELECT CHECKSUM(a::TEXT) FROM checksum_t1; checksum ------------- 18126842830 (1 row) gaussdb=# DROP TABLE checksum_t1;

    | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

表中所有列的CHECKSUM值。注意此时CHECKSUM参数是表名::TEXT，且表名前不加Schema。

| ```
gaussdb=# CREATE TABLE checksum_t1(a int, b int);  gaussdb=# INSERT INTO checksum_t1 VALUES (NULL,11),(1,2),(1,3),(2,4),(2,5),(3,6);  gaussdb=# SELECT CHECKSUM(checksum_t1::TEXT) FROM checksum_t1;   checksum    -------------  11160522226 (1 row)  gaussdb=# DROP TABLE checksum_t1; 
``` |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
聚集函数嵌套

描述：对聚集函数分组计算的结果再进行一次聚集函数操作。

一般可描述为：

SELECT AGG1(AGG2(column_name1)) FROM table_name GROUP BY column_name2;

等价为：

SELECT AGG1(value) FROM (SELECT AGG2(column_name1) value FROM table_name GROUP BY column_name2);

其中：

  • AGG1()：表示外层聚集函数。
  • AGG2()：表示内层聚集函数。
  • table_name：表示表名。
  • column_name1、column_name2：表示列名。
  • value：表示内层聚集函数结果的别名。

整体含义可描述为：将内层聚集函数AGG2()分组计算的结果作为外层聚集函数AGG1()的输入再计算一次。

  1. 嵌套的聚集函数应位于SELECT和FROM之间，否则无意义。
  2. 使用嵌套聚集函数的SELECT语句应包含GROUP BY子句。
  3. 与嵌套聚集函数同时被SELECT的，应同为嵌套聚集函数，或为常量表达式。
  4. 聚集函数仅支持一次聚集函数嵌套操作。
  5. 当前支持以下聚集函数之间的嵌套：avg、max、min、sum、var_pop、var_samp、variance、stddev_pop、stddev_samp、stddev、median、regr_sxx、regr_syy、regr_sxy、regr_avgx、regr_avgy、regr_r2、regr_slope、regr_intercept、covar_pop、covar_samp、corr和listagg。
  6. 内层聚集函数的返回结果类型应符合外层聚集函数的参数类型。

示例：

gaussdb=# CREATE TABLE test1 (id INT,val INT);
CREATE TABLE
gaussdb=# INSERT INTO test1 VALUES (1, 1);
INSERT 0 1
gaussdb=# INSERT INTO test1 VALUES (1, null);
INSERT 0 1
gaussdb=# INSERT INTO test1 VALUES (2, 10);
INSERT 0 1
gaussdb=# INSERT INTO test1 VALUES (2, 55);
INSERT 0 1

gaussdb=# SELECT SUM(MIN(val)) FROM test1 GROUP BY id;
 sum
-----
  11
(1 row)

gaussdb=# DROP TABLE test1;
DROP TABLE

更多详情请参考GaussDB 文档中心：doc.hcs.huawei.com/db/zh-cn/ga…

avatar
文章
阅读
粉丝