MySQL product(column)?
Another MySQL question....
The ability to do a "sum(column1) .... group by column2" is incredibly convenient. Ditto count, avg, std, etc. It would be even more convenient to be able to do a "product(column)". But I must be dumber than your average towel, because I can't find such a thing anywhere in the language reference. I can't imagine it's impossible to do such a simple calculation. There must even be a way to do "[insert expression here](column)", but I can't find that either. Any suggestions? Or do I need to submit this as a suggestion for inclusion in MySQL 5.0?
[ 03-06-2003: Message edited by: Towel ]</p>
The ability to do a "sum(column1) .... group by column2" is incredibly convenient. Ditto count, avg, std, etc. It would be even more convenient to be able to do a "product(column)". But I must be dumber than your average towel, because I can't find such a thing anywhere in the language reference. I can't imagine it's impossible to do such a simple calculation. There must even be a way to do "[insert expression here](column)", but I can't find that either. Any suggestions? Or do I need to submit this as a suggestion for inclusion in MySQL 5.0?
[ 03-06-2003: Message edited by: Towel ]</p>
Comments
<strong>Another MySQL question....
The ability to do a "sum(column1) .... group by column2" is incredibly convenient. Ditto count, avg, std, etc. It would be even more convenient to be able to do a "product(column)". But I must be dumber than your average towel, because I can't find such a thing anywhere in the language reference. I can't imagine it's impossible to do such a simple calculation. There must even be a way to do "[insert expression here](column)", but I can't find that either. Any suggestions? Or do I need to submit this as a suggestion for inclusion in MySQL 5.0?
[ 03-06-2003: Message edited by: Towel ]</strong><hr></blockquote>
Im curious as to the application of a running product on a column? what type of data are you accumulating?
you know you could dump the column of data into an excel spreadsheet (or free-equivelent) and do somehting like that with formulas pretty quick. I think that product() is more of spreadsheet style function rather than database, ive not seen it in other rdbms. but you never know, there is tons of custom shiznit in Oracle they could have a version of which you speak.
<strong>Im curious as to the application of a running product on a column? what type of data are you accumulating?</strong><hr></blockquote>
It's data from microarrays (gene chips). Specifically p-values for a change in expression levels between conditions. I've shifted all my analysis from Excel to MySQL because Excel is just too slow and clumsy when dealing with large files and complex manipulations.
Each gene on the chip (44,000 of them) is assigned to a category (871 all together). I want to find the product of all member p-values for each category, which I can then plug into an algorithm to see if the category as a whole has changed expression significantly. "GROUP BY category" povides a marvelously easy way to lump genes together by categories, something that (because of the complexity of genes belonging to more than one category) I found requires a sizeable VB script and several hours of running time on Excel.
But while it would be trivial to find the SUM of p-values (or average, standard deviation, or a few other funtions), the ability to find the product of groups seems to be missing in MySQL.
I think I might have to figure out how to add a user-definied funtion. Any advice on that would be most helpful, too. You'll get an acknowledgement in the paper that comes out of this.
heres an approximation ..not necessarily
correct...check the docs for your database
select sum(col1)
from t1
where
(
select sum(col1)
from t1
where blah
group by col1
)
or use the union operator
or use a scalar function
theres a lot of ways to kill the cat
check the docs ...im an oracle dba so im not
sure if mysql implements all the above
operators or even allows nested queries
good luck
[quote]Originally posted by Towel:
<strong>
It's data from microarrays (gene chips). Specifically p-values for a change in expression levels between conditions. I've shifted all my analysis from Excel to MySQL because Excel is just too slow and clumsy when dealing with large files and complex manipulations.
Each gene on the chip (44,000 of them) is assigned to a category (871 all together). I want to find the product of all member p-values for each category, which I can then plug into an algorithm to see if the category as a whole has changed expression significantly. "GROUP BY category" povides a marvelously easy way to lump genes together by categories, something that (because of the complexity of genes belonging to more than one category) I found requires a sizeable VB script and several hours of running time on Excel.
But while it would be trivial to find the SUM of p-values (or average, standard deviation, or a few other funtions), the ability to find the product of groups seems to be missing in MySQL.
I think I might have to figure out how to add a user-definied funtion. Any advice on that would be most helpful, too. You'll get an acknowledgement in the paper that comes out of this.
<strong>
select sum(col1)
from t1
where
(
select sum(col1)
from t1
where blah
group by col1
)
</strong><hr></blockquote>
And the result is a product?
I think what you need is this (aggregate):
<a href="http://www.mysql.com/doc/en/CREATE_FUNCTION.html"; target="_blank">http://www.mysql.com/doc/en/CREATE_FUNCTION.html</a>;
Writing such functions is fairly easy, you have to implement a few (5) function calls. I've never done this on a Mac though, don't know exactly how the shared lib thing is handled.
I think what you need is this (aggregate):
<a href="http://www.mysql.com/doc/en/CREATE_FUNCTION.html"; target="_blank">http://www.mysql.com/doc/en/CREATE_FUNCTION.html</a>;
<hr></blockquote>
Yeah, that is what I need. I was playing with it tonight, but got hung up on the need to recompile MySQL from source with a flag to accept dynamic loading. Seems odd to have to jump through such a hoop just to get it to accept user definied functions. Anyway, I didn't have any luck installing it from source originally (binaries work great) and didn't this time either. Ah well, that's what weekends are for. I think once I just get it compiled and working figuring how to make a function shouldn't be too bad. Thanks for the help!