MySQL实现中位数算法

共计 6581 个字符，预计需要花费 17 分钟才能阅读完成。

本次文章目的：

MySQL 并没有专门的中位数算法，而对于 SQL 不熟悉的人，书写中位数，只能通过 Java 等语言实现。并非推荐使用 MySQL 完成中位数计算，以下实现，仅为了通过算法解析的过程中，了解一些 MySQL 常用与不常用的功能、函数，并开拓思维。

当然，对于一些临时性的要求，需要制作一些临时性的算法测试、校验、导出，能使用 MySQL 完成这类算法，就凸显出其效率。

说到中位数，我们就需要一批数据来进行测试和实现，创建如下表：

DROP TABLE IF EXISTS CaseRent;

CREATE TABLE CaseRent(

ID int(6) NOT NULL AUTO_INCREMENT,

ResidentialAreaID int(6) DEFAULT NULL,

CaseFrom varchar(30) DEFAULT NULL,

Price int(6) DEFAULT NULL,

PRIMARY KEY (ID)

);

称之为出租案例表，关键字段有：小区 ID、案例来源及价格。

接下来通过随机数来给出租案例表赋值：

INSERT INTO CaseRent (ResidentialAreaID, CaseFrom ,Price)

SELECT ROUND((RAND()*100)+1),’ 链家在线 ’,ROUND((RAND()*8000)+1000)

该语句包含知识点如下：
1. 通过 INSERT INTO … SELECT 进行赋值（用途广泛，创建表亦可以使用）
2. 运用 Rand() 随机数函数，ROUND() 四舍五入函数，完成小区 ID 从 0~100，价格从 1000~9000 的随机录入。

一条数据当然不够，我们可以使劲的多点几下执行，使数据增加到近 10 条。这时候我们修改一下赋值语句

INSERT INTO CaseRent (ResidentialAreaID, CaseFrom ,Price)

SELECT ROUND((RAND()*100)+1),’ 链家在线 ’,ROUND((RAND()*8000)+1000) FROM CaseRent

继续反复来 N 下，之后将来源“链家在线”修改为“房天下”，进行一次赋值。

INSERT INTO CaseRent (ResidentialAreaID, CaseFrom ,Price)

SELECT ROUND((RAND()*100)+1),’ 房天下 ’,ROUND((RAND()*8000)+1000) FROM CaseRent

模拟数据到此完成！示例如下：

实际上，网上的中位数花式百出，但无一不是：代码篇幅长、需要自我关联 或者 使用上临时变量。

当然也有类似我们接下来要讲的方式。无论哪种方式，都需要更多的了解和扩展自己所知。

接下来以刚才我们自定义的模拟数据为例子，安排第一个问题：
1. 查找小区 ID = 99 的价格中位数

这类的中位数，可以说是最简单的，而且网上大部分中位数，均针对此类中位数（单条件），从上述网站就可以看到，其问题与我们的类似，但其代码量可谓不少。

我们来分析问题：其获取价格中位数，就必须使用 ORDER BY 来实现排序，排序后，统计总条数，来获取中间一条的价格作为结果（如果为偶数，可以取 2 条均值，亦可以取前一条 例如 6 条数据，可以取第 3、4 条进行均值计算，这里以取前一条为算法模拟）

那么第一步，无疑是要进行价格从小到大的排序：

SELECT * FROM CaseRent WHERE ResidentialAreaID = 99 ORDER BY Price

排序之后，ID 显的杂乱无章，关如此，我们人为的话，只能去手动数条数进行查找，因此我们需要拥有一个新的自增 ID，以此来更快的得知其对应的排名。

如何得到新的自增 ID 呢？我们可以新建一张表，通过 INSERT INTO …SELECT 来完成新数据的录入，以此达到数据的 ID 自增：例如：

INSERT INTO NewCaseRent(ResidentialAreaID,CaseFrom,Price)

SELECT ResidentialAreaID,CaseFrom,Price FROM CaseRent WHERE ResidentialAreaID = 99 ORDER BY Price

不过这样我们就需要建表了，这就显的很麻烦，因为一个自增，而新建一张表，入不敷出，

那么我们就需要一个变量，来实现自增功能。

同 JAVA/Python 等开发语言一样，Mysql 也有变量，通常以 @开头为用户自定义变量，以 @@开头为系统变量。

那么我们怎么使用变量？很简单，通过 SET 创建并赋值变量值，再通过 SELECT 查询结果，例如：

SET @ID = 0;

SELECT @ID;

有了变量，我们可以将变量作为新的自增 ID，来代替创建一张新表的操作了，

通过变量自加操作，完成新的自增 ID 功能：

SET @ID = 0;

SELECT @ID:=@ID+1 AS ID,ResidentialAreaID,CaseFrom,Price FROM CaseRent WHERE ResidentialAreaID = 99 ORDER BY Price

注意几点:
1. 在 SELECT 中，给临时变量赋值，使用 :=
2. 每条语句，从底层讲，都是循环查询，因此在语句上直接自增，就可以实现逐条累加。

