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 ::=
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 |