Trimmed average calculation in MySQL

Question

I want to write a function that calculates a simple reduced average calculation in MySQL. A function will (obviously) be an aggregate function. I am new to writing functions, etc. In MySQL, so could with some help.

The cropped average algorithm will be as follows (pseudo-code):

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

Can someone help with how to correctly write the function above for use with MySQL?

Answer 1

This is a small task, you need to write it in c / C ++ ...

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

MySQL , , , . , , , .

, ...

• /

( - )

GROUP BY, sql pass .

, , .

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);

avg ( ):

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 |
+--------+