在mysql中创建一个可与GROUP BY一起使用的用户定义函数

Question

我正试图在MySQL中创建一个聚合函数MEDIAN((，如MIN((、MAX((、AVG((，它接受输入列名或具有所需列的串联值的字符串。

我很难理解MySQL自定义函数的局限性；如果有人能帮我弄清楚这是怎么做到的，那将非常有帮助。

示例：

MySQL表有2列(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 |
+----+--------+

Answer 1

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

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

Answer 2

EDIT：我知道这个答案并不能直接解决这个问题，因为这个问题是"如何在mySQL中创建聚合中值函数"；我的回答特别说明了如何在没有UDF的情况下做到这一点。

然而，公认的答案是，这在mySQL中是不可能的，所以我给出了一个解决方案，可以在不必使用UDF的情况下解决聚合中值能力，以防有人无论如何都想计算聚合中值。

---

没有UDF是可能的，我知道有两种方法。第一种方法使用两个选择和一个连接，第一种选择用于获得值和排名，第二种选择用于获取计数，然后连接它们。第二个使用json函数在一次选择中获取所有内容。它们都有点长，但它们有效，速度也相当快。

解决方案#1(两个选择和一个加入，一个获取计数，一个获得排名(

SELECT  x.group_field, 
avg(
if( 
x.rank - y.vol/2 BETWEEN 0 AND 1, 
value_field, 
null
)
) as median
FROM (
SELECT  group_field, value_field, 
@r:= IF(@current=group_field, @r+1, 1) as rank, 
@current:=group_field
FROM (
SELECT group_field, value_field
FROM table_name
ORDER BY group_field, value_field
) z, (SELECT @r:=0, @current:='') v
) x, (
SELECT group_field, count(*) as vol 
FROM table_name
GROUP BY group_field
) y WHERE x.group_field = y.group_field
GROUP BY x.group_field;

解决方案#2(使用json对象存储计数并避免联接(

SELECT group_field, 
avg(
if(
rank - json_extract(@vols, path)/2 BETWEEN 0 AND 1,
value_field,
null
)
) as median
FROM (
SELECT group_field, value_field, path, 
@rnk := if(@curr = group_field, @rnk+1, 1) as rank,
@vols := json_set(
@vols, 
path, 
coalesce(json_extract(@vols, path), 0) + 1
) as vols,
@curr := group_field
FROM (
SELECT p.group_field, p.value_field, concat('$.', p.group_field) as path
FROM table_name
JOIN (SELECT @curr:='', @rnk:=1, @vols:=json_object()) v
ORDER BY group_field, value_field DESC
) z
) y GROUP BY group_field;