Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-8.x)/ SQL Reference/ Functions and Operators/ Aggregate Functions/ Aggregate Functions
Updated on 2025-05-29 GMT+08:00
View PDF
Share

Aggregate Functions

sum(expression)

Description: Sum of expression across all input values

Return type:

Generally, the output type is the same as the input type except in the following cases:

  • For a SMALLINT or INT input, the output type is BIGINT.
  • For a BIGINT input, the output type is NUMBER.
  • For a floating-point number input, the output type is DOUBLE PRECISION.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
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)

Description: Returns the maximum value of expression among all input rows.

Parameter type: any array, numeric, string, or date/time type

Return type: same as the input parameter type.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Returns the minimum value of expression among all input rows.

Parameter type: any array, numeric, string, or date/time type

Return type: same as the input parameter type.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Average (arithmetic mean) of all input values

Return type:

NUMBER for any integer-type argument.

DOUBLE PRECISION for floating-point parameters.

Otherwise, it is the same as the input data type.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Returns the number of input rows for which the value of expression is not null.

Return type: BIGINT

Operations on XML data are supported.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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(*)

Description: Returns the number of input rows.

Return type: BIGINT

Operations on XML data are supported.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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;

KEEP(DENSE_RANK { FIRST | LAST } ORDER BY expr)

Description: Sorts data rows in a query result based on specified rules and returns the first or last group of values. This can be used in aggregate or window functions.

Syntax:

aggregate_function KEEP ( DENSE_RANK { FIRST | LAST }  ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ]
      [, expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ]]...)

Return type: data type of the specified column in aggregate_function.

  • aggregate_function is an aggregate function. Currently, only MIN, MAX, SUM, AVG, COUNT, VARIANCE, and STDDEV are supported.
  • The KEEP keyword is used for clearer semantics and is limited to aggregate functions and window functions.
  • DENSE_RANK FIRST and DENSE_RANK LAST indicate that only rows after the minimum (FIRST) and maximum (LAST) dense ranking are aggregated, respectively.
  • Vectorized plans cannot be generated for execution.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
gaussdb=# CREATE TABLE keep_t1(a int, b int, c int);

gaussdb=# INSERT INTO keep_t1 values(2, 111, 333), (2, 11, 33), (2, 11, 3), (1, 22, 55), (1, 2, 5);

gaussdb=#  SELECT MIN(b) KEEP(DENSE_RANK FIRST ORDER BY a) MIN_FIRST, 
	     MIN(b) KEEP(DENSE_RANK LAST ORDER BY a) MIN_LAST,
	     MAX(b) KEEP(DENSE_RANK FIRST ORDER BY a) MAX_FIRST,
	     MAX(b) KEEP(DENSE_RANK LAST ORDER BY a) MAX_LAST FROM keep_t1;
 min_first | min_last | max_first | max_last
-----------+----------+-----------+----------
         2 |       11 |        22 |      111
(1 row)

gaussdb=# DROP TABLE keep_t1;

median(expression) [over (query partition clause)]

Description: Returns the median of an expression. NULL will be ignored by the median function during calculation. The DISTINCT keyword can be used to exclude duplicate records in an expression. The data type of the input expression can be numeric (including integer, double, and bigint) or interval. For other data types, the median cannot be calculated.

Return type: double or interval

Example:

gaussdb=# SELECT median(id) FROM (values(1), (2), (3), (4), (null)) test(id);
 median
--------
     2.5
(1 row)

default(column_name)

Description: Obtains the default output value of a table column.

Return type: text

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
 
-- Create a MySQL-compatible database.
gaussdb=# CREATE DATABASE gaussdb_m WITH DBCOMPATIBILITY 'B';

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');

-- Execute the query.
gaussdb_m=# SELECT default(id), default(name) FROM t1;
 default | default 
---------+---------
     100 | tt
(1 row)

