主页 / dba / 问题 / 17267
Accepted
Matt Fenwick
Asked: 2012-05-01 12:22:18 +0800 CST

如何创建用户定义的聚合函数？

  • 772

我需要一个 MySQL 不提供的聚合函数。

我希望它具有 MySQL 的 SQL 风格（也就是说，不是 C 语言）。

我该怎么做呢？我坚持的是创建一个聚合函数——文档似乎没有提到这是如何完成的。

函数的期望用法示例product：

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)
mysql aggregate

4 个回答

  1. 我不知道是否有办法定义一个新的聚合函数，而不是弄乱 MySQL 源代码。

    但如果你的数字都是正数，你很可能从算术恒等式中推导出来：

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

    可以EXP(SUM(LOG(x)))用来计算PRODUCT(x)。在SQL-Fiddle中测试：

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

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

    当数据可以有 0 时，它会变得有点复杂：

    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 ;

    在SQL-Fiddle测试

    对于其他没有 MySQL 将布尔值自动转换为整数的 DBMS，

    (NOT EXISTS (SELECT ...))

    应替换为：

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

    特别是对于Oracle，需要再做一些更改，而不改变答案的逻辑，只是因为Oracle在某些方面没有遵循严格的ANSI标准。在SQL-Fiddle-2测试

    • 11
  2. Best Answer

    根据文档http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html只能用 C 编写聚合函数。对不起！

    • 8

  3. 出于学习钓鱼的兴趣，我已经成功编译并安装了一个“Hello, World!”。在这里找到MySQL 的 UDF（用户定义函数）。hello_world.so 文件（在遵守之后gcc -shared -o hello_world.so -I /usr/include/mysql hello_world.c）应该存储在 /usr/lib/mysql/plugins/ 中，在 Ubuntu linux 系统上具有 755 权限。[“-I /usr/include/mysql”是mysql头文件的路径；我发现如果没有这个参数，我的代码将无法编译，但是 YMMV。]

    程序什么也不做，只是打印出字符串“Hello, World!”。对于查询的结果数据集中的每条记录，但这就是它应该做的一切。在接下来的几天里，我将尝试编写一个 SMALL 聚合函数。有一个聚合函数的例子，它计算一组价格和数量记录的平均成本；SMALL 函数最终不应该与那个函数有那么大的不同。

    希望这可以帮助。

    • 5

  4. 从技术上讲，它不是用户定义的聚合函数，也不是最优的，但这可以工作并且不需要创建 UDF：GROUP_CONCAT()在您的查询中使用这将使您的查询成为一个聚合函数，并将所有值合并到一个逗号分隔的字符串中，并且然后编写一个处理该字符串的 SQL 函数。

    示例（基于此答案的功能）::

    DELIMITER $$
CREATE FUNCTION `replace_multiple`(_text text, _from text, _to text) RETURNS text CHARSET utf8mb4
    DETERMINISTIC
BEGIN

DECLARE _nextfrom TEXT DEFAULT NULL;
DECLARE _nextto TEXT DEFAULT NULL;
DECLARE _nextlenfrom INT DEFAULT NULL;
DECLARE _nextlento INT DEFAULT NULL;
DECLARE _valuefrom TEXT DEFAULT NULL;
DECLARE _valueto TEXT DEFAULT NULL;

iterator:
LOOP
  -- exit the loop if the list seems empty or was null;
  -- this extra caution is necessary to avoid an endless loop in the proc.
  IF CHAR_LENGTH(TRIM(_from)) = 0 OR _from IS NULL THEN
    LEAVE iterator;
  END IF;

  -- capture the next value from the list
  SET _nextfrom = SUBSTRING_INDEX(_from,',',1);
  SET _nextto = SUBSTRING_INDEX(_to,',',1);

  -- save the length of the captured value; we will need to remove this
  -- many characters + 1 from the beginning of the string
  -- before the next iteration
  SET _nextlenfrom = CHAR_LENGTH(_nextfrom);
  SET _nextlento = CHAR_LENGTH(_nextto);

  -- trim the value of leading and trailing spaces, in case of sloppy CSV strings
  SET _valuefrom = TRIM(_nextfrom);
  SET _valueto = TRIM(_nextto);

  -- use the extracted value
  SET _text = replace(_text, _valuefrom, _valueto);

  -- rewrite the original string using the `INSERT()` string function,
  -- args are original string, start position, how many characters to remove,
  -- and what to "insert" in their place (in this case, we "insert"
  -- an empty string, which removes _nextlen + 1 characters)
  SET _from = INSERT(_from,1,_nextlenfrom + 1,'');
  SET _to = INSERT(_to,1,_nextlento + 1,'');
END LOOP;

RETURN _text;
END$$
DELIMITER ;

    药片：

    ID 由此 到那个
    1 1 一
    2 2 二
    3 3 三

    询问：

    SELECT replace_multiple('my 2 cents', GROUP_CONCAT(fromThis), GROUP_CONCAT(toThat))
FROM t;

    将翻译为：

    SELECT replace_multiple('my 2 cents', '1,2,3', 'one,two,three')

    对于这个特定的自定义函数将返回哪个my two cents

    • 0

相关问题