COVARIANCE or COVARIANCE_SAMP

The COVARIANCE and COVARIANCE_SAMP functions return the covariance (population) of a set of number pairs.


>>-+-COVARIANCE------+-(expression-1,expression-2)-------------><
   '-COVARIANCE_SAMP-'

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 can be null.

The function is applied to the set of (expression-1, expression-2) pairs that are 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, the result is a null value. Otherwise, the result is the covariance of the value pairs in the set. The result is equivalent to the following outputs:

For COVARIANCE:

Let avgexp1 be the result of AVG(expression-1) and let avgexp2 be the result of AVG(expression-2).
The result of COVARIANCE(expression-1,expression-2) is AVG( (expression-1 - avgexp1) * (expression-2 - avgexp2))

For COVARIANCE_SAMP:

Let samp_avgexp1 be the result of SUM(expression-1)/n-1 and let samp_avgexp2 be the result of SUM(expression-2)/n-1.
The result of COVARIANCE_SAMP(expression-1, expression-2) is AVG((expression-1 - samp_avgexp1) * (expression-2 - samp_avgexp2))

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

COVAR can be specified as a synonym for COVARIANCE.

COVAR_SAMP can be specified as a synonym for COVARIANCE_SAMP.

Example: Using sample table DSN8A10.EMP, set the host variable covarnce (double-precision floating point) to the covariance between the salary and the bonus for those employees in department (WORKDEPT) 'A00'.

   SELECT COVARIANCE(SALARY, BONUS) INTO :covarnce
     FROM EMPLOYEE WHERE WORKDEPT = 'A00';

covarnce is set to approximately 1.68888888888889E+006.