聚合函数的作用是根据一个输入值的集合计算出一个单独的结果值。

下面的表中列出了内置的聚合函数。

表 1. General-Purpose Aggregate Functions
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的时候,函数会返回空值。

表 2. Aggregate Functions for Statistics
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)