Calculating correlation coefficient using PostgreSQL?

Question

I have worked out how to calculate the correlation coefficient between two fields if both are in the same table:

SELECT corr(column1, column2) FROM table WHERE <my filters>;

...but I can't work out how to do it when the columns are from different tables (I need to apply the same filters to both tables).

Any hints, please?

Answer 1

If the tables are related to one another such that you can join them, it's fairly simple. Just join them and do the correlation:

SELECT corr(t1.col1, t2.col2)
FROM table1 t1
     JOIN table2 t2
         ON t1.join_field = t2.join_field
WHERE
     <filters for t1>
     AND
     <filters for t2>

If they're not, then how are you supposed to find out which combination of fields from each table you want to run corr on?

Answer 2

try this

SELECT corr(t1.column1, t2.column2) 
FROM table1 t1
join table2 t2 on t1.SomeColumn = t2.SomeColumn 
WHERE t1.<my filters>
AND t2.<my filters>;

Answer 3

Hello

Why is it not possible to get the correlation for every row of a table? it works for a hole table. i tried to do this:

SELECT corr(column1, column2) FROM table group by iso3_code;

or for example

SELECT corr(column1, column2) FROM table where iso3_code='DEU';

But i get an empty result. Has anybody an idea?!?