mysql中位数计算

mysql里面是没有中位数函数可以直接用的，我想了一个巧妙的方法来计算中位数：

select wx.mon,round(avg(lots),4)mid_lots from(
select ma.mon,ma.lots,row_number() over (partition by ma.mon order by ma.lots)rk,
        count(*) over(partition by ma.mon) total,
        cast(count(*) over(partition by ma.mon) as decimal)/2 mid,
        ceil(cast(count(*)over(partition by ma.mon) as decimal)/2) next
       from(
select from_unixtime(tr.close_time,'%Y-%m')mon,user_id,sum(lots)lots
       from pro_crm.trades tr where tr.close_time>0 and tr.cmd in(0,1) and server_id>10  group by 1,2)ma
join (select user_id from pro_crm.activity_bonus ab group by 1
)mb on ma.user_id=mb.user_id order by 1)wx where
 mod(wx.total,2)=0 and wx.rk in(wx.mid,mid+1)
or (mod(wx.total,2)=1 and wx.rk=wx.next)
group by 1

大致方法如下：

(1)利用窗口函数取出所需数据排名 
(2)判断排名奇数还是偶数，如果是奇数，取排名靠中间排名的数值，如果是偶数，取中间2个排名的数字的平均值