Calculating the correlation coefficient using PostgreSQL?

Question

I developed a way to calculate the correlation coefficient between two fields if both of them are in the same table:

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

... but I cannot figure out how to do this when the columns are from different tables (I need to apply the same filters to both tables).

Any clues please?

Answer 1

If the tables are linked together so that you can join them, it's pretty simple. Just join them and correlate:

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 this is not the case, then how should you find out which combination of fields from each table you want to run corron?

Answer 2

try it

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>;