Use aggregate functions, Use aggregate functions - AnalyticDB

AnalyticDB for PostgreSQL is compatible with the aggregate functions of PostgreSQL. This topic describes the aggregate functions that are supported by AnalyticDB for PostgreSQL and provides examples on how to use the aggregate functions.

For more information, see Aggregate Functions.

Aggregate functions

Overview

Aggregate functions perform calculations on a set of data and return corresponding results. You can use aggregate functions in databases to calculate the results of multiple input rows for statistics, such as the average value, maximum value, and sum. If no input rows exist, all functions except the count() and count(*) functions return a null value instead of zero. If no input rows exist, the array_agg() function returns null instead of an empty array. If necessary, you can use the coalesce() function to replace zeros or empty arrays with null.

General-purpose aggregate functions

Function	Data type of the input values	Data type of the return value	Description
`array_agg()`	Any non-array type (We recommend that you do not temporarily use types wrapped by using the row() function as the data type of the input values.)	Same as the data type of the input values	Concatenates input values, including null values, into an array.
`array_agg()`	Any array type	Same as the data type of the input values	Concatenates input arrays into an array of one higher dimension. The input values must have the same dimensionality and cannot be empty or null.
`avg()`	`SMALLINT`, `INT`, `BIGINT`, `REAL`, `DOUBLE PRECISION`, `NUMERIC`, or `INTERVAL`	`NUMERIC` for input values of an integer type, `DOUBLE PRECISION` for input values of a floating-point type, or same as the data type of the input values for input values of other types	Returns the average value of all non-null input values.
`bit_and()`	`SMALLINT`, `INT`, `BIGINT`, or `BIT`	Same as the data type of the input values	Returns the bitwise AND of all non-null input values. If no non-null input values exist, this function returns null.
`bit_or()`	`SMALLINT`, `INT`, `BIGINT`, or `BIT`	Same as the data type of the input values	Returns the bitwise OR of all non-null input values. If no non-null input values exist, this function returns null.
`bool_and()`	`BOOL`	`BOOL`	Returns true if all input values are true. Otherwise, this function returns false.
`bool_or()`	`BOOL`	`BOOL`	Returns true if at least one input value is true. Otherwise, this function returns false.
`count(*)`	-	`BIGINT`	Returns the number of input rows.
`count()`	`Any`	`BIGINT`	Returns the number of non-null input rows.
`every()`	`BOOL`	`BOOL`	Works in the same manner as the `bool_and()` function.
`json_agg()`	`Any`	`JSON`	Aggregates values, including null values, into a JSON array.
`jsonb_agg()`	`Any`	`JSONB`	Aggregates values, including null values, into a JSONB array.
`json_object_agg(name,value)`	`(any, any)`	`JSON`	Aggregates `key-value` pairs into a JSON object. The values in key-value pairs can be null but cannot be the corresponding key names.
`jsonb_object_agg(name,value)`	`(any, any)`	`JSONB`	Aggregates `key-value` pairs into a JSONB object. The values in key-value pairs can be null but cannot be the corresponding key names.
`max()`	Any array, numeric, string, date or time, network, or enumeration type, or array of these types	Same as the data type of the input values	Returns the maximum value of the non-null input values.
`min()`	Any array, numeric, string, date or time, network, or enumeration type, or array of these types	Same as the data type of the input values	Returns the minimum value of the non-null input values.
`string_agg(expression,delimiter)`	`TEXT` or `BYTEA` for both arguments	Same as the data type of the input values	Concatenates non-null input values into a string that is separated by the specified delimiter.
`sum()`	`SMALLINT`, `INT`, `BIGINT`, `REAL`, `DOUBLE PRECISION`, `NUMERIC`, `INTERVAL`, or `MONEY`	`BIGINT` for input values of the `SMALLINT` or `INT` type, `NUMERIC` for input values of the `BIGINT` type, or same as the data type of the input values for input values of other types	Returns the sum of all non-null input values.
`xmlagg()`	`XML`	`XML`	Concatenates non-null XML input values.

Statistics-related aggregate functions

