Re: [MonetDB-users] Standard Deviation tips and hints request

Hi again, I implemented the MAL functions to compute the stdev of columns with various numerical types. The code has been checked in to the development branch of the MonetDB cvs, so in order to obtain it you have to check out from our cvs. If you want to take a look how this can be done in Monet code, open the file: MonetDB5/src/modules/kernel/algebra.mx of the MonetDB5 source package and search for functions containing the string "stdev". In the MAL level, you can call this function as: a:dbl := aggr.stdev(:bat[:oid,:any]); where any can be: int, flt, dbl, etc (all numericals) Now for the SQL front end this is a bit more tricky because it has to become part of the SQL syntax, since one can apply the STDEV function to any subquery result or intermediate result. We are working on this, please stay tune. but for the time, maybe we can find a temporal solution, were you can apply the MAL function aggr.stdev to columns that are already part of the sql catalog throw a "create procudure" sql statement. kind regards, lefteris On Sun, Jan 17, 2010 at 10:17 AM, Lefteris wrote:

Hi Dennis,

sorry for the late reply. I assume that you use the SQL front end and thus MonetDB5 server. In this case the language is MAL and not MIL. But this is of little importance concerning your problem:)

Tha fastest way to compute the std deviation would be to define a C function that takes as input a column and outputs the std deviation. then you would make available that function in the sql frontend. Instead of using C you may use MAL also. I am not sure which one will be faster, but if the function available at the MAL level are suited for this computation then MAL would be as fast as C. Check out  http://monetdb.cwi.nl/SQL/Documentation/External-Functions.html on how to define MAL functions.

I am willing to do this, send it to you, and included it to a future release of Monet. Could you please send me the SQL query (the one with the cross join) and a description/sample of your data, in order to have an example/reference?

Thank you and again soryr for the late reply.

lefteris

On Thu, Jan 14, 2010 at 4:42 PM, Dennis Forbes wrote:

...

Hi all.

While it’s possible to calculate the stddev with an aggregated cross join, is there perhaps a better way? Any tips or hints on where I should look? Can this be done in MIL or some other tactic to provide a more straightforward, better performing experience?

I searched the web before asking and all I’ve found are unanswered questions, however I would really like to solve this even if it includes extending the system.

Dennis W. Forbes

Vastardis Capital Services

41 Madison Avenue, 30th Floor

New York, NY 10010

P: 646-230-0933

F: 212-243-5251

------------------------------------------------------------------------------ Throughout its 18-year history, RSA Conference consistently attracts the world's best and brightest in the field, creating opportunities for Conference attendees to learn about information security's most important issues through interactions with peers, luminaries and emerging and established companies. http://p.sf.net/sfu/rsaconf-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users