hive分位函数percentile和percentile_approx误区和解决方案
hive分位函数percentile和percentile_approx误区和解决方案
先说结论
percentile和percentile_approx对分位数的计算是不同的！！！
拿中位数来说，
percentile(col, 0.5)，结果和正常理解的中位数相同，即col排序后最中间的一个数（col观察数为奇数时）或者最中间两个数的平均数（col观察数为偶数时）为中位数；
percentile_approx(col, 0.5)，则是按照等频划分的方法来计算中位数的。
分位函数用法
介绍分位函数的用法
整数类型 percentile
percentile(col, p)：col是要计算的列（值必须为整数类型）；参数p取值为0-1。
当需要多个分位数的时候，可以用array数组，格式为：percentile(col, array(p1, p2, …,pn))
浮点或整数类型 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 为浮点数
CREATE TABLE test.test_table ( `col_int` int ,`col_double` double ) 插入数据：9条（奇数条）
insert into 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分位数
-- 查询1 select percentile(t.col_int, 0.5) from test.test_table t ; -- 5 -- 查询2 select percentile_approx(t.col_int, 0.5) from test.test_table t ; -- 4.5 -- 查询3 select 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 计算等频中位数的方法同上。
验证
往表里新增一条数据，把数据集变成偶数列，验证上述解析：
insert into test.test_table values (10, 1.05) 查询结果：符合预期
-- 查询4 select percentile(t.col_int, 0.5) from test.test_table t ; -- 5.5 -- 查询5 select percentile_approx(t.col_int, 0.5) from test.test_table t ; -- 5 -- 查询6 select percentile_approx(t.col_double, 0.5) from test.test_table t ; -- 0.55 提问：如果想对浮点数取中位数而不是等频中位数怎么办？
可以对浮点数列乘以相应的倍数，转化为整数列，使用percentile获得想要的中位数。例如：
-- 查询7 select percentile(int(t.col_double * 100), 0.5) / 100 from test.test_table t ; -- 0.6