COVAR_SAMP

Purpose

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

Syntax

covar_samp ::=

Covar_Samp

Usage Notes

  • If either expr1 or expr2 is the value 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_SAMP(age, current_salary) COVAR_SAMP
FROM employee_table GROUP BY department;
Result
DEPARTMENT COVAR_SAMP
ACCOUNTS 20000
HR 60833.333

Analytic Function

SELECT 
    id, department, age, current_salary, 
    COVAR_SAMP(age, current_salary) OVER (PARTITION BY department ORDER BY age) COVAR_SAMP
FROM employee_table ORDER BY department, age;
Result
ID DEPARTMENT AGE CURRENT_SALARY COVAR_SAMP
2003 ACCOUNTS 27 80000 NULL
2001 ACCOUNTS 30 55000 -37500
2002 ACCOUNTS 32 65000 -21666.667
2004 ACCOUNTS 42 70000 20000
2005 ACCOUNTS 42 80000 20000
1001 HR 30 55000 0
1004 HR 30 70000 0
1002 HR 34 70000 10000
1003 HR 40 90000 60833.333