感谢您抽出
.
.
来阅读本文
一、基本概念
在数据统计分析中，经常听到一个名词叫做百分位，在了解百分位之前我们先了解以下几个概念：
四分位数：将所有数值按某一顺序排列并分成四等份，处于三个分割点位置的数值就是四分位数。
中位数：中点位置的四分位数就是中位数。
最大的四分位数称为上四分位数，所有数值中，有四分之三小于上四分位数，四分之一大于上四分位数。也有叫第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
关注我们一起探索大数据