CORR or CORRELATION aggregate function
The CORR function returns the coefficient of the correlation of a set of number pairs.
The schema is SYSIBM.
The argument values must each be the value of any built-in numeric data type.
If an argument is DECFLOAT(n), the result of the function is DECFLOAT(34). Otherwise, the result of the function is double precision floating-point. The result is between -1 and 1. The result can be null.
The function is applied to the set of (expression-1, expression-2) pairs derived from the argument values by the elimination of all pairs for which either expression-1 or expression-2 is null.
If the function is applied to an empty set, or if either STDDEV(expression-1) or STDDEV(expression-2) 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(expression-1,expression-2)/
(STDDEV(expression-1)* STDDEV(expression-2))The order in which the values are aggregated is undefined, but every intermediate result must be within the range of the result data type.
Using sample table DSN8C10.EMP, set the host variable :corrln (double-precision floating point) to the correlation between the salary and the bonus for those employees in department (WORKDEPT) 'A00'.
SELECT CORRELATION(SALARY, BONUS) INTO :corrln
FROM DSN8C10.EMP WHERE WORKDEPT = 'A00';