查询表中每个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;
查询结果
具体思路是
- 先将数据排序,然后每行添加递增序号ID1
- 算出每组ID1的中间值,这个中间值所在的行就对应了中位数