如何计算SQL中的中位数_利用PERCENTILE函数分析数据

顺亿

2026-10-30 • 👁 0人浏览过

MySQL 8.0.13+ 可用 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) OVER() 精确计算中位数，需配合 ORDER BY 且仅支持常量分位点，返回插值结果而非近似值。 MySQL 8.0+ 怎么用

PERCENTILE_CONT

算中位数 MySQL 原生不支持

MEDIAN()

，但 8.0.13 起引入了窗口函数

PERCENTILE_CONT

，能真正按连续分布算中位数。它不是近似值，而是插值结果，比如 [1,3,5,7] 的中位数是 4.0（(3+5)/2），

PERCENTILE_CONT(0.5)

就会返回这个。实操注意三点：

PERCENTILE_CONT

必须配合

OVER()

窗口子句，不能直接写在

SELECT

普通字段里必须指定

ORDER BY

，否则报错

Window 'w' requires an ORDER BY clause

它只支持常量表达式作为分位点，不能写成

PERCENTILE_CONT(@p)

或列名示例：查订单金额中位数

SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) OVER() AS median_amount
FROM orders;

PostgreSQL 用

PERCENTILE_CONT

还是

PERCENTILE_DISC

PostgreSQL 同时提供两个函数：

PERCENTILE_CONT

插值计算（返回浮点），

PERCENTILE_DISC

取实际存在的离散值（返回同类型）。选哪个取决于业务语义。比如用户年龄字段是整数，用

PERCENTILE_DISC

返回的一定是某个真实用户的年龄；而用

PERCENTILE_CONT

可能返回 32.5 —— 这在统计报告里可以接受，但在生成“典型用户画像”时可能不合适。常见误用：忘记加

ORDER BY

，报错

ERROR: PERCENTILE_CONT requires an ORDER BY clause

在聚合查询里混用窗口函数，比如

GROUP BY user_id

同时又用

PERCENTILE_CONT() OVER()

，导致结果行数爆炸对空值没处理，

NULL

会被自动忽略，但若全列是

NULL

，函数返回

NULL

，不是报错 SQLite 和旧版 MySQL 怎么硬算中位数 SQLite 没有分位函数，MySQL 5.7 及更早版本也不支持窗口函数。这时得靠排序 + 行号模拟，核心思路是：取第

FLOOR((n+1)/2)

和

CEIL((n+1)/2)

行的平均值。难点不在逻辑，而在兼容性写法： MySQL 5.7 用变量

@row := @row + 1

排序后编号，但必须确保

ORDER BY

在子查询里完成，否则变量执行顺序不可靠 SQLite 3.25+ 支持

ROW_NUMBER()

，但老版本只能用自连接计数，性能极差，10 万行以上基本卡死所有手写方案都对

NULL

敏感：如果排序字段含

NULL

，不同数据库的

ORDER BY

默认行为不同（MySQL 把

NULL

排最前，PostgreSQL 排最后） MySQL 5.7 安全写法示例（显式排除

NULL

）：

SELECT AVG(amount) AS median
FROM (
  SELECT amount,
         @row := @row + 1 AS row_num
  FROM (SELECT amount FROM orders WHERE amount IS NOT NULL ORDER BY amount) t,
       (SELECT @row := 0) r
) ranked
WHERE row_num IN (FLOOR((@row + 1) / 2), CEIL((@row + 1) / 2));

为什么

PERCENTILE_CONT(0.5)

在某些场景不如手动取中间两行分位函数看着省事，但实际容易掩盖数据分布问题。比如一组数据是 [1,1,1,1,100]，中位数是 1，但

PERCENTILE_CONT(0.5)

也返回 1 —— 这没问题；可如果业务真正关心的是“去掉异常值后的中心趋势”，那中位数本身就不够用。更关键的是性能差异：

PERCENTILE_CONT

内部要对整个分区排序，无法利用索引加速

ORDER BY

（即使字段有索引，窗口函数仍会触发临时排序）大数据量下，手写方案如果能先用

LIMIT

配合子查询缩小范围（比如只取 top 10000），反而更快某些 OLAP 场景需要同时算多个分位点（p10/p50/p90），用一个

PERCENTILE_CONT

数组语法（如 PostgreSQL 的

PERCENTILE_CONT(ARRAY[0.1,0.5,0.9])

）比多次扫描高效，但 MySQL 不支持数组参数真正麻烦的不是语法怎么写，而是你得先确认：这个“中位数”是要进报表、做监控阈值，还是给下游模型当特征？不同用途，对精度、延迟、空值容忍度的要求天差地别。

相关文章