-- Delete the database.
gaussdb_m=# \c postgres
gaussdb=# DROP DATABASE gaussdb_m;
  • The default function takes effect only when sql_compatibility is 'B'.
  • If a table column does not have a default value, the default function returns a null value.
  • If a table column is a hidden column (such as xmin or cmin), the default function returns a null value.
  • If a table column is an auto-increment column, the default function returns 0.
  • GaussDB supports default values of partitioned tables, temporary tables, and multi-table join query.
  • GaussDB supports the query of nodes whose column names contain character string values (indicating names) and A_Star nodes (indicating that asterisks (*) appear), for example, default(tt.t4.id) and default(tt.t4.*). For invalid column names and A_Star nodes, the error information reported by GaussDB is different from that reported by B-compatible databases.
  • When the default value of a column is created in GaussDB, the range of the column type is not verified. As a result, an error may be reported when the default function is used.
  • If the default value of a column is a function expression, the default function of GaussDB will return the calculated value of the default expression set for the column during table creation. In contrast, the default function of B-compatible databases will return null in this scenario.

array_agg(expression)

Description: Input values, including nulls, concatenated into an array

Return type: array of the parameter type

Operations on XML data are supported.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Input values concatenated into a string, separated by delimiter

Return type: same as the input parameter type.

When the XML data type is explicitly converted into the character type, operations are still supported.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Aggregation column data sorted according to the mode specified by WITHIN GROUP, and concatenated to a string using the specified delimiter

  • expression: Required. It specifies an aggregation column name or a column-based, valid expression. It does not support the DISTINCT keyword and the parameters of VARIADIC.
  • delimiter: Optional. It specifies a delimiter, which can be a string constant or a deterministic expression based on a group of columns. The default value is empty.
  • order-list: Required. It specifies the sorting mode in a group.

Return type: text

Example:

The aggregation column is of the text character set type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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;

The aggregation column is of the integer type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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;

The aggregation column is of the floating point type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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;

The aggregation column is of the time type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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;

The aggregation column is of the time interval type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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;

By default, the delimiter is empty.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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;

When listagg is used as a window function, the OVER clause does not support the window sorting of ORDER BY, and the listagg column is an ordered aggregation of the corresponding groups.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
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])

Description: The number of parameters is not fixed. Multiple columns can be concatenated. Aggregation columns are sorted based on the value of ORDER BY and concatenated into a character string using the specified separator. This function cannot be used as a window function.

  • DISTINCT: Optional. It deduplicates the results after each row is concatenated.
  • expression: Required. It specifies the aggregation column name or a valid column-based expression.
  • ORDER BY: Optional. It is followed by a variable number of expressions and sorting rule. The group_concat function does not support the (ORDER BY + number) format.
  • SEPARATOR: Optional. It is followed by a character or string. This separator is used to concatenate the expression results of two adjacent lines in a group. If it is not specified, the comma (,) is used by default.
  • When both DISTINCT and ORDER BY are specified, all ORDER BY expressions must be in DISTINCT expressions. Otherwise, an error is reported.
  • Parameter group_concat_max_len is used to limit the maximum length of GROUP_CONCAT. If the length exceeds the maximum, the system truncates the length. Currently, the maximum length that can be returned is 1073741823.

Return type: text

Example:

Set separator to ';'.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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;

By default, the separator is a comma (,).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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
   | 1
(4 rows)

gaussdb=# DROP TABLE group_concat_t1;

The aggregation column is of the text character set type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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
   | a1
(4 rows)

gaussdb=# DROP TABLE group_concat_t1;

The aggregation column is of the integer type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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;

The aggregation column is of the floating point type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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 | 2.22,3.33
 2 | 4.44,5.55
 3 | 6.66
   | 1.11
(4 rows)

gaussdb=# DROP TABLE group_concat_t1;

The aggregation column is of the time type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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;

The aggregation column is of the binary type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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;

The aggregation column is of the time interval type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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;

Set distinct to deduplicate data.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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;

Set ORDER BY to sort data.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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)

