Calculating correlation coefficient using PostgreSQL?

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?

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?

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