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

精彩链接

评论可见，请评论后查看内容，谢谢！！！
 您阅读本篇文章共花了：