Start of change

COVAR_SAMP or COVARIANCE_SAMP

The COVAR_SAMP function returns unbiased sample covariance (n-1) of a set of number pairs.

Read syntax diagramSkip visual syntax diagramCOVAR_SAMPCOVARIANCE_SAMP( expression-1, expression-2)

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 sample covariance of the value pairs in the set. The result is equivalent to the following output:

  1. 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.
  2. The result of COVAR_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.

Start of changeThe following restrictions apply to COVAR_SAMP:End of change

  • Start of changeCOVAR_SAMP cannot be used as part of an OLAP specification.End of change

Notes

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

Example

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

   SELECT COVAR_SAMP(SALARY, BONUS)
     INTO :COVARNCE
     FROM DSN8C10.EMP
     WHERE WORKDEPT = 'A00';

The value of COVARNCE is set to 2178750.

End of change