在 mysql 中创建一个与 GROUP BY 一起使用的用户定义函数 [英] Create a user defined function that works with GROUP BY in mysql

问题描述

我正在尝试在 MySQL 中创建一个聚合函数 MEDIAN()，如 MIN()、MAX()、AVG()，它接受输入的列名或字符串，该列名或字符串连接了所需列的值.

I'm trying to create an aggregated function MEDIAN() in MySQL like MIN(), MAX(), AVG() which takes the input the column name or string that has concatenated values of the desired column.

我无法理解 MySQL 自定义函数的局限性 &如果有人可以帮助我了解这是如何完成的，那将非常有帮助.

I'm having trouble understanding the limitations of MySQL custom functions & would be really helpful if some can help me find out how this is done.

示例:

MySQL 表有 2 列 (ID, num)

MySQL table has 2 columns (ID, num)

+----+-----+
| id | num |
+----+-----+
|  1 |   5 |
|  1 |   6 |
|  1 |   7 |
|  2 |   1 |
|  2 |   3 |
|  2 |   5 |
+----+-----+

---

SELECT id, MEDIAN(num) as median
FROM table
GROUP BY id;

或

SELECT id, MEDIAN(GROUP_CONCAT(num SEPARATOR ',') as median
FROM table
GROUP BY id;

预期输出是

+----+--------+
| id | median |
+----+--------+
|  1 |      6 |
|  2 |      3 |
+----+--------+

推荐答案

MariaDB-10.3.3 中添加了用户定义的聚合存储函数

MySQL 可以执行聚合函数，但在 SQL 中不行.他们需要一个 UDF(共享库实现)

MySQL can do aggregate functions however not in SQL. They need a UDF (shared library implemenation)

这篇关于在 mysql 中创建一个与 GROUP BY 一起使用的用户定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！