Hive基础知识07-求取中位数
今天和大家分享一下用SQL查询中位数.有数据集如下：
+------+
|midian|
+------+
| 1|
| 2|
| 3|
| 4|
| 5|
| 6|
| 7|
| 8|
| 9|
| 10|
+------+1.中位数定义
中位数（Median）是描述一个数据集中心位置的统计量，它是将数据集从小到大排序后位于中间位置的数值。如果数据集中的元素数量是奇数，那么中位数就是正中间的那个数；如果是偶数，中位数则是中间两个数的平均值。
2.中位数的特性：
抗异常值影响：与平均数（Mean）相比，中位数对异常值（Outliers）不敏感，因为它不受数据集中极端值的影响。这使得中位数在某些情况下是描述数据集中趋势的更好指标。
中位数也有局限性，主要是中位数不能推算总量和结构。
3 中位数计算方法
- 对数据集进行排序。
- 确定数据集的大小（奇数或偶数）。
- 如果是奇数，取中间的数值；如果是偶数，取中间两个数值的平均值。
4.SQL取中位数
这里重点强调一下"a.rn IN (cnt/2,(cnt + 1) / 2, (cnt + 2) / 2)",当cnt是偶数时，三个值中有两个是整数，正好是中间两个值，当cnt是奇数时，只有（cnt+1）/2是整数，正好是中间值
SELECT AVG(midian) FROM (
SELECT a.midian
FROM (
SELECT d.midian, ROW_NUMBER() OVER (order by midian) AS rn, COUNT(*) OVER () AS cnt
FROM t_basic_07 d
) a
WHERE a.rn IN (cnt/2,(cnt + 1) / 2, (cnt + 2) / 2)
) t;执行结果
+-----------+
|avg(midian)|
+-----------+
| 5.5|
+-----------+5.使用hive自带函数percentile()计算
percentile(col, p) OVER ([PARTITION BY ...] ORDER BY ...)col：需要计算分位数的列。p：表示要计算的百分位数值，取值范围为[0, 1]。例如，0.5表示中位数（50%分位数），0.25表示下四分位数（25%分位数），0.75表示上四分位数（75%分位数）。
所以中位数可以直接这样写
select percentile(midian,0.5) as midian from t_basic_07执行结果
+------+
|midian|
+------+
| 5.5|
+------+6.海量数据时可以使用percentile_approx 近似计算
如果数据集非常大，排序可能会非常耗时。在这种情况下，可以使用percentile_approx函数，它提供了一个近似的百分位数计算，通常比percentile函数更快。
percentile_approx(col, p [, B]) OVER ([PARTITION BY ...] ORDER BY ...)col：需要计算分位数的列。p：表示要计算的百分位数值，取值范围为[0, 1]。B：（可选）控制内存消耗的近似精度。默认值为10,000。当col字段中的去重值的个数小于B时，结果为准确的百分位数。
所以中位数也可以这样写
select percentile_approx(midian,0.5) from t_basic_07执行结果
+-----------------------------------------------------+
|percentile_approx(midian, CAST(0.5 AS DOUBLE), 10000)|
+-----------------------------------------------------+
| 5|
+-----------------------------------------------------+问题出现了，为什么使用percentile_approx(col, p [, B]) OVER ([PARTITION BY ...] ORDER BY ...)
col：需要计算分位数的列。p：表示要计算的百分位数值，取值范围为[0, 1]。B：（可选）控制内存消耗的近似精度。默认值为10,000。当col字段中的去重值的个数小于B时，结果为准确的百分位数。
所以中位数也可以这样写
select percentile_approx(midian,0.5) from t_basic_07执行结果
+-----------------------------------------------------+
|percentile_approx(midian, CAST(0.5 AS DOUBLE), 10000)|
+-----------------------------------------------------+
| 5|
+-----------------------------------------------------+问题出现了，为什么使用percentile_approx 和percentile 的结果不一样呢？我们只有10个数字，默认B的参数是10000，肯定是精确求解，为什么还不对呢？这个和percentile_approx 的计算方式有关。
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位数 即为等频中位数。