Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

2 Answers 2

up vote 3 down vote accepted

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!

share|improve this answer
    
Either C or C++. Not SQL, anyway. –  Mike Sherrill 'Cat Recall' Oct 18 '12 at 0:49
    
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

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

share|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 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 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 at 17:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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