How can I calculate the correlation between two columns in Amazon Redshift?

user_90580 0
Viewed 1055

Let's say that I have the following table

create temporary table source( col1 float,  col2 float)
;
insert into source(col1,col2)
values (1, 10020)
      ,(2, 20062)
      ,(3, 30083)
      ,(4, 40099)
      ,(5, 50012)
      ,(6, 60035)
      ,(7, 70087)
      ,(8, 80015)
      ,(9, 90039)
      ,(10,100099);

where the col1, col2 are clearly correlated (col2 = col1 * 10000 + small random quantity)

Is there any easy way to calculate any kind of correlation coefficient between columns in redshift?

amazon-redshift
1 Answers

As far as I know Redshift does not support CORR(x,y) or COVAR_POP(x,y), as it is explicitly mentioned in Unsupported PostgreSQL functions.

The (unsupported) PostgresSQL's corr(y,x) would have calculated the correlation coefficient.

Since that function is not supported in Redshift you can calculate the correlation coefficent in plain SQL with

with t1 as (
    select
        col1
        ,avg(col1) over() as avg_col1
        ,col2
        ,avg(col2) over() as avg_col2
    from source
)
select
  sum( (col1-avg_col1) *(col2-avg_col2)) as numerator
 ,sqrt(sum( (col1-avg_col1)^2)) * sqrt(sum( (col2-avg_col2)^2)) as denominator
 ,numerator/denominator as correlation_coeff
from t1;

which gives the following result using the OP dataset:

numerator,denominator,correlation_coeff
825098.5,825099.0469993587,0.999999337050066

with values :

drop table source;
create temporary table source( col1 float,  col2 float)
;
insert into source(col1,col2)
values (1, 10020)
      ,(2, 20062)
      ,(3, 30083)
      ,(4, 40099)
      ,(5, 50012)
      ,(6, 60035)
      ,(7, 70087)
      ,(8, 40015)
      ,(9, 40039)
      ,(10,70000);

it gives a correlation coefficient of 0.7599329507675849 which matches what I get using R to calculate the same:

var1 <- c(1,2,3,4,5,6,7,8,9,10)
var2 <- c(10020,20062, 30083, 40099, 50012,60035, 70087, 40015, 40039, 70000)
cor.test(var1,var2, method="pearson")$estimate # 0.759933
user_905800
Related