直接上查询语句:
select avg(a) from (select a,@a:=@a+1 b from t1,(select @a:=0) t2 order by a) t
where b between @a/2 and @a/2+1;
讨论:MySQL本身没有提供中位数函数。网上有许多写法,基本是笛卡尔积与窗口函数两类,但都不是很理想。
造数:
create table t1 (id int primary key, a int);
insert into t1 values (1,10),(2,10),(3,20),(4,21),(5,30),(6,30),(7,30),(8,100);
1. 使用笛卡尔积
select avg(distinct a)
from (select t1.a from t1,t1 t2 group by t1.a
having sum(case when t2.a >= t1.a then 1 else 0 end) >= count(*) / 2.0
and sum(case when t2.a <= t1.a then 1 else 0 end) >= count(*) / 2.0) tmp;
笛卡尔积连接扫描行数指数增长,性能很差。
2. 使用窗口函数
select sum(score) / count(*) as midean
from (
select a score,
row_number() over (order by a desc,id desc) as desc_math,
row_number() over (order by a asc, id asc) as asc_math
from t1
) as order_table
where asc_math in (desc_math, desc_math + 1, desc_math - 1);
3. 使用变量 针对中位数这个需求还是用变量好:只扫一遍表,没有版本限制,写法巨简单,见开头。
三种方法都支持奇数行与偶数行。