Compatible with PostgreSQL, Hologres allows you to use standard PostgreSQL syntax for data development.
The following table describes the general-purpose aggregate functions supported by Hologres. The functions supported by Hologres are only a subset of the PostgreSQL functions. For more information about how to use these functions, see
Aggregate Functions in the PostgreSQL documentation.
| Function | Description | Example | Result |
|---|---|---|---|
| array_agg(bigint) | Aggregates the values of a BIGINT expression to an array. | array_agg(c1) | {1,2} |
| array_agg(bool) | Aggregates the values of a BOOLEAN expression to an array. | array_agg(c1) | {true,false} |
| array_agg(text) | Aggregates the values of a TEXT expression to an array. | array_agg(c1) | {a,b} |
| array_agg(float8) | Aggregates the values of a FLOAT8 expression to an array. | array_agg(c1) | {1.1,2.2} |
| array_agg(float4) | Aggregates the values of a FLOAT4 expression to an array. | array_agg(c1) | {1.1,2.2} |
| array_agg(int) | Aggregates the values of an INT expression to an array. | array_agg(c1) | {1, 2} |
| avg(bigint) | Calculates the average of the non-NULL values of a BIGINT expression. | avg(c1) | 2.000000 |
| avg(float8) | Calculates the average of the non-NULL values of a FLOAT8 expression. | avg(c1) | 2.000000 |
| avg(float4) | Calculates the average of the non-NULL values of a FLOAT4 expression. | avg(c1) | 2.000000 |
| avg(int) | Calculates the average of the non-NULL values of an INT expression. | avg(c1) | 2.000000 |
| bit_and(bigint) | Performs bitwise AND operations on the non-NULL values of a BIGINT expression. | bit_and(c1) | 0 |
| bit_and(int) | Performs bitwise AND operations on the non-NULL values of an INT expression. | bit_and(c1) | 0 |
| bit_or(bigint) | Performs bitwise OR operations on the non-NULL values of a BIGINT expression. | bit_or(c1) | 3 |
| bit_or(int) | Performs bitwise OR operations on the non-NULL values of an INT expression. | bit_or(c1) | 3 |
| bool_and(bool) | Performs AND operations on a BOOLEAN expression. This function returns true if all the values returned by the expression are true and returns false if one or more values are false. | bool_and(c1) | f |
| bool_or(bool) | Performs OR operations on a BOOLEAN expression. This function returns true if one or more values returned by the expression are true and returns false if no value is false. | bool_or(c1) | t |
| count(*) | Returns the number of rows in a table. | count(*) | 3 |
| count(bigint) | Returns the number of rows defined by a BIGINT expression.
Note This function ignores NULL values.
|
count(c1) | 3 |
| count(numeric) | Returns the number of rows defined by a NUMERIC expression.
Note This function ignores NULL values.
|
count(c1) | 3 |
| every(bool) | Performs AND operations on a BOOLEAN expression. This function returns true if all the values returned by the expression are true and returns false if one or more values are false. | N/A | N/A |
| max(bigint) | Returns the maximum value returned by a BIGINT expression. | max(c1) | 3 |
| max(float8) | Returns the maximum value returned by a FLOAT8 expression. | max(c1) | 3.0 |
| max(float4) | Returns the maximum value returned by a FLOAT4 expression. | max(c1) | 3.0 |
| max(int) | Returns the maximum value returned by an INT expression. | max(c1) | 3 |
| max(numeric) | Returns the maximum value returned by a NUMERIC expression. | max(c1) | 3.0 |
| min(bigint) | Returns the minimum value returned by a BIGINT expression. | min(c1) | 1 |
| min(float8) | Returns the minimum value returned by a FLOAT8 expression. | min(c1) | 1.0 |
| min(float4) | Returns the minimum value returned by a FLOAT4 expression. | min(c1) | 1.0 |
| min(int) | Returns the minimum value returned by an INT expression. | min(c1) | 1 |
| min(numeric) | Returns the minimum value returned by a NUMERIC expression. | min(c1) | 1.0 |
| sum(bigint) | Returns the sum of the values returned by a BIGINT expression. | sum(c1) | 6 |
| sum(float8) | Returns the sum of the values returned by a FLOAT8 expression. | sum(c1) | 6.0 |
| sum(float4) | Returns the sum of the values returned by a FLOAT4 expression. | sum(c1) | 6.0 |
| sum(int) | Returns the sum of the values returned by an INT expression. | sum(c1) | 6 |
| sum(numeric) | Returns the sum of the values returned by a NUMERIC expression. | sum(c1) | 6.0 |
| string_agg(expression, delimiter) | Concatenates the non-NULL values of an expression into a string by using a delimiter. | string_agg(c1, '-') | a-b-c |
| corr(Y, X) | Returns the correlation coefficient of input values. | corr(c1, c2) | N/A |
| covar_pop(Y, X) | Returns the population covariance of input values. | covar_pop(c1, c2) | N/A |
| covar_samp(Y, X) | Returns the sample covariance of input values. | covar_samp(c1, c2) | N/A |
| regr_avgx(Y, X) | Calculates the average of the independent variable. | regr_avgx(c1, c2) | N/A |
| regr_avgy(Y, X) | Calculates the average of the dependent variable. | regr_avgy(c1, c2) | N/A |
| regr_count(Y, X) | Returns the number of non-NULL data pairs of the dependent and independent variables. | regr_count(c1, c2) | N/A |
| regr_intercept(Y, X) | Returns the intercept on the y-axis of the linear regression line that best fits the dependent and independent variables. | regr_intercept(c1, c2) | N/A |
| regr_r2(Y, X) | Returns the square of the correlation coefficient. | regr_r2(c1, c2) | N/A |
| regr_slope(Y, X) | Returns the slope of the linear regression line that best fits the dependent and independent variables. | regr_slope(c1, c2) | N/A |
| regr_sxx(Y, X) | Returns the sum of the squares of the independent variable by using this formula: sum(X^2) - sum(X)^2/N. |
regr_sxx(c1, c2) | N/A |
| regr_sxy(Y, X) | Returns the sum of the products of the dependent and independent variables by using this formula: sum(X × Y) - sum(X) × sum(Y)/N. |
regr_sxy(c1, c2) | N/A |
| regr_syy(Y, X) | Returns the sum of the squares of the dependent variable by using this formula: sum(Y^2) - sum(Y)^2/N. |
regr_syy(c1, c2) | N/A |
| stddev(int) | Calculates the sample standard deviation of the values returned by an INT expression. | stddev(c1) | N/A |
| stddev(numeric) | Calculates the sample standard deviation of the values returned by a NUMERIC expression. | stddev(c1) | N/A |
| stddev(float8) | Calculates the sample standard deviation of the values returned by a FLOAT8 expression. | stddev(c1) | N/A |
| stddev_pop(int) | Calculates the population standard deviation of the values returned by an INT expression. | stddev_pop(c1) | N/A |
| stddev_pop(numeric) | Calculates the population standard deviation of the values returned by a NUMERIC expression. | stddev_pop(c1) | N/A |
| stddev_pop(float8) | Calculates the population standard deviation of the values returned by a FLOAT8 expression. | stddev_pop(c1) | N/A |
| stddev_samp(int) | Calculates the sample standard deviation of the values returned by an INT expression. | stddev_samp(c1) | N/A |
| stddev_samp(numeric) | Calculates the sample standard deviation of the values returned by a NUMERIC expression. | stddev_samp(c1) | N/A |
| stddev_samp(float8) | Calculates the sample standard deviation of the values returned by a FLOAT8 expression. | stddev_samp(c1) | N/A |
| variance(int) | Calculates the sample variance of the values returned by an INT expression. | variance(c1) | N/A |
| variance(numeric) | Calculates the sample variance of the values returned by a NUMERIC expression. | variance(c1) | N/A |
| var_pop(float8) | Calculates the population variance of the values returned by a FLOAT8 expression. | var_pop(c1) | N/A |
| var_pop(int) | Calculates the population variance of the values returned by an INT expression. | var_pop(c1) | N/A |
| var_pop(numeric) | Calculates the population variance of the values returned by a NUMERIC expression. | var_pop(c1) | N/A |
| var_samp(float8) | Calculates the sample variance of the values returned by a FLOAT8 expression. | var_samp(c1) | N/A |
| var_samp(int) | Calculates the sample variance of the values returned by an INT expression. | var_samp(c1) | N/A |
| var_samp(numeric) | Calculates the sample variance of the values returned by a NUMERIC expression. | var_samp(c1) | N/A |