Help Center/ Data Warehouse Service/ SQL Syntax Reference/ SQL Syntax Reference (9.1.1.x)/ Functions and Operators/ Aggregate Functions
Updated on 2026-09-23 GMT+08:00
View PDF
Share

Aggregate Functions

Aggregate functions are commonly used in databases. They compute a single result from a set of input values, helping users quickly obtain key data overview from a large amount of data, such as the total number, average number, maximum value, and deduplicated count. Aggregate functions are widely used in scenarios such as data reports and service analysis.

Table 1 Aggregate functions

Category

Function

Description

Statistics-based computing

sum(expression)

Sum of the expression

max(expression)

Maximum value of the expression

min(expression)

Minimum value of the expression

avg(expression)

Average value of the expression

count(expression)

Number of non-null (NOT NULL) rows in a table

count(*)

Total number of rows in a table (including rows containing NULL values)

Quantile and distribution analysis

median(expression)

Median

percentile_cont(const) within group(order by expression)

Continuous distribution percentile. It is used to calculate the value of the specified percentile through linear interpolation.

percentile_disc(const) within group(order by expression)

Discrete percentile. It returns the actual value in the dataset that is closest to the specified percentile.

Set aggregation and string concatenation

array_agg(expression)

Aggregate multiple rows of values into an array.

string_agg(expression, delimiter)

Concatenate multiple rows of strings into a single string using a specified delimiter.

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

Concatenate multiple rows of strings into a single string in a specified order.

group_concat(expression [ORDER BY {col_name | expr} [ASC | DESC]] [SEPARATOR str_val])

Concatenate multiple rows of string values in a group into a single string.

Advanced statistical analysis

var_pop(expression)

Population variance

var_samp(expression)

Sample variance

variance(expression)

Sample variance

stddev_pop(expression)

Population standard deviation

stddev_samp(expression)

Sample standard deviation

stddev(expression)

Sample standard deviation

covar_pop(Y, X)

Population covariance between Y and X

covar_samp(Y, X)

Sample covariance between Y and X

corr(Y, X)

Correlation coefficient between Y and X

Linear regression analysis

regr_avgx(Y, X)

Mean value of X

regr_avgy(Y, X)

Average value of Y

regr_count(Y, X)

Number of non-null pairs (X, Y)

regr_r2(Y, X)

Square of the correlation coefficient

regr_slope(Y, X)

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

regr_intercept(Y, X)

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

regr_sxx(Y, X)

Sum of squares of X

regr_syy(Y, X)

Sum of squares of Y

regr_sxy(Y, X)

Sum of the products of X and Y.

Logical and bitwise operation aggregation

bit_and(expression)

Bitwise AND operation on the values of all rows

bit_or(expression)

Bitwise OR operation on the values of all rows

bool_and(expression)

TRUE if all input values are true

bool_or(expression)

TRUE if any input value is true

every(expression)

Equivalent to bool_and(). Returns TRUE if all input values are true.

Deduplication and verification

checksum(expression)

Checksum of data. It compares the results to determine whether the data in the tables before and after the operation is consistent.

approx_count_distinct(col_name)

Approximate number of rows that contain a distinct value

UNIQ(col_name)

Number of rows that contain a distinct value

sum(expression)

Description: Specifies the sum of expression 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

Examples:

1
2
3
4
5
 
SELECT SUM(ss_ext_tax) FROM tpcds.STORE_SALES;
  sum      
--------------
 213267594.69
(1 row)

max(expression)

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

Argument types: any array, numeric, string, or date/time type

Return type: same as the argument type

Examples:

1
2
3
4
5
 
SELECT MAX(inv_quantity_on_hand) FROM tpcds.inventory;
   max   
---------
 1000000
(1 row)

min(expression)

Description: Returns the minimum value of expression across all input values.

Argument types: any array, numeric, string, or date/time type

Return type: same as the argument type

Examples:

1
2
3
4
5
 
SELECT MIN(inv_quantity_on_hand) FROM tpcds.inventory;
 min 
-----
   0
(1 row)

avg(expression)

Description: Calculates the arithmetic average of all input values.

