I have a MySQL table of correlation data that I need to extract. I am running this query to find the correlation between x and y given the id; however, I need to run x.id=1 against y.id=2, 3, 4, 5... Currently, I am just running the below command multiple times for each pairing. Is there a way to speed up the query so that I can just run the query once like the second equation?
SELECT @correlation := (COUNT(x.value)*SUM(x.value*y.value) -
sum(x.value)*sum(y.value))/SQRT((COUNT(x.value)*SUM(x.value*x.value) -
sum(x.value)*sum(x.value))*(COUNT(y.value)*SUM(y.value*y.value) -
sum(y.value)*sum(y.value))) AS correlation
FROM tbl_expressions x, tbl_expressions y
WHERE x.id=1 AND y.id=2 AND x.expressionnumber=y.expressionnumber;
SELECT @correlation := (COUNT(x.value)*SUM(x.value*y.value) -
sum(x.value)*sum(y.value))/SQRT((COUNT(x.value)*SUM(x.value*x.value) -
sum(x.value)*sum(x.value))*(COUNT(y.value)*SUM(y.value*y.value) -
sum(y.value)*sum(y.value))) AS correlation
FROM tbl_expressions x, tbl_expressions y
WHERE x.id=1 AND y.id IN (2, 3, 4, 5, 6, 7) AND x.expressionnumber=y.expressionnumber;
I want the result to be something like this:
correlation
.83
.82
.74
.64
...
but right now the query only outputs one row (the correlation between x.id and the last y.id)