MySQL 函数之求取一个表中的某个字段的中位数

CREATE DEFINER=`root`@`%` FUNCTION `get_median_from_communityhistoryprice`() RETURNS float
BEGIN
declare return_value float;
declare median int;
declare median2 int;
declare median_even_1 int;
declare median_even_2 int;
declare temp_1 int;
SELECT count(*) into median from communityhistoryprice;
-- 如果是奇数个数量
if median % 2 = 1 then
select FLOOR(median / 2) into median2;
SELECT price into return_value from communityhistoryprice ORDER BY price asc limit median2,1;
-- 如果是偶数个数量
ELSE
select (FLOOR(median / 2) -1) into median2;
select FLOOR(median / 2) into temp_1;
SELECT price into median_even_1 from communityhistoryprice ORDER BY price asc limit median2,1;
SELECT price into median_even_2 from communityhistoryprice ORDER BY price asc limit temp_1,1;
set return_value = (median_even_1 + median_even_2)/2;
END IF;
RETURN return_value;
END

转载于:https://www.cnblogs.com/ZhangYaF/p/11165893.html