SQL 如何实现分位数统计？-SQL-PHP中文网

PostgreSQL用percentile_cont做线性插值求连续分位数，须配合WITHIN GROUP(ORDER BY...)使用；MySQL 8.0+用PERCENT_RANK()窗口函数逼近；SQL Server 2012+支持同名函数，NTILE仅分组不返回分位值；通用方案可用ROW_NUMBER()模拟。

sql 如何实现分位数统计？

PostgreSQL 用 percentile_cont 计算连续分位数

PostgreSQL 原生支持分位数，percentile_cont 是最常用的选择，它做线性插值，返回数据分布中「理论上的」某分位点值（比如中位数就是 0.5）。注意它必须搭配 ORDER BY 和聚合一起用，不能直接写在 SELECT 列表里对每行计算。

常见错误是漏掉 WITHIN GROUP (ORDER BY ...) 子句，否则会报错 ERROR: aggregate function calls cannot contain nested aggregate function calls 或提示缺少排序上下文。

percentile_cont(0.5) WITHIN GROUP (ORDER BY score) → 中位数
percentile_cont(ARRAY[0.25,0.5,0.75]) WITHIN GROUP (ORDER BY score) → 一次性返回四分位数组（结果为数组）
若字段含 NULL，默认被忽略；想保留需提前用 COALESCE 或 FILTER (WHERE ...) 控制
性能上，大数据量时该函数会触发排序，没有索引加速，建议对高频查询字段建函数索引：CREATE INDEX idx_score_percentile ON table_name ((score));

MySQL 8.0+ 用 PERCENT_RANK() + 窗口函数模拟

MySQL 没有内置分位数聚合函数，但 8.0+ 支持窗口函数，可以用 PERCENT_RANK() 找出最接近目标分位的那条记录。关键思路是：先按目标列排序并算出每行的相对排名百分比，再用 ABS(PERCENT_RANK() - 0.9) 找离 0.9 最近的行。

这本质是「取样逼近」，不是严格插值，当数据量少或分布不均时，结果可能和统计学定义有偏差。

查 90% 分位数示例：

SELECT score
FROM (
  SELECT score, ABS(PERCENT_RANK() OVER (ORDER BY score) - 0.9) AS dist
  FROM t
) t1
ORDER BY dist
LIMIT 1;

要支持多分位（如 25/50/75），得用多次子查询或 CTE，没法像 PostgreSQL 那样一行写出
PERCENT_RANK() 的范围是 [0, 1)，首行永远是 0，末行是 1 - 1/(行数)，所以严格 1.0 分位无法命中

SQL Server 用 PERCENTILE_CONT 还是 NTILE？

SQL Server 同样提供 PERCENTILE_CONT，行为和 PostgreSQL 一致，但要注意版本 —— 必须是 2012 及以上，且只支持在聚合上下文中使用（即配合 GROUP BY 或全表聚合）。

GateCrash Forms

以 CLI 为先的表单构建器，采用 BYOK 理念。通过JSON Schema 生成美观的 HTML 表单，使用您自己的 SMTP 服务器处理提交，存储响应...

下载

另一个常被误用的是 NTILE(n)：它只是把结果集「强行等分」成 n 组，每组编号 1~n，并不保证每组数据量完全相等（因总数未必整除），更不返回分位数值本身。想从中推导分位点，得额外取各组边界值，容易出错。

正确用法：PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) OVER () → 全表 75% 分位数
错误典型：SELECT *, NTILE(4) OVER (ORDER BY amount) FROM t → 得到的是分组编号，不是 25%、50% 的具体数值
如果表有 GROUP BY，PERCENTILE_CONT 必须放在同一层级聚合中，不能混用未聚合列

通用替代方案：用 ROW_NUMBER 模拟分位点（兼容旧版数据库）

几乎所有 SQL 引擎都支持 ROW_NUMBER()，所以一个跨库可用的底线方案是：先排序编号，再按总行数比例算出目标位置，最后用 MIN()/MAX() 或自连接取值。适用于 MySQL 5.7、SQLite、旧版 SQL Server 等不支持窗口聚合的环境。

缺点是逻辑冗长、易错，且对重复值敏感——如果大量相同值挤在分位附近，ROW_NUMBER 的任意排序可能导致结果抖动。

假设求中位数：

SELECT AVG(score * 1.0)
FROM (
  SELECT score,
         ROW_NUMBER() OVER (ORDER BY score) AS rn,
         COUNT(*) OVER() AS cnt
  FROM t
) t1
WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0));