今天和大家分享一下用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位数 即为等频中位数。