Although MySQL is a powerful and fast database server, there are certain tasks that cannot be done with it so easily.
If you've ever tried to

• calculate the median of any values,
• calculate the skewness and kurtosis of a distribution of value
• retrieve the effective length of the longest value in a STRING column,
• get the longest value from a STRING column,
• calculate the faculty of a value,
• calculate linear regression parameters (intercept, slope, correlation coefficent) of any values,

directly from MySQL, you'll already know what I am writing about.

In fact, these tasks can only be performed in MySQL with ugly workarounds or require the use of additional programming on the client side.
This is where UDFs (User Defined Functions) come into play. UDFs are a nice and simple way to enhance your MySQL server's functionality with custom functions.

As you've already guessed, the aim of this page is to provide you with UDFs for some common tasks that can't be done in MySQL without UDFs that easily or not at all.

The UDFs provided via this page are available under a BSD style license. The UDFs are open source and you can also use them free of charge in your projects. Before I forget to mention it, MySQL AB is not responsible at all for the contents of this page. And of course, MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries.

Please click here if you want to skip the introductionary blah blah and go directly to list of available UDFs. Or go to project page on sourceforge.net and proceed from there.

Let's assume the following initial situation: you have a MySQL version 4.0 server running and yet no UDFs installed.

Now, try to calculate the median of a column's values with MySQL.
At the moment (that means latest stable MySQL release 4.0.20), the median can be retrieved in MySQL with these workarounds:

• count the number of rows in the table with
  SELECT COUNT(*) FROM {table name}
• if the retrieved number of rows is odd, get the value directly with
  SELECT {column name} FROM {table name} ORDER BY {column name} LIMIT {(total number of rows in table-1)/2},1
• if the number of rows is even, get the two relevant values from the table with
  SELECT {column name} FROM {table name} ORDER BY {column name} LIMIT {(total number of rows in table-1)/2},2
  add them afterwards and divide the results by 2

It seems that the median can not be calculated with a single query with current stable versions of MySQL.

The most preferred solution would be to be able to retrieve the median of a column together with other statistical data in a single query, e. g.:

 SELECT MIN({column name}) AS min, MAX({column name}) AS max, AVG({column name}) AS avg, STDDEV({column name}) AS stddev, COUNT({column name}) AS count, MEDIAN({column name}) AS median FROM {table name}

In fact, you can. If you've read the MySQL manual, you'll know that you can extend the functionality of your MySQL server by supplying UDFs.
So, the only thing to do is to add a median() function to MySQL via a UDF.

There's a section in the MySQL manual named 23.2 Adding New Functions to MySQL that describes the most important things about UDFs. If you have any problems with UDFs, please look into the manual first.

Briefly described, you can write a C module that conforms with the MySQL UDF interface. If you issue a query using a custom function in MySQL, your C module will be used. First, you have to implement some kind of data type checking in your module, so that it ensures your function is called with the right data types (e. g. no STRING instead of a REAL).
After the input data passed your initial checking, a specific function in your module will called by MySQL for each row. In this function, you can do whatever you want with the input data (e. g. adding, sorting, concatenating). After that, you just write back the result. By the way, you can write aggregate and non-aggregate UDFs.

There are only a few steps you have to follow (all examples given are for the median UDF; of course you have to adjust all paths to your specific situation):

• download the UDF's source code
• compile the UDF as a shared object (I assume you have gcc)
  jansen@linux:˜> gcc -Wall -I /home/jansen/mysql-4.0.20/include -I /usr/local/include -c udf_median.cc -o udf_median.o
jansen@linux:˜> ld -shared -o udf_median.so udf_median.o
• put the UDF somewhere where MySQL can find it
  jansen@linux:˜> cp udf_median.so /usr/lib
• run mysql and register the UDF's functions and input data types in MySQL
  jansen@linux:˜> mysql
mysql> CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME 'udf_median.so';

If you want to get rid of a UDF you have registered successfully, simply run mysql and type
mysql> DROP FUNCTION median;

