聚合函数的作用是根据一个输入值的集合计算出一个单独的结果值。
下面的表中列出了内置的聚合函数。
| Function | Argument Type | Return Type | Description |
|---|---|---|---|
| AVG(expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | NUMBER for any integer type, DOUBLE PRECISION for a floating-point argument, otherwise the same as the argument data type | The average (arithmetic mean) of all input values |
| COUNT(*) | BIGINT | Number of input rows | |
| COUNT(expression) | Any | BIGINT | Number of input rows for which the value of expression is not null |
| MAX(expression) | Any numeric, string, ordate/time type | Same as argument type | Maximum value of expression across all input values |
| MIN(expression) | Any numeric, string, or date/time type | Same as argument type | Minimum value of expression across all input values |
| SUM(expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | BIGINT for SMALLINT or INTEGER arguments, NUMBER forBIGINT arguments, DOUBLE PRECISION for floating¬point arguments, otherwise the same as the argument data type | Sum of expression across all input values |
应该注意的是除了函数COUNT外，当没有查询出记录时，其它函数返回空值。特别是，如果没有记录的话，函数SUM返回空值，而不是像期待的那样，返回0。当必要的时候，您可以使用函数 COALESCE将空值替换为0。
下面的表格显示了通常用于统计分析的聚合函数（之所以和上面的通用聚合函数分开列出，是为了更明确各自的功能）。在描述中提到N的地方，表示对于那些输入表达式为非空的输入记录的数量 。在所有情况下，如果计算没有意义，例如，当N是0的时候，函数会返回空值。
| Function | Argument Type | Return Type | Description |
|---|---|---|---|
| CORR( Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | Correlation coefficient |
| COVAR POP( Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | Population covariance |
| COVAR SAMP( Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | Sample covariance |
| REGR AVGX( Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | Average of the independent variable (sum(X) / N) |
| REGR AVGY(Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | Average of the dependent variable (sum(Y) / N) |
| REGR COUNT( Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | Number of input rows in which both expressions are nonnull |
| REGR INTERCEPT(Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | y-intercept of the least- squares-fit linear equation determined by the (X, Y) pairs |
| REGR R2( Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | Square of the correlation coefficient |
| REGR SLOPE( Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | Slope of the least-squares- fit linear equation determined by the (X, Y) pairs |
| REGR SXX(Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | Sum (X2) - sum (X)2 / N ("sum of squares" of the independent variable) |
| REGR SXY(Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | Sum (X* Y) - sum (X) * sum( Y) / N ("sum of products" of independent times dependent variable) |
| REGR SYY( Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | Sum (Y2) - sum (Y)2 / N ("sum of squares" of the dependent variable) |
| STDDEV(expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | DOUBLE PRECISION for floating-point arguments, otherwise NUMBER | Historic alias for STDDEV SAMP |
| STDDEV POP(expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | DOUBLE PRECISION for floating-point arguments, otherwise NUMBER | Population standard deviation of the input values |
| STDDEV SAMP(expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | DOUBLE PRECISION for floating-point arguments, otherwise NUMBER | Sample standard deviation of the input values |
| VARIANCE(expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | DOUBLE PRECISION for floating-point arguments, otherwise NUMBER | Historical alias for VAR SAMP |
| VAR POP(expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | DOUBLE PRECISION for floating-point arguments, otherwise NUMBER | Population variance of the input values (square of the population standard deviation) |
| VAR SAMP(expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | DOUBLE PRECISION for floating-point arguments, otherwise NUMBER | Sample variance of the input values (square of the sample standard deviation) |