Math Aggregate Functions

APPROX_COUNT_DISTINCT(expr)

The approximate number of distinct values of expr; this is faster to calculate than COUNT_DISTINCT but is only an approximation

APPROX_MEDIAN(expr)

The approximate median of expr; the result should be within about 2% of the true median value. This is equivalent to issuing APPROX_PERCENTILE(expr, 50).

APPROX_PERCENTILE(expr, p)

The approximate pth percentile of expr; p should be a value between 0.0 and 100.0. APPROX_PERCENTILE(expr, 50) will return the approximate median of expr.

AVG(expr)

Average of the values of expr

CORR(expr1, expr2)

Correlation coefficient of the values of expr1 and expr2

CORRELATION(expr1, expr2)

Alias for CORR

CORRCOEF(expr1, expr2)

Alias for CORR

COUNT(expr)

Count of non-null values of expr; use * to count all values within an aggregation group or over an entire table

COUNT_DISTINCT(expr)

Count of the distinct values of expr

COV(expr1, expr2)

Alias for COVAR_POP

COVAR(expr1, expr2)

Alias for COVAR_POP

COVARIANCE(expr1, expr2)

Alias for COVAR_POP

COVAR_POP(expr1, expr2)

Population covariance of the values of expr1 and expr2

COVAR_SAMP(expr1, expr2)

Sample covariance of the values of expr1 and expr2

KURT(expr)

Alias for KURTOSIS_POP

KURTOSIS(expr)

Alias for KURTOSIS_POP

KURTOSIS_POP(expr)

Population kurtosis of the values of expr

KURTOSIS_SAMP(expr)

Sample kurtosis of the values of expr

KURT_POP(expr)

Alias for KURTOSIS_POP

KURT_SAMP(expr)

Alias for KURTOSIS_SAMP

MAX(expr)

Maximum of the values of expr

MEAN(expr)

Alias for AVG

MIN(expr)

Minimum of the values of expr

PRODUCT(expr)

Product of the values of expr

REGR_AVGX(y, x)

Average of the independent variable (SUM(x)/N) of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs

REGR_AVGY(y, x)

Average of the dependent variable (SUM(y)/N) of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs

REGR_COUNT(y, x)

Number of input rows used in computing a linear regression, where both expressions are non-null

REGR_INTERCEPT(y, x)

Y-intercept of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs

REGR_R2(y, x)

Square of the correlation coefficient, marking how well the least-squares-fit linear regression fit the data set

REGR_SLOPE(y, x)

Slope of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs

REGR_SXX(y, x)

“Sum of squares” of the independent variable (SUM(x^2) - SUM(x)^2/N) of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs

REGR_SXY(y, x)

“Sum of Products” of independent variable times dependent variable (SUM(x * y) - SUM(x) * SUM(y)/N) of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs

REGR_SYY(y, x)

“Sum of squares” of the dependent variable (SUM(y^2) - SUM(y)^2/N) of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs

SKEW(expr)

Alias for SKEWNESS_POP

SKEWNESS(expr)

Alias for SKEWNESS_POP

SKEWNESS_POP(expr)

Population skew of the values of expr

SKEWNESS_SAMP(expr)

Sample skew of the values of expr

SKEW_POP(expr)

Alias for SKEWNESS_POP

SKEW_SAMP(expr)

Alias for SKEWNESS_SAMP

STDDEV(expr)

Population standard deviation over values of expr (i.e. the denominator is N)

STDDEV_POP(expr)

Population standard deviation over values of expr (i.e. the denominator is N)

STDDEV_SAMP(expr)

Sample standard deviation over values of expr (i.e. the denominator is N-1)

SUM(expr)

Sum of the values of expr

VAR(expr)

Population variance over values of expr (i.e. the denominator is N)

VAR_POP(expr)

Population variance over values of expr (i.e. the denominator is N)

VAR_SAMP(expr)

Sample variance over values of expr (i.e. the denominator is N-1)

VARIANCE(expr)

Alias for VAR

VARIANCE_POP(expr)

Alias for VAR_POP

VARIANCE_SAMP(expr)

Alias for VAR_SAMP