Description: Concatenates the expression data of a specified column or columns into a character string by commas (,) for output.

Return type: text

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
gaussdb=# CREATE TABLE wm_concat_t1(a int, b interval);
CREATE TABLE
gaussdb=# INSERT INTO wm_concat_t1 VALUES (NULL,'1 days'),(1,'2 days'),(1,'3 days'),(2,'4 days'),(2,'5 days'),(3,'6 days');
INSERT 0 6
gaussdb=# SELECT wm_concat(a) FROM wm_concat_t1;
 wm_concat 
-----------
 1,1,2,2,3
(1 row)
gaussdb=# SELECT wm_concat(b) FROM wm_concat_t1;
                wm_concat                 
------------------------------------------
 1 day,2 days,3 days,4 days,5 days,6 days
(1 row)
gaussdb=# DROP TABLE wm_concat_t1;

The return value of this function is text, and the combined result is unordered.

covar_pop(Y, X)

Description: Overall covariance

Return type: double precision

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Sample covariance

Return type: double precision

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Overall standard difference

Return type: DOUBLE PRECISION for floating-point arguments, otherwise NUMERIC

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Sample standard deviation of the input values

Return type: DOUBLE PRECISION for floating-point arguments, otherwise NUMERIC

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Specifies the population variance of the input values (square of the population standard deviation).

Return type: DOUBLE PRECISION for floating-point arguments, otherwise NUMERIC

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Specifies the sample variance of the input values (square of the sample standard deviation).

Return type: DOUBLE PRECISION for floating-point arguments, otherwise NUMERIC

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: The bitwise AND of all non-null input values, or null if none

Return type: same as the input parameter type.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: The bitwise OR of all non-null input values, or null if none

Return type: same as the input parameter type.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Its value is true if all input values are true, otherwise false.

Return type: Boolean

Example:

1
2
3
4
5
 
gaussdb=# SELECT bool_and(100 <2500);
 bool_and
----------
 t
(1 row)

bool_or(expression)

Description: Its value is true if at least one input value is true, otherwise false.

Return type: Boolean

Example:

1
2
3
4
5
 
gaussdb=# SELECT bool_or(100 <2500);
 bool_or
----------
 t
(1 row)

corr(Y, X)

Description: Specifies the correlation coefficient.

Return type: double precision

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Equivalent to bool_and

Return type: Boolean

Example:

1
2
3
4
5
 
gaussdb=# SELECT every(100 <2500);
 every
-------
 t
(1 row)

regr_avgx(Y, X)

Description: Specifies the average of the independent variable (sum(X)/Y).

Return type: double precision

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Specifies the average of the dependent variable (sum(Y)/X).

Return type: double precision

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Specifies the number of input rows in which both expressions are non-null.

Return type: bigint

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs

Return type: double precision

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Specifies the square of the correlation coefficient.

Return type: double precision

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Slope of the least-squares-fit linear equation determined by the (X, Y) pairs

Return type: double precision

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: sum(Y^2) – sum(X)^2/N (sum of squares of the independent variables)

Return type: double precision

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: sum(X*Y) – sum(X) * sum(Y)/N (sum of products of independent times dependent variable)

Return type: double precision

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: sum(Y^2) – sum(X)^2/N (sum of squares of the dependent variable)

Return type: double precision

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Alias of stddev_samp

Return type: DOUBLE PRECISION for floating-point arguments, otherwise NUMERIC

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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)

Description: Alias of var_samp

Return type: DOUBLE PRECISION for floating-point arguments, otherwise NUMERIC

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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;

delta

Description: Returns the difference between the current row and the previous row.

Parameter: numeric

Return type: numeric

checksum(expression)

