How do I create a user-defined aggregate function?

Question

I need an aggregate function that MySQL doesn't provide.

I would like it to be in MySQL's flavor of SQL (that is, not in C).

How do I do this? What I'm stuck on is creating an aggregate function -- the docs don't seem to mention how this is done.

Examples of desired usage of a product function:

mysql> select product(col) as a from `table`;
+------+
| a    |
+------+
|  144 |
+------+
1 row in set (0.00 sec)

mysql> select col, product(col) as a from `table` group by col;
+-----+------+
| col | a    |
+-----+------+
|   6 |   36 |
|   4 |    4 |
+-----+------+
2 rows in set (0.01 sec)

Answer 1

According to the documentation http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html it's only possible to write aggregate functions in C. Sorry!

Answer 2

I don't know if there is way to define a new aggregate function, not without messing with MySQL source code.

But if your numbers are all positive, you may well derive from the arithmetic identity:

log( product( Ai ) ) = sum( log( Ai ) )

that you can use EXP(SUM(LOG(x))) to calculate PRODUCT(x). Test in SQL-Fiddle:

SELECT EXP(SUM(LOG(a))) AS product
FROM t ;

SELECT col, EXP(SUM(LOG(a))) AS product
FROM t 
GROUP BY col ;

---

When the data can have 0s, it gets a bit more complicated:

SELECT (NOT EXISTS (SELECT 1 FROM t WHERE a = 0)) 
       * EXP(SUM(LOG(a))) AS p
FROM t 
WHERE a > 0 ;

SELECT d.col, 
       (NOT EXISTS (SELECT 1 FROM t AS ti WHERE ti.col = d.col AND ti.a = 0)) 
       * COALESCE(EXP(SUM(LOG(t.a))),1)  AS p
FROM 
    ( SELECT DISTINCT col
      FROM t
    ) AS d
  LEFT JOIN
    t  ON  t.col = d.col
       AND t.a > 0
GROUP BY d.col ;

Tested at SQL-Fiddle

---

For other DBMS, that do not have MySQL's auto-conversion of boolean values to integers, the

(NOT EXISTS (SELECT ...))

should be replaced with:

(CASE WHEN EXISTS (SELECT 1...) THEN 0 ELSE 1 END) 

Specifically for Oracle, a few more changes will be needed, without changing the logic of the answer, only because Oracle does not follow strict ANSI standard in some areas. Tested at SQL-Fiddle-2

Answer 3

In the interests of learning how to fish, I have successfully compiled and installed a "Hello, World!" UDF (user-defined function) for MySQL found here. The hello_world.so file (after being complied with gcc -shared -o hello_world.so -I /usr/include/mysql hello_world.c) should be stored in /usr/lib/mysql/plugins/ with 755 permissions on Ubuntu linux systems. [The "-I /usr/include/mysql" is the path to the mysql header files; I found my code wouldn't compile without this parameter, but YMMV.]

The program does nothing but print out the string "Hello, World!" for each record in the resulting dataset of a query, but that's all it is supposed to do. I will try to write a SMALL aggregate function in the next few days. There is an example of an aggregate function which computes the average cost of a group of price and quantity records; the SMALL function shouldn't be that different from that function in the end.

Hope this helps.