COVAR_POP

Purpose

This function returns the population covariance of a set of number pairs (a type of relation measure). This equates to the following formula:

Syntax

covar_pop ::=

Covar_pop

Usage Notes

  • If either expr1 or expr2 is NULL, Exasol removes the corresponding row from the window before the computation of the function.
  • For information about the over_clause and analytic functions in general, refer to the Analytic Functions section.

Examples

Aggregate Function

SELECT 
    department, 
    COVAR_POP(age, current_salary) COVAR_POP 
FROM employee_table GROUP BY department;
Result
DEPARTMENT COVAR_POP
ACCOUNTS 16000
HR 45625

Analytic Function

SELECT 
    id, department, age, current_salary, 
    COVAR_POP(age, current_salary) OVER (PARTITION BY department ORDER BY age) COVAR_POP 
FROM employee_table ORDER BY department, age;
Result
ID DEPARTMENT AGE CURRENT_SALARY COVAR_POP
2003 ACCOUNTS 27 80000 0
2001 ACCOUNTS 30 55000 -18750
2002 ACCOUNTS 32 65000 -14444.444
2004 ACCOUNTS 42 70000 16000
2005 ACCOUNTS 42 80000 16000
1001 HR 30 55000 0
1004 HR 30 70000 0
1002 HR 34 70000 6666.6667
1003 HR 40 90000 45625