DB2 Version 9.7 for Linux, UNIX, and Windows

CORRELATION aggregate function


>>-CORRELATION--(--expression1--,--expression2--)--------------><

The schema is SYSIBM.

The CORRELATION function returns the coefficient of correlation of a set of number pairs.

The argument values must be numbers.

If either argument is decimal floating-point, the result is DECFLOAT(34); otherwise, the result is a double-precision floating-point number. The result can be null. When not null, the result is between -1 and 1.

The function is applied to the set of (expression1, expression2) pairs derived from the argument values by the elimination of all pairs for which either expression1 or expression2 is null.

If the function is applied to an empty set, or if either STDDEV(expression1) or STDDEV(expression2) is equal to zero, the result is a null value. Otherwise, the result is the correlation coefficient for the value pairs in the set. The result is equivalent to the following expression:

   COVARIANCE(expression1,expression2)/
   (STDDEV(expression1)*
   STDDEV(expression2))

The order in which the values are aggregated is undefined, but every intermediate result must be within the range of the result data type.

CORR can be specified in place of CORRELATION.

Example:

Using the EMPLOYEE table, set the host variable CORRLN (double-precision floating point) to the correlation between salary and bonus for those employees in department (WORKDEPT) 'A00'.
```
   SELECT CORRELATION(SALARY, BONUS)
     INTO :CORRLN
     FROM EMPLOYEE
     WHERE WORKDEPT = 'A00'
```
CORRLN is set to approximately 9.99853953399538E-001 when using the sample table.