hive分位函數percentile和percentile_approx誤區(qū)和解決方案

先說結論

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))

2. 浮點或整數類型 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獲得想要的中位數。例如：文章來源地址http://www.zghlxwxcb.cn/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誤區(qū)和解決方案的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！