MySQL實現中位元演算法

MySQL實現中位元演算法

本次文章目的：

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

本文永久更新連結地址：https://www.bkjia.com/Linux/2018-03/151272.htm