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(anyelement) | Aggregates the values of expression to an array. Hologres currently does not support aggregating array types, and does not support aggregating data types such as JSON, JSONB, TIMETZ, INTERVAL, INET, OID and UUID. | array_agg(c1) | |
| 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 |