Return type:

  • NUMBER for any integer-type argument.
  • DOUBLE PRECISION for floating-point parameters.
  • Other values are the same as the input data type.

Examples:

1
2
3
4
5
 
SELECT AVG(inv_quantity_on_hand) FROM tpcds.inventory;
         avg          
----------------------
 500.0387129084044604
(1 row)

median(expression)

Description: Calculates the median of all input values. Currently, only the numeric and interval types are supported. Null values are not considered in the calculation.

Return type:

  • For any integer input, the result is of the NUMERIC type. otherwise, a median of the same type as the input values is returned.
  • In the Teradata-compatible mode, if the input values are integers, the returned median is rounded to the nearest integer.

Examples:

1
2
3
4
5
 
SELECT MEDIAN(inv_quantity_on_hand) FROM tpcds.inventory;
 median 
--------
    500
(1 row)

percentile_cont(const) within group(order by expression)

Description: Returns a value corresponding to the specified percentile in the ordering, interpolating between adjacent input items if needed. Null values are not involved in the calculation.

Parameters:

  • const: percentile. The value ranges from 0 to 1.
  • expression: column or expression used for sorting. Currently, only the numeric and interval types are supported.

Return type:

  • For any integer input, the result is of the NUMERIC type. otherwise, a median of the same type as the input values is returned.
  • In the Teradata-compatible mode, if the input values are integers, the returned median is rounded to the nearest integer.

Examples:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT percentile_cont(0.3) within group(order by x) FROM (SELECT generate_series(1,5) AS x) AS t;
percentile_cont
-----------------
2.2
(1 row)
SELECT percentile_cont(0.3) within group(order by x desc) FROM (SELECT generate_series(1,5) AS x) AS t;
percentile_cont
-----------------
3.8
(1 row)

percentile_disc(const) within group(order by expression)

Description: returns the first input value whose position in the ordering equals or exceeds the specified percentile.

Parameters:

  • const: percentile. The value ranges from 0 to 1.
  • expression: column or expression used for sorting. Currently, only the numeric and interval types are supported. Null values are not considered in the calculation.

Return type: If all input values are integers, a median of the NUMERIC type is returned; otherwise, a median of the same type as the input values is returned.

Examples:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT percentile_disc(0.3) within group(order by x) FROM (SELECT generate_series(1,5) AS x) AS t;
percentile_disc
-----------------
2
(1 row)
SELECT percentile_disc(0.3) within group(order by x desc) FROM (SELECT generate_series(1,5) AS x) AS t;
percentile_disc
-----------------
4
(1 row)

count(expression)

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

Return type: bigint

Examples:

1
2
3
4
5
 
SELECT COUNT(inv_quantity_on_hand) FROM tpcds.inventory;
  count   
----------
 11158087
(1 row)

count(*)

Description: Returns the total number of rows in a table (including rows containing NULL values).

Return type: bigint

Examples:

1
2
3
4
5
 
SELECT COUNT(*) FROM tpcds.inventory;
  count   
----------
 11745000
(1 row)

array_agg(expression)

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

For clusters of version 9.1.1.300 or later, the input parameters of this function can be of the array type, and a maximum of six-dimensional arrays are supported. When the input parameters are arrays, the dimensions of the arrays must be the same. After aggregation, the result will be increased by one dimension.

Return type: array of the argument type

Examples:

Create the employeeinfo table and insert data into the table:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
CREATE TABLE employeeinfo (empno smallint, ename varchar(20), job varchar(20), projects varchar(20)[], hiredate date,deptno smallint);
INSERT INTO employeeinfo VALUES (7155, 'JACK', 'SALESMAN', '{ "ERP", "CRM" }', '2018-12-01', 30);
INSERT INTO employeeinfo VALUES (7003, 'TOM', 'FINANCE', '{ "Tax_Refactor","Audit" }', '2016-06-15', 20);
INSERT INTO employeeinfo VALUES (7357, 'MAX', 'SALESMAN', '{ "HR_System", "ERP" }', '2020-10-01', 30);