当然，上面的语句其实是 2 条语句，这样放到 JAVA 或者其他语言中执行，可能不方便，因此也可以修改成如下语句：

SELECT @ID:=@ID+1 AS ID,ResidentialAreaID,CaseFrom,Price FROM CaseRent,(SELECT @ID:=0) b

WHERE ResidentialAreaID = 99 ORDER BY Price

结果示例：

效果很好，接下来我们要做的，就是获取 ID= 总条数 /2 的那条数据了。

思考一下，如何才能简单的得到结果？

SELECT * FROM (

SELECT @ID:=@ID+1 AS ID,ResidentialAreaID,CaseFrom,Price FROM CaseRent,(SELECT @ID:=0) b

WHERE ResidentialAreaID = 99 ORDER BY Price ) a WHERE ID = @ID/2

通过简单的中位数选取，深刻认知 Mysql 临时变量的用法。

接下来引入加深层次的中位数：
1. 根据案例来源，分别统计不同来源，小区 ID=99 的中位数。

分析问题：比第一步多了一个条件，其结果也多了一条数据。

那么该怎么做呢？

我们知道，排序的时候，需要按照 案例来源、价格 2 个条件进行排序了，如果直接自增 ID，会是什么样的呢？

SELECT @ID:=@ID+1 AS ID,ResidentialAreaID,CaseFrom,Price FROM CaseRent,(SELECT @ID:=0) b

WHERE ResidentialAreaID = 99 ORDER BY CaseFrom,Price

很明显，如果想要实现真确的自增 ID，到了链家在线这一步，ID 需要重新从 1 开始计算。

那么难道我们分成 2 次统计？如果案例来源有 N 个，这个方式明显不行。

接下来引入 Mysql 函数  IF

IF (条件 , 真 , 假)

为什么引入 IF？我们需要判断排序后自增的时候，案例来源是否和上次的一样，如果不一样 说明切换到了新来源，这时候将 @ID 设置为从 1 开始，就可以实现 2 个来源不同的自增 ID。

要判断来源是否一样，我们还得加个临时变量 @CaseFrom

复制代码
SET @ID:=0,@CaseFrom=”;

SELECT IF(@CaseFrom!=CaseFrom,@ID:=1,@ID:=@ID+1) AS ID,ResidentialAreaID,CaseFrom,Price,

@CaseFrom:=CaseFrom wy

FROM CaseRent WHERE ResidentialAreaID = 99 ORDER BY CaseFrom,Price;

这里的 wy 字段，就纯粹是为了赋值 CaseFrom。对其他操作无用。

结果如下：

但是问题来了。@ID 已经不能直接用来 判断 Count(*)/ 2 了。因为 @ID 已经是链家在线的 ID，而不是房天下的。

通过创建临时表：临时完美通俗的解决该问题：

临时表 Temporary 只在当前会话使用，其余会话创建相同名称临时表，不互相冲突，不直接生成实体表。

但临时表不能自我关联。

复制代码
SET @ID:=0,@CaseFrom=”;

DROP TABLE IF EXISTS CS_1;

CREATE TEMPORARY TABLE CS_1

SELECT IF(@CaseFrom!=CaseFrom,@ID:=1,@ID:=@ID+1) AS ID,ResidentialAreaID,CaseFrom,Price,@CaseFrom:=CaseFrom wy

FROM CaseRent WHERE ResidentialAreaID = 99 ORDER BY CaseFrom,Price;

DROP TABLE IF EXISTS CS_2;

CREATE TEMPORARY TABLE CS_2

SELECT CaseFrom,FLOOR(Max(ID)/2) CenterID FROM CS_1 GROUP BY CaseFrom;

SELECT * FROM CS_1 a INNER JOIN CS_2 b ON a.ID = b.CenterID AND a.CaseFrom=b.CaseFrom;

这就显的拖沓了，写了这么多代码，创建了 2 张临时表，关联后获取结果。不过只是相对而言，对于一些临时性的操作，计算、导出的时候，就算是 python 编写个脚本，其代码量也远远大于这些。

上述方式，通过临时表 +  IF 的方式，实现了多层次的中位数获取。但是我们知道，通过 IF 判断，意味着我如果添加新的层次，例如：
1. 获取每一个小区、每一个来源的中位数。

这样我们就得增加一个小区 ID 的临时变量，不仅案例来源改变，需要重置 ID 为 1，小区 ID 改变时，也要重置为 1，这样的代码如下：

复制代码
SET @ID:=0,@CaseFrom=”,@ResidentialAreaID=0;

DROP TABLE IF EXISTS CS_1;

CREATE TEMPORARY TABLE CS_1

SELECT IF(@CaseFrom!=CaseFrom,@ID:=1,@ID:=@ID+1) AS ID,

IF(@ResidentialAreaID!=ResidentialAreaID,@ID:=1,1) AS ID2,

