mysql 分组中位数

查询表中每个userid的loading_time 中位数

最终sql如下

set @ID1 = 0;
set @ID2 = 0;
select t2.userid,
       sum(if(t3.ID2=t2.id25, t3.loading_time, 0)) as t25,
       sum(if(t3.ID2=t2.id50, t3.loading_time, 0)) as t50,
       sum(if(t3.ID2=t2.id75, t3.loading_time, 0)) as t75
from (
    select userid,
     min(t.ID1) + round((max(t.ID1) - min(t.ID1)) / 4, 0) as id25,
     min(t.ID1) + round((max(t.ID1) - min(t.ID1)) / 2, 0) as id50,
     min(t.ID1) + round(3 * (max(t.ID1) - min(t.ID1)) / 4, 0) as id75
    from (
        select @ID1 := @ID1 + 1 as ID1, userid
        from matchinfo
        order by userid, loading_time
    ) as t
    group by userid
) as t2
left join (
    select @ID2 := @ID2 + 1 as ID2, userid, loading_time
    from matchinfo
    order by userid, loading_time
) as t3 on t2.userid=t3.userid and (t2.id25 = t3.ID2 or t2.id50 = t3.ID2 or t2.id75 = t3.ID2)
group by t2.userid;

查询结果

具体思路是

1. 先将数据排序，然后每行添加递增序号ID1
2. 算出每组ID1的中间值，这个中间值所在的行就对应了中位数