SQL Reference

CORRELATION

>>-+-CORRELATION-+--(----expression1--,--expression2--)--------><
   '-CORR--------'
 

The schema is SYSIBM.

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

The argument values must be numbers.

The data type of the result is double-precision floating point. The result can be null. When not null, the result is between 0 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.

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.