вівторок, 14 лютого 2012 р.

calc median in mysql

Aloha! As you know, MySQL is a very poor rdbms, but cheap. There aren't a lot of build in functions, special for statistic processing...
But imagine, you need to calculate median. It seems very easy, isn't it? Well, I have bad news for you - Mysql doesn't have this aggregation function.
I've googled it and found several solutions, but all of them have a problems with performance and ugly plan... So, I created own stored procedure (inspired by comment to http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html)

CREATE PROCEDURE calcMedian(IN tbl CHAR(64), IN col CHAR(64))
DECLARE counter INT;
SET @counter:= 0;
SET @s1 = CONCAT('select floor(count(',col,')/2) into @counter from ',tbl);
prepare rowsCounterStmt from @s1;
execute rowsCounterStmt;
SET @s2 = CONCAT('select ',col,' from ',tbl,' order by ',col,' asc limit ',@counter,', 1' );
PREPARE stmt FROM @s2;

Another way: you can use User defined function as explained here http://mysql-udf.sourceforge.net/. In fact, it's much more powerful solution, but it's not suitable for all of us

Немає коментарів:

Дописати коментар