SELECT * FROM employeeinfo;
 empno | ename |   job    |       projects       |      hiredate       | deptno
-------+-------+----------+----------------------+---------------------+--------
  7155 | JACK  | SALESMAN | {ERP,CRM}            | 2018-12-01 00:00:00 |     30
  7003 | TOM   | FINANCE  | {Tax_Refactor,Audit} | 2016-06-15 00:00:00 |     20
  7357 | MAX   | SALESMAN | {HR_System,ERP}      | 2020-10-01 00:00:00 |     30
(3 rows)

Query the names of all employees in department 30.

1
2
3
4
5
 
SELECT array_agg(ename) FROM employeeinfo where deptno = 30;
 array_agg
------------
 {JACK,MAX}
(1 row)

Query all employees belonging to the same department.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
SELECT deptno, array_agg(ename) FROM employeeinfo group by deptno;
 deptno | array_agg
--------+------------
     30 | {JACK,MAX}
     20 | {TOM}
(2 rows)

SELECT distinct array_agg(ename) OVER (PARTITION BY deptno) FROM employeeinfo;
 array_agg
------------
 {TOM}
 {JACK,MAX}
(2 rows)

Query all department IDs and deduplicate them.

1
2
3
4
5
6
 
SELECT array_agg(distinct deptno) FROM employeeinfo group by deptno;
 array_agg
-----------
 {20}
 {30}
(2 rows)

Query all department IDs, sorted in descending order after deduplication.

1
2
3
4
5
 
SELECT array_agg(distinct deptno order by deptno desc) FROM employeeinfo;
 array_agg
-----------
 {30,20}
(1 row)

Query the names of all projects in department 30.

1
2
3
4
5
 
SELECT array_agg(projects) FROM employeeinfo where deptno = 30;
          array_agg
-----------------------------
 {{ERP,CRM},{HR_System,ERP}}
(1 row)

Query projects belonging to the same department.

1
2
3
4
5
6
 
SELECT deptno, array_agg(projects) FROM employeeinfo group by deptno;
 deptno |          array_agg
--------+-----------------------------
     20 | {{Tax_Refactor,Audit}}
     30 | {{ERP,CRM},{HR_System,ERP}}
(2 rows)

Query the list of all projects, sorted by department in descending order.

1
2
3
4
5
 
SELECT array_agg(projects order by deptno desc) FROM employeeinfo;
                    array_agg
--------------------------------------------------
 {{ERP,CRM},{HR_System,ERP},{Tax_Refactor,Audit}}
(1 row)

string_agg(expression, delimiter)

Description: Concatenates input values into a single string using a specified delimiter.

Return type: same as the argument type

Examples:

Query all employees in the same department based on the created table employeeinfo:

1
2
3
4
5
6
 
SELECT deptno, string_agg(ename,',') FROM employeeinfo group by deptno;
 deptno | string_agg
--------+------------
     30 | JACK,MAX
     20 | TOM
(2 rows)

Query employees whose work IDs are smaller than 7156:

1
2
3
4
5
 
SELECT string_agg(ename,',') FROM employeeinfo where empno < 7156;
 string_agg
------------
 TOM,JACK
(1 row)

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

Description: Orders the aggregated column data according to the sorting method specified by WITHIN GROUP and concatenates it into a string using the specified delimiter.

Parameters:

  • 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

listagg is a column-to-row aggregation function, compatible with Oracle Database 11g Release 2. You can specify the OVER clause as a window function. When listagg is used as a window function, the OVER clause does not support the window sorting or framework of ORDER BY, so as to avoid ambiguity in listagg and ORDER BY of the WITHIN GROUP clause.

Examples:

The aggregation column is of the text character set type:

1
2
3
4
5
6
7
 
