MySQL中的修剪平均数计算

Question

我希望编写一个在MySQL中计算简单修剪平均值的函数。该函数将明显是一个聚合函数。我在MySQL中编写函数等方面还很新，所以需要一些帮助。

修剪平均数的算法如下（伪代码）：

CREATE AGGREGATE FUNCTION trimmed_mean(elements DOUBLE[], trim_size INTEGER)
RETURNS DOUBLE
BEGIN
   -- determine number of elements
   -- ensure that number of elements is greater than 2 * trim_size else return error
   -- order elements in ASC order
   -- chop off smallest trim_size elements and largest trim_size elements
   -- calculate arithmetic average of the remaining elements
   -- return arithmetic average
END

有人可以帮忙正确编写上述函数，以便与MySQL一起使用吗？

- Homunculus Reticulli

---

你想将这个操作作为函数而不是查询，有特定的原因吗？另外，考虑到你之前偏好使用标准SQL的答案，你是否需要在多个不同的关系型数据库管理系统（即不仅限于MySQL）中使用它？ - user359040

@MarkBannister 我本来想使用PG（我最喜欢的数据库！），但是我不得不跨过太多障碍才能让PG与PHP配合工作（重新编译PHP [或类似的疯狂要求]等），所以我选择了已经与PHP配合工作的mySQL。我想将其作为函数的原因是我想将修剪后的平均值作为查询中的一列返回。我想，如果我有一个SQL解决方案，我可以拼凑一些SQL代码将修剪后的平均值作为一列粘贴到我的返回数据集中。 - Homunculus Reticulli

@MarkBannister：回答你的问题，最好使用ANSI SQL版本。但由于我碰巧正在使用MySQL，所以如果我必须要与数据库相关，则MySQL风格的SQL将优先考虑。 - Homunculus Reticulli

我认为这个问题可能会有用：https://dev59.com/VF7Va4cB1Zd3GeqPLqj- - newtover

Answer 1

这可不是小任务，你需要用c/c++来编写它...

• http://dev.mysql.com/doc/refman/5.0/en/adding-udf.html

MySQL内部的一个选项是编写一个视图或标量函数，按照您想要的方式对数据进行聚合，但限定在特定的表中。这显然将函数限制在单个源表上，可能并不理想。

解决办法是专门为这个“函数”创建一个表...

- 开始一个事务 - 清空表 - 插入示例数据 - 查询视图/函数

（或类似的操作）

这将排除GROUP BY变体，除非您使用动态SQL或向函数传递参数以实现特定的分组模式。

总之，这些都不是理想的解决方案，抱歉。

- MatBailie

---

我不介意走C/C++这条路（作为最后的选择）- 但我宁愿不这样做，仅仅是因为我不想花费数小时来熟悉mySQL内部数据类型等。如果有一个用C/C++编写的聚合函数的“hello world”示例，那将是一个非常好的起点（可以减少学习曲线），因为我想做的事情相对较简单（即算法部分）。 - Homunculus Reticulli

在Dems提供的链接中：“MySQL源代码分发包含一个名为sql/udf_example.c的文件，其中定义了5个新的函数。” - ypercubeᵀᴹ

Answer 2

看一下这个例子（针对MySQL）-

创建测试表：

CREATE TABLE test_table (
  id INT(11) NOT NULL AUTO_INCREMENT,
  value INT(11) DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO test_table(value) VALUES 
  (10), (2), (3), (5), (4), (7), (1), (9), (3), (5), (9);

让我们计算平均值（编辑版）：

SET @trim_size = 3;

SELECT AVG(value) avg FROM (
  SELECT value, @pos:=@pos + 1 pos FROM (SELECT * FROM test_table ORDER BY value) t1, (SELECT @pos:=0) t2
  ) t
WHERE pos > @trim_size AND pos <= @pos - @trim_size;

+--------+
| avg    |
+--------+
| 4.8000 |
+--------+

- Devart

---

一定应该是4.8吧——修剪应该消除其中一个3而不是两个？即：（not(1 + 2 + 3) + 3 + 4 + 5 + 5 + 7 + not(9 + 9 + 10)）/ 5 - user359040

@Mark Bannister 您是正确的。我错过了重点 - 按ASC顺序排序元素。它应该按value字段排序。我已编辑查询。谢谢;-) - Devart

Answer 3

我创建了一个UDF，完全匹配此处引用的Excel函数 https://support.office.com/en-us/article/trimmean-function-d90c9878-a119-4746-88fa-63d988f511d3

https://github.com/StirlingMarketingGroup/mysql-trimmean

使用方法

`trimmean` ( `NumberColumn`, double `Percent` [, integer `Decimals` = 4 ] )

• `NumberColumn`

  • 需要进行修剪和计算平均值的数值列。

• `Percent`

  • 排除在计算中的数据点的小数部分。例如，如果 percent = 0.2，则从包含 20 个数据点的数据集中修剪 4 个点 (20 x 0.2)：从数据集的顶部和底部各删除 2 个数据点。

• `Decimals`

  • 可选的输出小数位数。默认值为4。

- Brian Leishman