MySQL如何计算中位数！

转自：http://blog.sina.com.cn/jjbenben

前2天，老板有个报表需求，需要用到中位数算法。而MYSQL没有直接提供median()这样的直接算中位数的算法。于是就在百度上百度了下。看到有为朋友提供了一种算法。但是只要代码，没有解释。花了挺久的时间，终于理解了这种算法的含义。

源代码如下：

create table state_mid
as

select user_id,avg(price)
from (
select e.user_id, e.price
from producte e, producte d
where e.user_id = d.user_id
group by e.user_id, e.price
having sum(case when e.price = d.price then 1 else 0 end)>= abs(sum(sign(e.price - d.price)))
)t
group by user_id。

首先，向写出这位代码的前辈致敬。

这段代码的精髓地方，就在于

having sum(case when e.price = d.price then 1 else 0 end)>= abs(sum(sign(e.price - d.price)))

理解这段代码，其实就是理解了中位数的一个体征：

1、当一列数列的数量N是奇数的时候。则中位数的那个数字在数列中的数量>=中位数减去所有数字的结果的符号值(1,0，-1中的一个)的和的绝对值。

简单的例子：

例：数列为1,2,3,4,5

可以看出它的中位数为3，它的数量是1.所以 having sum(case when e.price = d.price then 1 else 0 end)=1。

而它减去所以数字的差值为2,1,0，-1，-2.符号值就为1,1,0，-1，-1.符号值的和为1+1+0+(-1)+ (-1)=0.所以绝对值也为0.所以abs(sum(sign(e.price - d.price)))=0

因此 having sum(case when e.price = d.price then 1 else 0 end)>= abs(sum(sign(e.price - d.price)))成立。

接下来我们看下如果是2的话，会不会条件成立。首先2的数量是1，它与所有数字的差值为 1,0，-1，-2，-3，符号值为1,0,-1,-1,-1。和为-2，绝对值就为2.可以看出，这就和我们要求的条件不符合，就会被剔除。

其他种数列的话大家也可以试试，这里举一个最简单的例子用于方便理解

2、当一列数列的数量N是偶数的时候。这时候用条件筛选出来的就会是最靠近中位数的那两个数字。则为最靠近中位数的那两个数字在数列中的数量>=那两个数字减去所有数字的结果的符号值(1,0，-1中的一个)的和的绝对值。ps:我们都知道，当数列数量N为偶数的时候，中位数就是用最靠近中位数的那两个数字求平均值得来的。

所以 select user_id,avg(price)
from producte e, producte d

where e.user_id = d.user_id
group by e.user_id, e.price
having sum(case when e.price = d.price then 1 else 0 end)>= abs(sum(sign(e.price - d.price)))

就是帮我们找出了最中间的那一个数字(N为奇数的时候)或者两个数字(N为偶数的时候)。

最后在求出这一个数字或者两个数字的平均值，就是我们所要求的中位数了。

已经试用此算法，比较快。

第二种方法

安装并使用这个mysql统计函数：[http://www.xarg.org/2012/07/statistical-functions-in-mysql/]

之后，计算中位数很容易：

SELECT median（x）FROM t1

第三种方法

mysql-求中位数方法

mysql中并没有直接求中位数的函数，经过一番查找之后，找到了如下方法：

SELECT ((SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(URINEIODINE order by URINEIODINE), ',', floor(1+((count(URINEIODINE)-1) / 2))), ',', -1)) + (SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(URINEIODINE order by URINEIODINE), ',', ceiling(1+((count(URINEIODINE)-1) / 2))), ',', -1)))/2 as median FROM bs_lowiodinergn_chld_con_srvy t

但是此方法并不是函数，使用起来有诸多限制。

SELECT ((SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(URINEIODINE order by URINEIODINE), ',', floor(1+((count(URINEIODINE)-1) / 2))), ',', -1))+(SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(URINEIODINE order by URINEIODINE), ',', ceiling(1+((count(URINEIODINE)-1) / 2))), ',', -1)))/2as medianFROM bs_lowiodinergn_chld_con_srvy t

小琥用的第二种方法。

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！

更多技巧， www.excelbook.cn

欢迎加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；