Introduction
"Statistics for MySQL" provides additional statistical functions (like Pearson's coefficient of correlation) which are missing in MySQL. DLLs for 32 and 64bit Windows as well as makefiles for Linux are available.
Download
The latest release of "Statistics for MySQL" is available at http://sourceforge.net/projects/sqlstat/
You can download the latest development version from the subversion repository with
svn co http://svn.code.sf.net/p/sqlstat/code/ libsqlstat
or view it the repository at http://sourceforge.net/p/sqlstat/code/.
Installation
Linux
Download and unzip the source tar ball. Follow the instructions in file INSTALL.
Windows
Remove old version
When upgrading it is necessary to first uninstall the old version of Statistics for MySQL
Drop definitions of functions provided by the previous version of Statistics for MySQL. Uninstall the plugin. This can be achieved by running the uninstall.sql script of the previous version
mysql -u root -p -f < examples\uninstall.sql
Stop the MySQL service
Remove the old Statistics for MySQL library. You find it as file libsqlstat.dll in the lib\plugin directory of your MySQL installation.
Install new version
Download and unzip the distribution tar ball.
Depending on whether you are using a 32 or a 64 bit version of MySQL navigate to directory w32 or w64. Copy libsqlstat.dll to the lib\plugin directory of your MySQL installation. You can determine the location of the plugin directory with the following command
SHOW VARIABLES LIKE 'PLUGIN_DIR';
Start the MySQL service
Run
mysql -u root -p -f < examples\install.sql
Quick Guide
To use any of the functions provided by Statistics for MySQL they have to be defined in MySQL by the CREATE FUNCTION command, e.g.
CREATE AGGREGATE FUNCTION corr RETURNS REAL SONAME 'libsqlstat';
Some functionality is only available if Statistics for MySQL is installed as plugin. For installation use the INSTALL PLUGIN command, e.g.
INSTALL PLUGIN libsqlstat SONAME 'libsqlstat';
File examples/install.sql provides all necessary statements.
The following functions are provided:
- avgw(x [,w]) - weighted average
- corr(x, y [,w]) - calculate Pearson correlation coefficient
- gini(x [,w]) - calculate Gini coefficient
- median(x) - calculate median
- rand_mt() - generate random number using the Mersenne Twister algorithm
- rand_norm() - generate variate of the normal distribution
- rownumber() - row number
- skewness_pop(x [,w]) - calculate population moment coefficient of skewness
- skewness_samp(x [,w]) - calculate sample moment coefficient of skewness
- stddevw_pop(x [,w]) - calculate weighted standard deviation of population
- stddevw_samp(x [,w]) - calculate weighted standard deviation of sample
License
Statistics for MySQL is licensed under the Apache License, Version 2.0.
You may obtain a copy of the license at http://www.apache.org/licenses/LICENSE-2.0.