9.3Statistical Aggregate Functions

9.3.1CORR()

Correlation coefficient

Result typeDOUBLE PRECISION

Syntax

  |CORR ( <expr1>, <expr2> )

Table 9.7CORR Function Parameters
ParameterDescription

exprN

Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.

The CORR function return the correlation coefficient for a pair of numerical expressions.

The function CORR(<expr1>, <expr2>) is equivalent to

  |COVAR_POP(<expr1>, <expr2>) / (STDDEV_POP(<expr2>) * STDDEV_POP(<expr1>))

This is also known as the Pearson correlation coefficient.

In a statistical sense, correlation is the degree to which a pair of variables are linearly related. A linear relation between variables means that the value of one variable can to a certain extent predict the value of the other. The correlation coefficient represents the degree of correlation as a number ranging from -1 (high inverse correlation) to 1 (high correlation). A value of 0 corresponds to no correlation.

If the group or window is empty, or contains only NULL values, the result will be NULL.

9.3.1.1CORR Examples

  |select
  |  corr(alength, aheight) AS c_corr
  |from measure

See alsoSection 9.3.2, “COVAR_POP(), Section 9.3.4, “STDDEV_POP()

9.3.2COVAR_POP()

Population covariance

Result typeDOUBLE PRECISION

Syntax

  |COVAR_POP ( <expr1>, <expr2> )

Table 9.8COVAR_POP Function Parameters
ParameterDescription

exprN

Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.

The function COVAR_POP returns the population covariance for a pair of numerical expressions.

The function COVAR_POP(<expr1>, <expr2>) is equivalent to

  |(SUM(<expr1> * <expr2>) - SUM(<expr1>) * SUM(<expr2>) / COUNT(*)) / COUNT(*)

If the group or window is empty, or contains only NULL values, the result will be NULL.

9.3.2.1COVAR_POP Examples

  |select
  |  covar_pop(alength, aheight) AS c_covar_pop
  |from measure

See alsoSection 9.3.3, “COVAR_SAMP(), Section 9.2.6, “SUM(), Section 9.2.2, “COUNT()

9.3.3COVAR_SAMP()

Sample covariance

Result typeDOUBLE PRECISION

Syntax

  |COVAR_SAMP ( <expr1>, <expr2> )

Table 9.9COVAR_SAMP Function Parameters
ParameterDescription

exprN

Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.

The function COVAR_SAMP returns the sample covariance for a pair of numerical expressions.

The function COVAR_SAMP(<expr1>, <expr2>) is equivalent to

  |(SUM(<expr1> * <expr2>) - SUM(<expr1>) * SUM(<expr2>) / COUNT(*)) / (COUNT(*) - 1)

If the group or window is empty, contains only 1 row, or contains only NULL values, the result will be NULL.

9.3.3.1COVAR_SAMP Examples

  |select
  |  covar_samp(alength, aheight) AS c_covar_samp
  |from measure

See alsoSection 9.3.2, “COVAR_POP(), Section 9.2.6, “SUM(), Section 9.2.2, “COUNT()

9.3.4STDDEV_POP()

Population standard deviation

Result typeDOUBLE PRECISION or NUMERIC depending on the type of expr

Syntax

  |STDDEV_POP ( <expr> )

Table 9.10STDDEV_POP Function Parameters
ParameterDescription

expr

Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.

The function STDDEV_POP returns the population standard deviation for a group or window. NULL values are skipped.

The function STDDEV_POP(<expr>) is equivalent to

  |SQRT(VAR_POP(<expr>))

If the group or window is empty, or contains only NULL values, the result will be NULL.

9.3.4.1STDDEV_POP Examples

  |select
  |  dept_no
  |  stddev_pop(salary)
  |from employee
  |group by dept_no

See alsoSection 9.3.5, “STDDEV_SAMP(), Section 9.3.6, “VAR_POP(), SQRT

9.3.5STDDEV_SAMP()

Sample standard deviation

Result typeDOUBLE PRECISION or NUMERIC depending on the type of expr

Syntax

  |STDDEV_POP ( <expr> )

Table 9.11STDDEV_SAMP Function Parameters
ParameterDescription

expr

Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.

The function STDDEV_SAMP returns the sample standard deviation for a group or window. NULL values are skipped.

The function STDDEV_SAMP(<expr>) is equivalent to

  |SQRT(VAR_SAMP(<expr>))

If the group or window is empty, contains only 1 row, or contains only NULL values, the result will be NULL.

9.3.5.1STDDEV_SAMP Examples

  |select
  |  dept_no
  |  stddev_samp(salary)
  |from employee
  |group by dept_no

See alsoSection 9.3.4, “STDDEV_POP(), Section 9.3.7, “VAR_SAMP(), SQRT

9.3.6VAR_POP()

Population variance

Result typeDOUBLE PRECISION or NUMERIC depending on the type of expr

Syntax

  |VAR_POP ( <expr> )

Table 9.12VAR_POP Function Parameters
ParameterDescription

expr

Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.

The function VAR_POP returns the population variance for a group or window. NULL values are skipped.

The function VAR_POP(<expr>) is equivalent to

  |(SUM(<expr> * <expr>) - SUM (<expr>) * SUM (<expr>) / COUNT(<expr>))
  |  / COUNT (<expr>)

If the group or window is empty, or contains only NULL values, the result will be NULL.

9.3.6.1VAR_POP Examples

  |select
  |  dept_no
  |  var_pop(salary)
  |from employee
  |group by dept_no

See alsoSection 9.3.7, “VAR_SAMP(), Section 9.2.6, “SUM(), Section 9.2.2, “COUNT()

9.3.7VAR_SAMP()

Sample variance

Result typeDOUBLE PRECISION or NUMERIC depending on the type of expr

Syntax

  |VAR_SAMP ( <expr> )

Table 9.13VAR_SAMP Function Parameters
ParameterDescription

expr

Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.

The function VAR_POP returns the sample variance for a group or window. NULL values are skipped.

The function VAR_SAMP(<expr>) is equivalent to

  |(SUM(<expr> * <expr>) - SUM(<expr>) * SUM (<expr>) / COUNT (<expr>))
  |  / (COUNT(<expr>) - 1)

If the group or window is empty, contains only 1 row, or contains only NULL values, the result will be NULL.

9.3.7.1VAR_SAMP Examples

  |select
  |  dept_no
  |  var_samp(salary)
  |from employee
  |group by dept_no

See alsoSection 9.3.6, “VAR_POP(), Section 9.2.6, “SUM(), Section 9.2.2, “COUNT()