hadoop - 如何在Hive中模拟BigQuery的分位数

Question

我想在Hive中模拟BigQuery的QUANTILES函数。

数据集:1,2,3,4

BigQuery的查询结果将返回值2

select nth(2, quantiles(col1, 3))

但是在Hive中:

select percentile(col1, 0.5)

我有2.5

注意:对于奇数个记录，我得到相同的结果。

Hive的udf功能是否足够？

Answer 1

我猜您在寻找的是percentile_approx UDF。

This page为您提供Hive中所有内置UDF的列表。

percentile_approx(DOUBLE col, p [, B])

Returns an approximate pth percentile of a numeric column (including floating point types) in the group. The B parameter controls approximation accuracy at the cost of memory. Higher values yield better approximations, and the default is 10,000. When the number of distinct values in col is smaller than B, this gives an exact percentile value.