COVARIANCE aggregate function
The COVARIANCE function returns the (population) 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 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, the result is a null value. Otherwise, the result is the covariance of the value pairs in the set.
COVARIANCE = SUM(
( expression1 - AVG(expression1) ) *
( expression2 - AVG(expression2) ) ) / COUNT(expression1)
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 or COVAR_POP can be specified in place of COVARIANCE.
Example
SELECT COVARIANCE(SALARY, BONUS)
INTO :COVARNCE
FROM EMPLOYEE
WHERE WORKDEPT = 'A00'
COVARNCE is set to
approximately 1.68888888888889E+006 when using the sample table.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.