• The functions provided here are considered to be of alpha quality and not fully stable versions. It is possible that some bugs in the functions kill your connection to the MySQL server or even cause it to hang up. You have been warned. Bug reports are welcome.
• If you try to register a function in MySQL that has already been registered, you will get an error message. Drop the existing function first.
• If you don't have permissions to access to .o or .so file, MySQL won't be able to use your UDF. Instead, you will get an error message when registering the UDF in MySQL.
• Important: It seems that MySQL AB changed the UDF API in MySQL version 4.1.1. The UDFs provided here should work with the 4.0-branch of MySQL (at least on my box they do). I am not sure if they will still work with the 4.1.x-branches. Update: it seems that the non-aggregate functions will still work in MySQL 4.1.1+, but the aggregate functions need some additional hook-in to work (the xxx_clear procedure). I will fix this when I have some spare time.

All UDFs provided here are subject to the following licensing conditions:

Copyright (c) 2004, Jan Steemann
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

• Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
• Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
• The names of its contributors must not be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

The following list states which UDFs are available at the moment. The download location for all UDFs is http://prdownloads.sourceforge.net/mysql-udf/mysql-udf-0.3.tar.gz?download.

Function name	median
Purpose	calculate the median of a column
Aggregate function	Yes
Input parameter(s)	1 (column: REAL)
Conditions	-
Output value(s)	1 per group (REAL)
Examples	SELECT MEDIAN(age) FROM bar SELECT gender, MEDIAN(age),AVG(age) FROM bar GROUP BY gender
Registration	CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME 'udf_median.so';

Function name	correlation
Purpose	calculate the correlation coefficient of the linear regression of two sets of variables
Aggregate function	Yes
Input parameter(s)	1 (dependent variable: REAL, independent variable: REAL)
Conditions	-
Output value(s)	1 per group (REAL)
Examples	SELECT correlation(income,age) FROM customers
Registration	CREATE AGGREGATE FUNCTION correlation RETURNS REAL SONAME 'udf_correlation.so';

Function name	intercept
Purpose	calculate the intercept of the linear regression of two sets of variables
Aggregate function	Yes
Input parameter(s)	2 (dependent variable: REAL, independent variable: REAL)
Conditions	-
Output value(s)	1 per group (REAL)
Examples	SELECT intercept(income,age) FROM customers
Registration	CREATE AGGREGATE FUNCTION intercept RETURNS REAL SONAME 'udf_intercept.so';

Function name	slope
Purpose	calculate the slope of the linear regression of two sets of variables
Aggregate function	Yes
Input parameter(s)	2 (dependent variable: REAL, independent variable: REAL)
Conditions	-
Output value(s)	1 per group (REAL)
Examples	SELECT slope(income,age) FROM customers
Registration	CREATE AGGREGATE FUNCTION slope RETURNS REAL SONAME 'udf_slope.so';

Function name	skewness
Purpose	calculate the skewness of a distribution of values
Aggregate function	Yes
Input parameter(s)	1 (data: REAL)
Conditions	-
Output value(s)	1 per group (REAL)
Examples	SELECT skewness(age) FROM customers
Registration	CREATE AGGREGATE FUNCTION skewness RETURNS REAL SONAME 'udf_skewness.so';

Function name	kurtosis
Purpose	calculate the kurtosis of a distribution of values
Aggregate function	Yes
Input parameter(s)	1 (data: REAL)
Conditions	-
Output value(s)	1 per group (REAL)
Examples	SELECT kurtosis(age) FROM customers
Registration	CREATE AGGREGATE FUNCTION kurtosis RETURNS REAL SONAME 'udf_kurtosis.so';

Function name	confidence_higher
Purpose	calculate the upper bound of the confidence interval for a given standard deviation, sample size, mean and confidence probabilty
Aggregate function	No
Input parameter(s)	4 or 5 (confidence probability p: INT or REAL, sample size n: INT, arithmetic mean my: INT or REAL, standard deviation s: INT or REAL, number of decimals: INT, optional)
Conditions	-
Output value(s)	1 per row (REAL)
Examples	SELECT confidence_higher(0.95,2000,550,60,4)
Registration	CREATE FUNCTION confidence_higher RETURNS REAL SONAME 'udf_confidence_higher.so';