SELECT deptno, listagg(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees FROM emp GROUP BY deptno;
 deptno |              employees               
--------+--------------------------------------
     10 | CLARK,KING,MILLER
     20 | ADAMS,FORD,JONES,SCOTT,SMITH
     30 | ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
(3 rows)

The aggregation column is of the integer type:

1
2
3
4
5
6
7
 
SELECT deptno, listagg(mgrno, ',') WITHIN GROUP(ORDER BY mgrno NULLS FIRST) AS mgrnos FROM emp GROUP BY deptno;
 deptno |            mgrnos             
--------+-------------------------------
     10 | 7782,7839
     20 | 7566,7566,7788,7839,7902
     30 | 7698,7698,7698,7698,7698,7839
(3 rows)

The aggregation column is of the floating point type:

1
2
3
4
5
6
7
8
9
 
SELECT job, listagg(bonus, '($); ') WITHIN GROUP(ORDER BY bonus DESC) || '($)' AS bonus FROM emp GROUP BY job;
    job     |                      bonus                      
------------+-------------------------------------------------
 CLERK      | 10234.21($); 2000.80($); 1100.00($); 1000.22($)
 PRESIDENT  | 23011.88($)
 ANALYST    | 2002.12($); 1001.01($)
 MANAGER    | 10000.01($); 2399.50($); 999.10($)
 SALESMAN   | 1000.01($); 899.00($); 99.99($); 9.00($)
(5 rows)

The aggregation column is of the time type:

1
2
3
4
5
6
7
 
SELECT deptno, listagg(hiredate, ', ') WITHIN GROUP(ORDER BY hiredate DESC) AS hiredates FROM emp GROUP BY deptno;
 deptno |                                                          hiredates                                                           
--------+------------------------------------------------------------------------------------------------------------------------------
     10 | 1982-01-23 00:00:00, 1981-11-17 00:00:00, 1981-06-09 00:00:00
     20 | 2001-04-02 00:00:00, 1999-12-17 00:00:00, 1987-05-23 00:00:00, 1987-04-19 00:00:00, 1981-12-03 00:00:00
     30 | 2015-02-20 00:00:00, 2010-02-22 00:00:00, 1997-09-28 00:00:00, 1981-12-03 00:00:00, 1981-09-08 00:00:00, 1981-05-01 00:00:00
(3 rows)

The aggregation column is of the time interval type.

1
2
3
4
5
6
7
 
SELECT deptno, listagg(vacationTime, '; ') WITHIN GROUP(ORDER BY vacationTime DESC) AS vacationTime FROM emp GROUP BY deptno;
 deptno |                                    vacationtime                                    
--------+------------------------------------------------------------------------------------
     10 | 1 year 30 days; 40 days; 10 days
     20 | 70 days; 36 days; 9 days; 5 days
     30 | 1 year 1 mon; 2 mons 10 days; 30 days; 12 days 12:00:00; 4 days 06:00:00; 24:00:00
(3 rows)

By default, the delimiter is empty:

1
2
3
4
5
6
7
 
SELECT deptno, listagg(job) WITHIN GROUP(ORDER BY job) AS jobs FROM emp GROUP BY deptno;
 deptno |                     jobs                     
--------+----------------------------------------------
     10 | CLERKMANAGERPRESIDENT
     20 | ANALYSTANALYSTCLERKCLERKMANAGER
     30 | CLERKMANAGERSALESMANSALESMANSALESMANSALESMAN
(3 rows)

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
17
18
 
SELECT deptno, mgrno, bonus, listagg(ename,'; ') WITHIN GROUP(ORDER BY hiredate) OVER(PARTITION BY deptno) AS employees FROM emp;
 deptno | mgrno |  bonus   |                 employees                 
--------+-------+----------+-------------------------------------------
     10 |  7839 | 10000.01 | CLARK; KING; MILLER
     10 |       | 23011.88 | CLARK; KING; MILLER
     10 |  7782 | 10234.21 | CLARK; KING; MILLER
     20 |  7566 |  2002.12 | FORD; SCOTT; ADAMS; SMITH; JONES
     20 |  7566 |  1001.01 | FORD; SCOTT; ADAMS; SMITH; JONES
     20 |  7788 |  1100.00 | FORD; SCOTT; ADAMS; SMITH; JONES
     20 |  7902 |  2000.80 | FORD; SCOTT; ADAMS; SMITH; JONES
     20 |  7839 |   999.10 | FORD; SCOTT; ADAMS; SMITH; JONES
     30 |  7839 |  2399.50 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN
     30 |  7698 |     9.00 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN
     30 |  7698 |  1000.22 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN
     30 |  7698 |    99.99 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN
     30 |  7698 |  1000.01 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN
     30 |  7698 |   899.00 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN
(14 rows)

group_concat(expression [ORDER BY {col_name | expr} [ASC | DESC]] [SEPARATOR str_val])

Description: concatenates the specified str_val delimiters used by column data into a string. The concatenation uses a sorting method that must be specified by the ORDER BY clause. ORDER BY 1 is not allowed.

Parameters:

  • expression: (mandatory) specifies a column name or a column-based valid expression. It does not support the DISTINCT keyword or the VARIADIC parameter.
  • str_val: (optional) specifies a delimiter, which can be a string constant or a deterministic expression based on grouped columns. The default value indicates that commas (,) are used as delimiters.

Return type: text

Examples:

The default delimiter is a comma (,).

1
2
3
4
5
 
SELECT group_concat(sname) FROM group_concat_test;
               group_concat
------------------------------------------
 ADAMS,FORD,JONES,KING,MILLER,SCOTT,SMITH
(1 row)

Delimiters can be customized for the group_concat function.

1
2
3
4
5
 
SELECT group_concat(sname separator ';') from group_concat_test;
               group_concat
------------------------------------------
 ADAMS;FORD;JONES;KING;MILLER;SCOTT;SMITH
(1 row)

The group_concat function supports the ORDER BY clause, which concatenates column data in sequence.

1
2
3
4
5
 
SELECT group_concat(sname order by snumber separator ';') FROM group_concat_test;
               group_concat
------------------------------------------
 MILLER;FORD;SCOTT;SMITH;KING;JONES;ADAMS
(1 row)

covar_pop(Y, X)

Description: Calculates the population covariance.

Return type: double precision

Examples:

1
2
3
4
5
 
SELECT COVAR_POP(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    covar_pop     
------------------
 829.749627587403
(1 row)

covar_samp(Y, X)

Description: Calculates the sample covariance.

Return type: double precision

Examples:

1
2
3
4
5
 
SELECT COVAR_SAMP(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    covar_samp    
------------------
 830.052235037289
(1 row)

stddev_pop(expression)

Description: Calculates the population standard deviation (square root of the population variance).

Return type: double precision for floating-point arguments, otherwise numeric

Examples:

1
2
3
4
5
 
SELECT STDDEV_POP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
    stddev_pop    
------------------
 289.224294957556
(1 row)

stddev_samp(expression)

Description: Calculates the sample standard deviation (square root of the sample variance).

Return type: double precision for floating-point arguments, otherwise numeric

Examples:

1
2
3
4
5
 
SELECT STDDEV_SAMP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
   stddev_samp    
------------------
 289.224359757315
(1 row)

var_pop(expression)

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

Return type: double precision for floating-point arguments, otherwise numeric

Examples:

1
2
3
4
5
 
SELECT VAR_POP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
      var_pop       
--------------------
 83650.692793695475
(1 row)

var_samp(expression)

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

Return type: double precision for floating-point arguments, otherwise numeric

Examples:

1
2
3
4
5
 
SELECT VAR_SAMP(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
      var_samp      
--------------------
 83650.730277028768
(1 row)

bit_and(expression)

Description: Performs a bitwise AND operation on all non-null input values. If all input values are NULL, the result is also NULL.

Return type: same as the argument type

Examples:

1
2
3
4
5
 
SELECT BIT_AND(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
 bit_and 
---------
       0
(1 row)

bit_or(expression)

Description: Performs a bitwise OR operation on all non-null input values. If all input values are NULL, the result is also NULL.

Return type: same as the argument type

Examples:

1
2
3
4
5
 
SELECT BIT_OR(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
 bit_or 
--------
   1023
(1 row)

bool_and(expression)

Description: Returns TRUE if all input values are true. Otherwise, FALSE is returned.

Return type: Boolean

Examples:

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

bool_or(expression)

Description: Returns TRUE if any input value is true. Otherwise, FALSE is returned.

Return type: Boolean

Examples:

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

corr(Y, X)

Description: Calculates the correlation coefficient (covariance divided by the product of the standard deviations of two variables).

Return type: double precision

Examples:

1
2
3
4
5
 
SELECT CORR(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
       corr        
-------------------
 0.0381383624904186
(1 row)

every(expression)

Description: Is equivalent to bool_and(expression) Returns TRUE if all input values are true. Otherwise, returns FALSE.

Return type: Boolean

Examples:

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

regr_avgx(Y, X)

Description: Calculates the average of the independent variable (sum(X)/N).

Return type: double precision

Examples:

1
2
3
4
5
 
SELECT REGR_AVGX(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    regr_avgx     
------------------
 578.606576740795
(1 row)

regr_avgy(Y, X)

Description: Calculates the average of the dependent variable (sum(Y)/N).

Return type: double precision

Examples:

1
2
3
4
5
 
SELECT REGR_AVGY(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    regr_avgy     
------------------
 50.0136711629602
(1 row)

regr_count(Y, X)

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

Return type: bigint

Examples:

1
2
3
4
5
 
SELECT REGR_COUNT(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
 regr_count 
------------
       2743
(1 row)

regr_intercept(Y, X)

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

Return type: double precision

Examples:

1
2
3
4
5
 
SELECT REGR_INTERCEPT(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
  regr_intercept  
------------------
 49.2040847848607
(1 row)

regr_r2(Y, X)

Description: Calculates the square of the correlation coefficient.

Return type: double precision

Examples:

1
2
3
4
5
 
SELECT REGR_R2(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
      regr_r2       
--------------------
 0.00145453469345058
(1 row)

regr_slope(Y, X)

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

Return type: double precision

Examples:

1
2
3
4
5
 
SELECT REGR_SLOPE(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
     regr_slope     
--------------------
 0.00139920009665259
(1 row)

regr_sxx(Y, X)

Description: Calculates sum(X^2) – sum(X)^2/N (sum of the squares of the independent variable X).

Return type: double precision

Examples:

1
2
3
4
5
 
SELECT REGR_SXX(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
     regr_sxx     
------------------
 1626645991.46135
(1 row)

regr_sxy(Y, X)

Description: Calculates sum(X*Y) – sum(X) * sum(Y)/N (sum of the products of independent variable X and dependent variable Y).

Return type: double precision

Examples:

1
2
3
4
5
 
SELECT REGR_SXY(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
     regr_sxy     
------------------
 2276003.22847225
(1 row)

regr_syy(Y, X)

Description: Calculates sum(Y^2) - sum(Y)^2/N (sum of squares of the dependent variable)

Return type: double precision

Examples:

1
2
3
4
5
 
SELECT REGR_SYY(sr_fee, sr_net_loss) FROM tpcds.store_returns WHERE sr_customer_sk < 1000;
    regr_syy     
-----------------
 2189417.6547314
(1 row)

stddev(expression)

Description: Specifies the alias of stddev_samp(expression) and calculates the sample standard deviation.

Return type: double precision for floating-point arguments, otherwise numeric

Examples:

1
2
3
4
5
 
SELECT STDDEV(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
      stddev      
------------------
 289.224359757315
(1 row)

variance(expression)

Description: Specifies the alias of var_samp(expression) and calculates the sample variance.

Return type: double precision for floating-point arguments, otherwise numeric

Examples:

1
2
3
4
5
 
SELECT VARIANCE(inv_quantity_on_hand) FROM tpcds.inventory WHERE inv_warehouse_sk = 1;
      variance      
--------------------
 83650.730277028768
(1 row)

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 DWS database. Before and after the backup and restoration or data migration, you need to manually execute a SQL command to obtain the execution result. By comparing the obtained execution results, it can be determined whether the data in the table is consistent before and after the operation.

  • For a table with a large amount of data, it may take a long time to execute the CHECKSUM() function.
  • 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 changed 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 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.

Return type: numeric

Examples:

The following shows the CHECKSUM value of a column that can be converted to the TEXT type by default:

1
2
3
4
5
 
SELECT CHECKSUM(inv_quantity_on_hand) FROM tpcds.inventory;
     checksum      
-------------------
 24417258945265247
(1 row)

CHECKSUM value of a column that cannot be converted to the TEXT type by default (Note that the CHECKSUM parameter is column_name::TEXT):

1
2
3
4
5
 
SELECT CHECKSUM(inv_quantity_on_hand::TEXT) FROM tpcds.inventory;
     checksum      
-------------------
 24417258945265247
(1 row)

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
 
SELECT CHECKSUM(inventory::TEXT) FROM tpcds.inventory;                    
     checksum      
-------------------
 25223696246875800
(1 row)

approx_count_distinct(col_name)

Description: Estimates the number of rows in a column after deduplication (cardinality) using the HyperLogLog++ (HLL++) algorithm. This function is supported only in clusters of version 8.3.0 or later.

Input parameter description: col_name indicates the name of the column whose cardinality needs to be estimated.

You can adjust the error rate by setting the approx_count_distinct_precision parameter.

  • The value range is [10,20]. The default value is 17. The theoretical error rate is 3‰.

  • This parameter indicates the number of buckets in the HyperLogLog++ (HLL++) algorithm. A larger value indicates a larger number of buckets and a smaller theoretical error rate.
  • A larger value of this parameter results in more computing time and memory resource overhead, but is still far less than the overhead of the count distinct statement. You are advised to use this function to replace the COUNT DISTINCT statement when the estimation cardinality is large.

Examples:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE employeeinfo (empno smallint, ename varchar(20), job varchar(20), hiredate date,deptno smallint) WITH (ORIENTATION = COLUMN);
INSERT INTO employeeinfo VALUES (7155, 'JACK', 'SALESMAN', '2018-12-01', 30);
INSERT INTO employeeinfo VALUES (7003, 'TOM', 'FINANCE', '2016-06-15', 20);
INSERT INTO employeeinfo VALUES (7357, 'MAX', 'SALESMAN', '2020-10-01', 30);

SELECT APPROX_COUNT_DISTINCT(empno) from employeeinfo;
 approx_count_distinct
-----------------------
                     3
(1 row)

SELECT COUNT(DISTINCT empno) FROM employeeinfo GROUP BY ename;
 count
-------
     1
     1
     1
(3 rows)

UNIQ(col_name)

Description: Calculates the number of rows after deduplication in a column and returns a deduplicated value. Its function is similar to that of COUNT DISTINCT. This function is supported only in clusters of version 8.3.0 or later.

Input parameter description: col_name indicates the column whose number of rows after deduplication needs to be calculated. The value can be SMALLINT, INTEGER, BIGINT, REAL, DOUBLE PRECISION, TEXT, VARCHAR, TIMESTAMP, TIMESTAMPTZ, DATE, TIMETZ or UUID.

  • Currently, only DWS column-store tables support the UNIQ() function.
  • When the UNIQ() function is used, the SQL statement must contain GROUP BY. To achieve better performance, the GROUP BY fields must be evenly distributed.
  • To deduplicate three or more columns in an SQL statement, you can use the UNIQ() function, which is more efficient than the COUNT DISTINCT function.
  • Generally, the memory usage of the UNIQ() function is lower than that of the COUNT DISTINCT function. If the memory usage exceeds the threshold when the COUNT DISTINCT function is used, you can use the UNIQ() function.

Examples:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
CREATE TABLE employeeinfo (empno smallint, ename varchar(20), job varchar(20), hiredate date,deptno smallint) WITH (ORIENTATION = COLUMN);
INSERT INTO employeeinfo VALUES (7155, 'JACK', 'SALESMAN', '2018-12-01', 30);
INSERT INTO employeeinfo VALUES (7003, 'TOM', 'FINANCE', '2016-06-15', 20);
INSERT INTO employeeinfo VALUES (7357, 'MAX', 'SALESMAN', '2020-10-01', 30);

SELECT UNIQ(deptno) FROM employeeinfo GROUP BY ename;
 uniq
------
    1
    1
    1
(3 rows)
Parent Topic: Functions and Operators