SQL: add various statistical functions #2454
Last updated: 2019-01-21 15:36:55 +0100
Date: 2008-06-08 08:32:08 +0200
MonetDB should support the median and quantile functions
Date: 2008-06-15 12:22:51 +0200
Logged In: YES
MySQL and Oracle also provide stddev() and variance()
Date: 2010-05-04 09:32:09 +0200
This bug was previously known as tracker item 1987928 at http://sourceforge.net/support/tracker.php?aid=1987928
Date: 2010-09-23 23:03:13 +0200
Created attachment 36
Implementation of standard deviation for sample (stdev) and population (stdevp); variance for sample(vars) and population(varp). This function also available from sql as aggregation function.
Date: 2010-09-24 09:40:58 +0200
The patch looks nice, but it misses the very important version's for group-ed aggregation. For example, there is an implementation in group.mx (GRPvar*), which seems close.
Date: 2010-09-24 09:45:05 +0200
(In reply to comment 4)
So should i create something similar to GRPvar_ for stdev and variance for sample?
Date: 2010-09-24 09:58:39 +0200
Problem with the patch is that it changes the catalog. Doesn't that cause trouble Niels? Probably the functions should be added with a .sql file which declares them as external (=MAL) functions.
Date: 2010-09-24 10:13:30 +0200
(In reply to comment 6)
I try do do it from sql, but for a some reason i can't do it. SQL doesn't allow to create aggregation function.
Date: 2010-10-06 23:31:38 +0200
Created attachment 43
http://bugs.monetdb.org/attachment.cgi?id=36 was updated now it support group by.
Date: 2010-10-06 23:33:15 +0200
I updated functions Standard deviation and variance, so now they support group by, could you please review it. http://bugs.monetdb.org/attachment.cgi?id=43
(In reply to comment 4)
Date: 2010-10-27 16:33:55 +0200
Any comments on latest patch?
Date: 2011-01-25 15:24:22 +0100
Date: 2011-03-03 01:03:05 +0100
We have been using the statistical functions that Alex wrote for us and they are working well for us. When the functions were used with "group by", the Oct2010 Release instantly crashed. But, the functions work just fine in Oct2010-SP1 release. Is it possible to have these functions included in the next release? Thanks.
Date: 2011-03-03 08:52:29 +0100
Niels has to review it. It won't make it for the Mar2011 release, perhaps the Apr2011 release.
(Don't CC me, I'm on the alias)
Date: 2011-03-28 17:36:29 +0200
The Mar2011 version has been released.
Date: 2011-04-01 10:03:17 +0200
Did you already commit this patch into repo? Because i try to find functions by name and can't find them.
Date: 2011-07-29 11:00:02 +0200
Apr2011-SP2 has been released.
Date: 2011-09-16 15:10:37 +0200
The Aug2011 version has been released.
Date: 2012-05-09 17:38:34 +0200
stddev and median have been added.
Date: 2012-11-05 12:32:22 +0100
The stddev() function does not appear to be available in the Windows version of MonetDB?
When I run these commands, I get the following error when I hit stddev..
sql>select * from tables limit 3;
Here's a copy of my server window, which does have a release date after comment 18 below (when this issue was marked as resolved) --
MonetDB 5 server v11.11.11 "Jul2012-SP2"
On this topic, is it possible to also add some sort of quantile() function so that users can access something like the 25th, 75th, 90th percentiles quickly? This seems like just a small variation on the median() function (which does work), since quantile( colname , 50 ) would be the same as median( colname )?
My apologies if I'm overlooking something obvious, I am pretty new to MonetDB.
Thanks for the amazing software!!
Date: 2012-11-08 12:42:22 +0100
It appears that the problem is in sql/scripts/39_analytics.sql. It's missing bindings for stdev and stdevp.
For stdev, at least, it seems that one can mimic median with something like the following:
create aggregate stdev(val TINYINT) returns TINYINT
This can of course be run through the mclient, so anyone needing to patch their environment prior to this bug being fixed might be able to just run these manually.
Date: 2015-04-04 11:09:49 +0200
I hate to reopen this bug but statistical functions stopped to work in MonetDB-11.19.9-x86_64-Darwin-9-bin.tar.bz2.
For example following queries:
Similar error messages appear with other statistical messages mentioned at:
The queries were tested on:
Interestingly, the queries worked with MonetDB-11.19.7-x86_64-Darwin-9-bin.tar.bz2.
Date: 2015-04-04 11:22:31 +0200
this re-introduced bug is platform-specific. that code works fine for me on
MonetDB 5 server v11.19.9 "Oct2014-SP2"
Date: 2019-01-21 15:36:55 +0100
Closing since as far as I am aware, the statistical functions work on all supported platforms.
The text was updated successfully, but these errors were encountered: