COVARIANCE_SAMP aggregate function

The COVARIANCE_SAMP function returns the sample covariance of a set of number pairs.

The schema is SYSIBM.

expression1: An expression that returns a value of any built-in numeric data type.
expression2: An expression that returns a value of any built-in numeric data type.

If either argument is decimal floating-point, the result is DECFLOAT(34); otherwise, the result is a double-precision floating-point number. The result can be null.

The function is applied to the set of (expression1, expression2) pairs that are 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 a set with only one row, the result is a null value. Otherwise, the result is the sample covariance of the value pairs in the set.

The calculation that is used to determine the sample covariance is logically equivalent to the following formula:

COVARIANCE_SAMP = SUM( 
   ( expression1 - AVG(expression1) ) * 
   ( expression2 - AVG(expression2) ) ) / ( COUNT(expression1) – 1 )

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_SAMP can be specified in place of COVARIANCE_SAMP.

Example

Set the host variable COVARNCE to the sample covariance between the salary and bonus for those employees in department 'A00' of the EMPLOYEE table. The data type of the host variable COVARNCE is double-precision floating point.

   SELECT COVARIANCE_SAMP(SALARY, BONUS)
     INTO :COVARNCE
     FROM EMPLOYEE
     WHERE WORKDEPT = 'A00'

COVARNCE is set to approximately +5.42875000000000E+006 when the sample table is used.

The following result set is shown for reference.

SELECT SALARY, BONUS FROM EMPLOYEE WHERE WORKDEPT = 'A00'

SALARY      BONUS      
----------- -----------
  152750.00     1000.00
   66500.00      900.00
   49250.00      600.00
   46500.00     1000.00
   39250.00      600.00

5 record(s) selected.