hive分位函数percentile和percentile_approx误区和解决方案

hive分位函数percentile和percentile_approx误区和解决方案

先说结论

percentile和percentile_approx对分位数的计算是不同的！！！

拿中位数来说，
percentile(col, 0.5)，结果和正常理解的中位数相同，即col排序后最中间的一个数（col观察数为奇数时）或者最中间两个数的平均数（col观察数为偶数时）为中位数；
percentile_approx(col, 0.5)，则是按照等频划分的方法来计算中位数的。

分位函数用法

介绍分位函数的用法

1. 整数类型 percentile

percentile(col, p)：col是要计算的列（值必须为整数类型）；参数p取值为0-1。

当需要多个分位数的时候，可以用array数组，格式为：percentile(col, array(p1, p2, …,pn))

1. 浮点或整数类型 percentile_approx

percentile_approx(col, p, B)：col是要计算的列（值可以是浮点类型）；参数p取值为0-1；参数B控制内存消耗的近似精度，B越大，结果的精度越高，默认值为10000，当col字段中的distinct值的个数小于B时，结果就为准确的百分位数，可不填。

当需要多个分位数的时候，可以用array数组，格式为：percentile_approx(col, array(p1, p2, …,pn), B)

分位函数用法示例

建表语句：col_int 为整数，col_double 为浮点数

CREATETABLE  test.test_table
(`col_int`int,`col_double`double)

插入数据：9条（奇数条）

insertinto test.test_table values(1,0.15),(2,0.25),(3,0.35),(4,0.45),(5,0.55),(6,0.65),(7,0.75),(8,0.85),(9,0.95)

查询结果：中位数，即50分位数

-- 查询1select percentile(t.col_int,0.5)from test.test_table t ;-- 5-- 查询2select percentile_approx(t.col_int,0.5)from test.test_table t ;-- 4.5-- 查询3select percentile_approx(t.col_double,0.5)from test.test_table t ;-- 0.5

疑问

为什么整数列 col_int 使用 percentile 和 percentile_approx 的结果不一样？

为什么浮点数列 col_double 使用 percentile_approx 的结果是0.5而不是0.55？

解析

对于整数列的中位数：
percentile 在奇数个数值时，排序后，第(n+1)/2位数 即为中位数，所以查询1的结果是5；
percentile 在偶数个数值时，排序后，第n/2位数 和 第n/2+1位数 的平均值即为中位数；
percentile_approx 通过等频率划分来计算中位数，在奇数个数值时，排序后，第1个数的为累积概率1/9，依次第4个数的累积概率为4/9，第5个数的累积概率为5/9，等频率中位数的计算为 (4 x (1/2 - 4/9) + 5 x (5/9 - 1/2) / (5/9 - 4/9) = 4.5 ，化简可以得到 (4+5)/2，即 第(n+1)/2位数 和 第(n-1)/2位数 的平均值为等频中位数；
percentile_approx 在偶数个数值时，排序后，第n/2位数 的累积概率为0.5，故 第n/2位数 即为等频中位数。

对于浮点数列的中位数：
percentile_approx 计算等频中位数的方法同上。

验证

往表里新增一条数据，把数据集变成偶数列，验证上述解析：

insertinto test.test_table values(10,1.05)

查询结果：符合预期

-- 查询4select percentile(t.col_int,0.5)from test.test_table t ;-- 5.5-- 查询5select percentile_approx(t.col_int,0.5)from test.test_table t ;-- 5-- 查询6select percentile_approx(t.col_double,0.5)from test.test_table t ;-- 0.55

提问：如果想对浮点数取中位数而不是等频中位数怎么办？

可以对浮点数列乘以相应的倍数，转化为整数列，使用percentile获得想要的中位数。例如：

-- 查询7select percentile(int(t.col_double *100),0.5)/100from test.test_table t ;-- 0.6