CORR

Purpose

This function returns the coefficient of correlation of a set of number pairs (a type of relation measure).

Therefore, it equates to the following formula:

Syntax

corr::=

CORR

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.

Example

Aggregate Function

Copy 
SELECT 
    department, 
    CORR(age, current_salary) CORR
FROM employee_table GROUP BY department;
Result
DEPARTMENT CORR
ACCOUNTS 0.26992544
HR 0.89632985

Analytic Function

Copy 
SELECT 
    id, department, age, current_salary, 
    CORR(age, current_salary) OVER (PARTITION BY department order by age) CORR
FROM employee_table ORDER BY department, age;
Result
ID DEPARTMENT AGE CURRENT_SALARY CORR
2003 ACCOUNTS 27 80000 NULL
2001 ACCOUNTS 30 55000 -1
2002 ACCOUNTS 32 65000 -0.68421053
2004 ACCOUNTS 42 70000 0.26992544
2005 ACCOUNTS 42 80000 0.26992544
1001 HR 30 55000 NULL
1004 HR 30 70000 NULL
1002 HR 34 70000 0.5
1003 HR 40 90000 0.89632985