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.