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

SELECT 
    department, 
    CORR(age, current_salary) CORR
FROM employee_table GROUP BY department;

Result

DEPARTMENT	CORR
ACCOUNTS	0.26992544
HR	0.89632985

Analytic Function

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