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)
查询结果:符合预期文章来源:https://www.toymoban.com/news/detail-752847.html
-- 查询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获得想要的中位数。例如:文章来源地址https://www.toymoban.com/news/detail-752847.html
-- 查询7
select percentile(int(t.col_double * 100), 0.5) / 100 from test.test_table t ; -- 0.6
到了这里,关于hive分位函数percentile和percentile_approx误区和解决方案的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!