kerry kerry - 2 months ago 11
MySQL Question

php function or mysql statement to provide correlation coefficient between2 columns

Can anybody point me in the direction of or give me an example of a function in php or query in mysql that will return the correlation coefficient between 2 columns in a mysql table. I have googled and looked at several SO questions and answers - I am not proficient in php or mysql - I am hoping for a simple function that takes 2 column names and returns the correlation - any ideas please guys

Answer

Adapted from a similar question found on the interwebs perhaps something like this might be of use?

select ( avg( `col1` * `col2` ) - avg( `col1` ) + avg( `col2` ) ) / ( stddev( `col1` ) * stddev(`col2`) ) as 'correlation'
from `table`;