Function	Data type of the input values	Data type of the return value	Description
`corr(Y,X)`	`DOUBLE PRECISION`	`DOUBLE PRECISION`	Returns the correlation coefficient between variables Y and X.
`covar_pop(Y,X)`	`DOUBLE PRECISION`	`DOUBLE PRECISION`	Returns the population covariance between variables Y and X.
`covar_samp(Y,X)`	`DOUBLE PRECISION`	`DOUBLE PRECISION`	Returns the sample covariance between variables Y and X.
`regr_avgx(Y,X)`	`DOUBLE PRECISION`	`DOUBLE PRECISION`	Returns the average value of the independent variable X by using the following formula: `sum(X)/N`. N is the number of non-null values for the X variable.
`regr_avgy(Y,X)`	`DOUBLE PRECISION`	`DOUBLE PRECISION`	Returns the average value of the dependent variable Y by using the following formula: `sum(Y)/N`. N is the number of non-null values for the Y variable.
`regr_count(Y,X)`	`DOUBLE PRECISION`	`BIGINT`	Returns the number of input rows in which the values for both variables Y and X are non-null values.
`regr_intercept(Y,X)`	`DOUBLE PRECISION`	`DOUBLE PRECISION`	Returns the y-intercept of the linear equation fitted by least squares from the data points of variables `Y` and `X`.
`regr_r2(Y,X)`	`DOUBLE PRECISION`	`DOUBLE PRECISION`	Returns the square of the correlation coefficient.
`regr_slope(Y,X)`	`DOUBLE PRECISION`	`DOUBLE PRECISION`	Returns the slope of the linear equation fitted by least squares from the data points of variables `Y` and `X`.
`regr_sxx(Y,X)`	`DOUBLE PRECISION`	`DOUBLE PRECISION`	Returns the sum of the squares of the independent variable X by using the following formula: `sum(X^2) - sum(X)^2/N`.
`regr_sxy(Y,X)`	`DOUBLE PRECISION`	`DOUBLE PRECISION`	Returns the sum of the products of the independent variable X and the dependent variable Y by using the following formula: `sum(X × Y) - sum(X) × sum(Y)/N`. N is the number of value pairs of variables Y and X.
`regr_syy(Y,X)`	`DOUBLE PRECISION`	`DOUBLE PRECISION`	Returns the sum of the squares of the dependent variable Y by using the following formula: `sum(Y^2) - sum(Y)^2/N`.
`stddev()`	`SMALLINT`, `INT`, `BIGINT`, `REAL`, `DOUBLE PRECISION`, or `NUMERIC`	`DOUBLE PRECISION` for input values of a floating-point type or `NUMERIC` for input values of other types	Works in the same manner as the `stddev_samp()` function. This function is the alias of the stddev_samp() function.
`stddev_pop()`	`SMALLINT`, `INT`, `BIGINT`, `REAL`, `DOUBLE PRECISION`, or `NUMERIC`	`DOUBLE PRECISION` for input values of a floating-point type or `NUMERIC` for input values of other types	Returns the population standard deviation of the input values.
`stddev_samp()`	`SMALLINT`, `INT`, `BIGINT`, `REAL`, `DOUBLE PRECISION`, or `NUMERIC`	`DOUBLE PRECISION` for input values of a floating-point type or `NUMERIC` for input values of other types	Returns the sample standard deviation of the input values.
`variance`()	`SMALLINT`, `INT`, `BIGINT`, `REAL`, `DOUBLE PRECISION`, or `NUMERIC`	`DOUBLE PRECISION` for input values of a floating-point type or `NUMERIC` for input values of other types	Works in the same manner as the `var_samp()` function. This function is the alias of the var_samp() function.
`var_pop`()	`SMALLINT`, `INT`, `BIGINT`, `REAL`, `DOUBLE PRECISION`, or `NUMERIC`	`DOUBLE PRECISION` for input values of a floating-point type or `NUMERIC` for input values of other types	Returns the population variance of the input values, which is the square of the population standard deviation.
`var_samp`()	`SMALLINT`, `INT`, `BIGINT`, `REAL`, `DOUBLE PRECISION`, or `NUMERIC`	`DOUBLE PRECISION` for input values of a floating-point type or `NUMERIC` for input values of other types	Returns the sample variance of the input values, which is the square of the sample standard deviation.