ResidentialAreaID,CaseFrom,Price,@CaseFrom:=CaseFrom wy,@ResidentialAreaID:=ResidentialAreaID wy2

FROM CaseRent ORDER BY ResidentialAreaID,CaseFrom,Price;

DROP TABLE IF EXISTS CS_2;

CREATE TEMPORARY TABLE CS_2

SELECT ResidentialAreaID,CaseFrom,FLOOR(Max(ID)/2) CenterID FROM CS_1 GROUP BY ResidentialAreaID,CaseFrom;

SELECT * FROM CS_1 a INNER JOIN CS_2 b ON a.ID = b.CenterID AND a.CaseFrom=b.CaseFrom

AND a.ResidentialAreaID=b.ResidentialAreaID;

多了一个 IF 判断，多了一个临时变量，多关联了一个字段。

这对熟悉并了解该逻辑的人来说并没有增加多少代码量，但其多了一层逻辑，需要了解，这就可能照成混淆。

看上去很多，其实相较于其他方式，已经很精简了，不过还没完，我们还有很多方法可以尝试！

例如编写 Mysql 自定义函数、存储过程来实现，不过这就有点偏离了。

接下来换一种方式实现。

通过 GROUP_CONCAT 和 SUBSTRING_INDEX 实现中位数算法

Group_concat 一般不会太陌生，一般伴随着 Group By 使用，当然也可以不实用 Group by

通过 Group_concat 可以将结果字段 默认通过 逗号 分割，组成一个新的字符串。

例如：

SELECT GROUP_CONCAT(Price) FROM CaseRent WHERE ResidentialAreaID = 99;

其结果如下：

而 GROUP_CONCAT 中，还可以写一些 SQL 代码。例如

GROUP_CONCAT(Price ORDER BY Price)

或者：

GROUP_CONCAT(DISTINCT Price)

是不是很方便，可以自行排序、剔除重复等操作，组成一个新的字符串。

再介绍另一个函数：SUBSTRING_INDEX

先看一下结果：

SELECT SUBSTRING_INDEX(‘ 一批, 数, 据 ’,’,’,1)

= 一批

SELECT SUBSTRING_INDEX(‘ 一批, 数, 据 ’,’,’,2)

= 一批, 数

SELECT SUBSTRING_INDEX(‘ 一批, 数, 据 ’,’,’,3)

= 一批, 数, 据

很明确了，第一个参数放字符串，第二个为分割字符，第三个为取到第几个字符。

那就再说一个  -1，-1 很常见，Redis、python 中  分割、查找字符经常使用，意为反向取值，例如：

SELECT SUBSTRING_INDEX(‘ 一批, 数, 据 ’,’,’,-1)

= 据

结合这两种函数的特性，就能完成中位数获取了。

我们来看一下：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(Price ORDER BY Price),’,’,Count(1)/2),’,’,-1) zws

FROM CaseRent WHERE ResidentialAreaID = 99;

以上涉及了 2 个函数，SUBSTRING_INDEX 以及 GROUP_CONCAT，

通过 GROUP_CONCAT 将结果排序后组成逗号分割的新字符串，并通过 SUBSTRING_INDEX, 获取到总量 / 2 的结果，再通过 SUBSTIRNG_INDEX - 1 的获取倒数第一个值，即为中位数结果。

那么如果加上案例来源获取中位数，这代码会变成什么样？

SELECT CaseFrom,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(Price ORDER BY Price),’,’,Count(1)/2),’,’,-1) zws

FROM CaseRent WHERE ResidentialAreaID = 99 Group By CaseFrom;

再加上区分小区呢？：

SELECT ResidentialAreaID,CaseFrom,

SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(Price ORDER BY Price),’,’,Count(1)/2),’,’,-1) zws

FROM CaseRent Group By ResidentialAreaID,CaseFrom;

似乎很简单，但是 GROUP_CONCAT 有个默认承载长度 1024

如果不修改参数的情况下，做大量数据的中位数统计，会超出 GROUP_CONCAT 的承载长度，导致计算错误。

而一般情况下，我们无法修改服务器的 Mysql 配参，可以通过：

show variables like ‘group_concat_max_len’

来参考当前参数。

以及：

— 以当前会话，临时修改 GROUP_CONCAT 支撑长度。

SET @@GROUP_CONCAT_MAX_LEN = 1024000;

当然，如果有必要，可以直接通知运维修改一下参数长度，如果不常用，可以自行使用这种方式修改后临时使用；因此数据量大的情况下，正确的写法如下：

复制代码
SET @@GROUP_CONCAT_MAX_LEN = 1024000;

SELECT ResidentialAreaID,CaseFrom,

SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(Price ORDER BY Price),’,’,Count(1)/2),’,’,-1) zws

FROM CaseRent Group By ResidentialAreaID,CaseFrom;

到此，中位数算法结束。

主要知识点：

临时变量

临时表

系统变量

IF

GROUP_CONCAT

SUBSTRING_INDEX