Help Center/ GaussDB/ Developer Guide(Centralized_3.x)/ SQL Reference/ Functions and Operators/ Aggregate Functions
Updated on 2024-05-07 GMT+08:00
View PDF
Share

Aggregate Functions

Aggregate Functions

  • sum(expression)

    Description: Specifies the sum of expressions across all input values.

    Return type:

    Generally, same as the argument data type. In the following cases, type conversion occurs:

    • BIGINT for SMALLINT or INT arguments
    • NUMBER for BIGINT arguments
    • DOUBLE PRECISION for floating-point arguments

    Example:

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

    Description: Specifies the maximum value of expression across all input values.

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

    Return type: same as the argument 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: Specifies the minimum value of expression across all input values.

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

    Return type: same as the argument 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: Specifies the 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 argument 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: Specifies the number of input rows for which the value of the 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;
  • 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:

    SELECT median(id) FROM (values(1), (2), (3), (4), (null)) test(id);
 median
--------
     2.5
(1 row)
  • array_agg(expression)

    Description: Concatenates input values, including nulls, into an array.

    Return type: array of the argument 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: Concatenates input values into a string, separated by delimiter.

    Return type: same as the argument type

    Operations on XML data that is explicitly converted to the character type are 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: Sorts aggregation column data according to the mode specified by WITHIN GROUP and concatenates the data to a string using the specified delimiter.

    • expression: Mandatory. It specifies an aggregation column name or a column-based valid expression. It does not support the DISTINCT keyword and the VARIADIC parameter.
    • 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: Mandatory. 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: Mandatory. 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 this parameter is not specified, ',' 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.

    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 column data into a string separated by commas (,).

    Return type: same as the argument type

    wm_concat is developed for compatibility with database A. Currently, this function has been canceled and replaced by the listagg function in the latest version of database A. You can also use the string_agg function. For details, see the description of the two functions.

  • covar_pop(Y, X)

    Description: Specifies the 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: Specifies the 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: Specifies the overall 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 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: Specifies the 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: bitwise AND of all non-null input values, or null if none

    Return type: same as the argument 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: bitwise OR of all non-null input values, or null if none

    Return type: same as the argument 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)/N).

    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)/N).

    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: Specifies the 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: Specifies the 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(X^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(Y)^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: Specifies the 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: Specifies the 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 the GaussDB database (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 the 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.
    • If the CHECKSUM value of a column is calculated and the column type can be changed to TEXT by default, set expression to the column name.
    • If the CHECKSUM value of a column is calculated and the column type cannot be converted to TEXT by default, set expression to Column name::TEXT.
    • If the CHECKSUM value of all columns is calculated, set expression to Table name::TEXT.

    The following types of data can be converted into the TEXT type 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 to TEXT.

    Return type: numeric

    Example:

    The following shows the CHECKSUM value of a column that can be converted to 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 shows the CHECKSUM value of a column that cannot be converted to the TEXT type by default. Note that 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 shows the CHECKSUM value of all columns in a table. Note that the CHECKSUM parameter is set to Table name::TEXT. The table name is not modified by its schema.

     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. value is an input parameter and 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. value is an input parameter and 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;
Parent topic: Functions and Operators