感謝您抽出
.
.
來閱讀本文
一、基本概念
在資料統計分析中，經常聽到一個名詞叫做百分位，在了解百分位之前我們先了解以下幾個概念：
四分位數：将所有數值按某一順序排列并分成四等份，處于三個分割點位置的數值就是四分位數。
中位數：中點位置的四分位數就是中位數。
最大的四分位數稱為上四分位數，所有數值中，有四分之三小于上四分位數，四分之一大于上四分位數。也有叫第25百分位數、第75百分位數的。
總結一句話：
百分位數的定義就是，一組n個觀測值按數值大小排列如 x1,x2,…,xn，處于p%位置的值稱第p百分位數。
計算第p百分位數的步驟：
第1步：以遞增順序排列原始資料(即從小到大排列)。
第2步：計算指數i=np% 。
第3步：
1) 若 i 不是整數，将 i 向上取整。大于i的毗鄰整數即為第p百分位數的位置。
2) 若i是整數，則第p百分位數是第i項與第(i＋1)項資料的平均值。
二、SQL實作
了解過以上幾個概念我們再來看一下具體SQL實作：
2.1 HiveSQL實作
在Hive中有兩個專門計算百分位的函數：percentile(col,p) 函數和 percentile_approx(col,p,B) 函數,其中p∈(0,1)，percentile要求輸入的字段必須是int類型的，而percentile_approx則是數值類似型的都可以。
hive中關于兩個函數的介紹：
percentile介紹：
percentile_approx介紹：
percentile_approx有一個參數B，控制記憶體消耗的近似精度，B越大，結果的準确度越高。預設為10000。當col字段中的distinct值的個數小于B時，結果為準确的百分位數。
如果要求多個分位數，可以把 p 換為 array(p1,p2,p3…p1,p2,p3…)，即
percentile_approx(col,array(0.05,0.5,0.95),9999)
還可以給col再加個類型轉換：
percentile_approx(cast(col as double),array(0.05,0.5,0.95),9999)
具體使用方法如下：
2.2 MySQL實作
mysql中并沒有類似于Hive中的計算百分位的函數，那要怎麼實作呢？
其實mysql中要想實作計算百分位，也沒有那麼的難，隻要定義一個變量就可以了 。
在mysql中建立一張測試表：
CREATE TABLE `student_t` ( `id` VARCHAR(32) NOT NULL COMMENT '學生學号', `score` INT(11) DEFAULT NULL COMMENT '學生得分', PRIMARY KEY (`id`)); INSERT INTO student_t (id,`score`) VALUES ('A',40),('B',50),('C',60),('D',70),('E',80),('F',90);
我們先按照成績正序排序：
SELECTid,@INDEX := @INDEX + 1 AS order_num,`score` FROMstudent_tINNER JOIN ( SELECT @INDEX := 0 ) AS initvar ON 1 = 1 ORDER BY`score`
得到結果：
然後在此基礎上我們來求學生成績的中位數：
需要注意的是，當記錄是奇數時，中位數是中間位置的數；當記錄是偶數時，中位數是中間兩個數的平均，SQL如下：
SELECTGROUP_CONCAT( id ),AVG( `score` ) FROM(#第二層開始SELECT id, @INDEX := @INDEX + 1 AS order_num, `score` FROM student_t INNER JOIN ( SELECT @INDEX := 0 ) AS initvar ON 1 = 1 ORDER BY `score` ) AS t WHEREorder_num = FLOOR( @INDEX / 2+1 ) OR order_num = CEIL( @INDEX / 2 )
得到結果如下：
接下來我們求正序排序一四分位分數：
SELECTGROUP_CONCAT( id ),AVG( `score` ) FROM(SELECT id, @INDEX := @INDEX + 1 AS order_num, `score` FROM student_t INNER JOIN ( SELECT @INDEX := 0 ) AS initvar ON 1 = 1 ORDER BY `score`) AS t WHEREorder_num = FLOOR(( @INDEX+1) /4 )
得到結果如下：
同理求正序排序的三四分位分數：
SELECTGROUP_CONCAT( id ),AVG( `score` ) FROM(SELECTid,@INDEX := @INDEX + 1 AS order_num,`score` FROMstudent_tINNER JOIN ( SELECT @INDEX := 0 ) AS initvar ON 1 = 1 ORDER BY`score`) AS t WHEREorder_num = FLOOR(3*( @INDEX+1) /4 )
得到結果如下：
需要注意的是，同樣的資料按照指定列正序排序和倒序排序時，所得到的P分位數是不一樣的，是以求百分位數的時候要注意列排序的順序。
END
關注我們一起探索大資料