8

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)
|improve this question
7

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!

|improve this answer
  • Either C or C++. Not SQL, anyway. – Mike Sherrill 'Cat Recall' Oct 18 '12 at 0:49
  • 1
    I presume any language which can generate binary libraries in the platform-supported binary format with C calling conventions. – Colin 't Hart Oct 18 '12 at 7:30
  • I don't know. It was documented as "C or C++ (or another language that can use C calling conventions)" in version 5.0. The docs dropped "or another language that can use C calling conventions" in version 5.1. That's a strange phrase to drop. – Mike Sherrill 'Cat Recall' Oct 18 '12 at 10:18
  • is it available in recent mysql versions now (after few years) ? – Dinesh Jun 23 '17 at 16:01
9

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

|improve this answer
  • 2
    That's sweet. High school math has come back to haunt me. +1 !!! – RolandoMySQLDBA Apr 30 '12 at 21:49
  • 1
    Cool math, but I actually wanted to know how to create an aggregate function in general. product was just supposed to be one example of several. – Matt Fenwick Apr 30 '12 at 23:01
  • This is pretty cool, but doesn't work if any of the values are zero, since log(0) is undefined. – jameshfisher Apr 30 '14 at 17:30
  • @jameshfisher Correct. One can write easily the extra condition, checking for zeros (where the product would be zero of course). I didn't think at the time it was necessary to add that complication. – ypercubeᵀᴹ Apr 30 '14 at 17:40
  • It's not clear to me how best to add that condition. We can't add the condition in the inner function of the values: since we want that PRODUCT(..., 0, ...) = 0, we want that EXP(SUM(..., f(0), ...)) = 0, for some f that we choose, but to satisfy this, we need that SUM(..., f(0), ...) = LOG(0) -- again thwarted by the same issue that log(0) is undefined. We need to check for the presence of zero in some other way, e.g. MIN(ABS(a)) = 0. So we'd have SELECT CASE WHEN MIN(ABS(a)) = 0 THEN 0 ELSE EXP(SUM(LOG(a))) END AS product. Is this the kind of thing you were thinking of? – jameshfisher Apr 30 '14 at 17:56
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.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.