CORR or CORRELATION

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

expression1: An expression that returns a value of any built-in numeric, character-string, or graphic-string data type. If the argument is a character-string or graphic-string, it is cast to DECFLOAT(34) before evaluating the function.
expression2: An expression that returns a value of any built-in numeric, character-string, or graphic-string data type. If the argument is a character-string or graphic-string, it is cast to DECFLOAT(34) before evaluating the function.

If either argument is decimal floating-point, the result of the function is DECFLOAT(34). Otherwise, the result of the function 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:

Let sdexp1 be the result of STDDEV(expression1) and let sdexp2 be the result of STDDEV(expression2).

The result of CORRELATION(expression1, expression2) is:

COVARIANCE(expression1, expression2) / (sdexp1 * sdexp2)

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

Notes®

Results involving DECFLOAT special values: If the data type of the argument is decimal floating-point and a special value of sNaN or -sNaN, or both +Infinity and -Infinity are included in the aggregation, an error or warning is returned. Otherwise, if +NaN or -NaN is found, the result is +NaN or -NaN. If +Infinity or -Infinity is found, the result is +Infinity or -Infinity.

Syntax alternatives: CORR should be used for conformance to the SQL standard.

Example

Using the EMPLOYEE table, set the host variable CORRLN to the correlation between the salary and the bonus for those employees in department 'A00'.
```
SELECT CORRELATION(SALARY, BONUS) 
  INTO :CORRLN 
  FROM EMPLOYEE
  WHERE WORKDEPT = ‘A00’;
```
CORRLN is set to 0.9760236077658643 when using the sample table.