Function name	confidence_lower
Purpose	calculate the lower bound of the confidence interval for a given standard deviation, sample size, mean and confidence probabilty
Aggregate function	No
Input parameter(s)	4 or 5 (confidence probability p: INT or REAL, sample size n: INT, arithmetic mean my: INT or REAL, standard deviation s: INT or REAL, number of decimals: INT, optional)
Conditions	-
Output value(s)	1 per row (REAL)
Examples	SELECT confidence_lower(0.95,2000,550,60,4)
Registration	CREATE FUNCTION confidence_lower RETURNS REAL SONAME 'udf_confidence_lower.so';

Function name	stdnorm_density
Purpose	calculate the value of the density function of the standard normal distribution for a given point
Aggregate function	No
Input parameter(s)	1 or 2 (point p: INT or REAL, number of decimals: INT, optional)
Conditions	-
Output value(s)	1 per row (REAL)
Examples	SELECT stdnorm_density(0,4)
Registration	CREATE FUNCTION stdnorm_density RETURNS REAL SONAME 'udf_stdnorm_density.so';

Function name	stdnorm_dist
Purpose	calculate the value of the distribution function of the standard normal distribution for a given point
Aggregate function	No
Input parameter(s)	1 or 2 (point p: INT or REAL, number of decimals: INT, optional)
Conditions	-
Output value(s)	1 per row (REAL)
Examples	SELECT stdnorm_dist(0,4)
Registration	CREATE FUNCTION stdnorm_dist RETURNS REAL SONAME 'udf_stdnorm_dist.so';

Function name	geomean
Purpose	calculate the geometric mean of a column
Aggregate function	Yes
Input parameter(s)	1 (data: REAL)
Conditions	-
Output value(s)	1 per group (REAL)
Examples	SELECT geomean(price) FROM products
Registration	CREATE AGGREGATE FUNCTION geomean RETURNS REAL SONAME 'udf_geomean.so';

Function name	weightedavg
Purpose	calculate the weighted average of a values
Aggregate function	Yes
Input parameter(s)	2 (data: REAL) (weight: REAL)
Conditions	-
Output value(s)	1 per group (REAL)
Examples	SELECT weightedavg(voting,categoryweight) FROM votes
Registration	CREATE AGGREGATE FUNCTION weightedavg RETURNS REAL SONAME 'udf_weightedavg.so';

Function name	noverm
Purpose	calculate the number of possible combinations of size m from a set of n total values (without regarding the order of values in combinations)
Aggregate function	No
Input parameter(s)	2 (n: INTEGER, m: INTEGER)
Conditions	n must be greater than 1, m must be between 1 and m
Output value(s)	1 (REAL)
Examples	SELECT noverm(10,2) SELECT noverm(foo,bar) FROM baz
Registration	CREATE FUNCTION noverm RETURNS INTEGER SONAME 'udf_noverm.so';

Function name	faculty
Purpose	calculate the faculty (n!) of a number (1*2*3*4*5*6...*n)
Aggregate function	No
Input parameter(s)	1 (n: INTEGER)
Conditions	n must be greater than 0
Output value(s)	1 (REAL)
Examples	SELECT faculty(20) SELECT faculty(foo) FROM bar
Registration	CREATE FUNCTION faculty RETURNS REAL SONAME 'udf_faculty.so';

Function name	colwidth
Purpose	calculate the length of the longest value in a STRING column
Aggregate function	Yes
Input parameter(s)	1 (column: STRING)
Conditions	-
Output value(s)	1 per group (INTEGER)
Examples	SELECT COLWIDTH(request_vars) FROM request_log SELECT session.id,COLWIDTH(session_vars.value) FROM session,session_vars WHERE session.id=session_vars.session_id GROUP BY session_id
Registration	CREATE AGGREGATE FUNCTION COLWIDTH RETURNS INTEGER SONAME 'udf_colwidth.so';

Function name	longest
Purpose	get the longest value in a STRING column
Aggregate function	Yes
Input parameter(s)	1 (column: STRING)
Conditions	-
Output value(s)	1 per group (STRING)
Examples	SELECT LONGEST(name) FROM users SELECT LONGEST(request_vars) FROM request_log GROUP BY script_name
Registration	CREATE AGGREGATE FUNCTION LONGEST RETURNS STRING SONAME 'udf_longest.so';