Have Geek Brain

Displaying all rows in a calculation?

I have a MySQL correlation data table that I need to retrieve. I am running this query to find the correlation between x and y given id; however I need to run x.id = 1 against y.id = 2, 3, 4, 5 ... Currently, I just run the following 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 (correlation between x.id and last y.id)

+1


a source to share


2 answers


It is difficult to answer this because it is not clear from your description what you need to fulfill the request.



But I would suggest that you could use GROUP BY

to achieve what you want. Sorry, I can't show you an example because I can't tell you what you intend to get.

0


a source


if you're using MySQL 5.0 and up, you might want to consider stored procedures to handle some of this stuff ....



have you counted subqueries? http://dev.mysql.com/doc/refman/5.1/en/subqueries.html

0


a source






More articles:


All Articles