Description: Returns the CHECKSUM value of all input values. This function can be used to check whether the data in the tables is the same before and after the backup, restoration, or migration of GaussDB (databases other than GaussDB are not supported). Before and after database backup, database restoration, or data migration, you need to manually run SQL commands to obtain the execution results. Compare the obtained execution results to check whether the data in the tables before and after the backup or migration is the same.

  • For large tables, the execution of CHECKSUM function may take a long time.
  • If the CHECKSUM values of two tables are different, it indicates that the contents of the two tables are different. Using the hash function in the CHECKSUM function may incur conflicts. There is low possibility that two tables with different contents may have the same CHECKSUM value. The same problem may occur when CHECKSUM is used for columns.
  • If the time type is timestamp, timestamptz, or smalldatetime, ensure that the time zone settings are the same when calculating the CHECKSUM value.
  • To calculate CHECKSUM for a column that can be converted into the TEXT type by default, set expression to the column name.
  • To calculate CHECKSUM for a column that cannot be converted into the TEXT type by default, set expression to Column name::TEXT.
  • To calculate CHECKSUM for all columns, set expression to Table name::TEXT.

The following data types can be converted into TEXT by default: char, name, int8, int2, int1, int4, raw, pg_node_tree, float4, float8, bpchar, varchar, nvarchar, nvarchar2, date, timestamp, timestamptz, numeric, and smalldatetime. Other types need to be forcibly converted into TEXT, such as XML.

Return type: numeric

Example:

The following calculates CHECKSUM for a column can be converted into the TEXT type by default:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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;

The following calculates CHECKSUM for a column cannot be converted into the TEXT type by default. In this case, the CHECKSUM parameter is set to Column name::TEXT.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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;

The following calculates CHECKSUM for all columns. In this case, the CHECKSUM parameter is set to Table name without a schema::TEXT.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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;

percentile_cont(percentile float)

Description: Sorts a given column by time series and returns a percentile value.

Return type: float

  • percentile is a decimal from 0 to 1. The value is of the floating point type and does not support the percent sign (%), for example, 95%.
  • This parameter must be used together with WITHIN GROUP (ORDER BY) to specify the column to be calculated. The column must be of the numeric type.
Example: 
gaussdb=# SELECT percentile_cont(0) WITHIN GROUP (ORDER BY value) FROM (VALUES (1),(2)) v(value);
 percentile_cont 
-----------------
               1
(1 row)

mode() WITHIN GROUP (ORDER BY value anyelement)

Description: Returns the value with the highest occurrence frequency in a column. If multiple values have the same frequency, the smallest value is returned. The sorting mode is the same as the default sorting mode of the column type. The input parameter value can be of any type.

Return type: same as the input parameter type.

Example:

gaussdb=# SELECT mode() WITHIN GROUP (ORDER BY value) FROM (values(1, 'a'), (2, 'b'), (2, 'c')) v(value, tag);
 mode
------
    2
(1 row)
gaussdb=# SELECT mode() WITHIN GROUP (ORDER BY tag) FROM (values(1, 'a'), (2, 'b'), (2, 'c')) v(value, tag);
 mode
------
 a
(1 row)

pivot_func(anyelement)

Description: Returns the only non-null value in a column. If there are two or more non-null values, an error is reported. The input parameter value can be of any type.

Return type: same as the input parameter type.

This aggregate function is mainly used inside the pivot syntax.

Example:

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;

json_agg(any)

Description: Aggregates values into a JSON array.

Return type: array-json

Example:

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

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

gaussdb=# SELECT JSON_AGG(a) FROM json_agg_t1;
       json_agg        
-----------------------
 [null, 1, 1, 2, 2, 3]
(1 row)

gaussdb=# DROP TABLE json_agg_t1;

json_object_agg(any, any)

Description: Aggregates values into a JSON object.

Return type: object-json

Example:

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

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

gaussdb=# SELECT JSON_OBJECT_AGG(a,b) FROM json_object_agg_t1;
                       json_object_agg                        
--------------------------------------------------------------
 { "11" : null, "1" : 2, "1" : 3, "2" : 4, "2" : 5, "3" : 6 }
(1 row)

gaussdb=# DROP TABLE json_object_agg_t1;
Parent topic: Aggregate Functions