★ FEATURED ARTICLE 做数据分析这些年“SQL 求分位数”是我被问到频率最高的操作之一。业务方要算订单的 P90 金额、接口响应的 P99 耗时、部门薪酬中位数、用户活跃时长水位这些指标背后全是同一个问题怎么用 SQL 把一组数据按位置切开。分位数不像 sum、avg 那样在任意数据库里都能一句话写完不同数据库、不同函数版本、不同排序规则都会影响最终结果稍不留神就会算出一版和 Excel 对不上、或者和同事口径对不齐的数字然后被业务追问好几天。分位数的本质是把一组数据从小到大排好序再从某个百分比位置切一刀用那个位置的值代表整体水平。它和平均值最大的区别是不容易被极端值绑架。一组订单里只要出现几个万元大单平均值就会明显偏高但 P90 依然能告诉你“绝大多数订单落在什么价位”。所以凡是涉及分层分析、长尾治理、性能水位监控的报表分位数都是绕不开的基本功。这篇文章会从概念讲起把 SQL 求分位数的四种主流写法、三个典型业务场景、以及我这些年踩过的坑一次说清楚。不管你的数据库有没有内置分位数函数应该都能找到能直接抄回项目的方案。1. 先搞明白分位数在算什么再看 SQL 怎么写1.1 一个订单金额案例说清“位置指标”和“均值指标”的区别假设某运营平台有 10 笔订单金额分别是 8、12、15、20、25、30、40、55、80、200。平均值一算是 48.5。如果拿着 48.5 元这个均值去做促销方案很容易误判明明大部分订单只有二三十元怎么平均价这么高因为那个 200 元的订单把均值拉上去了。换成分位数来看中位数 P50 落在第 5 和第 6 个值中间也就是 (25 30) / 2 27.5。它的业务含义很直观一半订单低于 27.5 元一半订单高于 27.5 元不受 200 元的影响。P90 则是继续往排序后的位置走对应的大约是 80 元这个量级意思是“90% 的订单金额小于等于这个值”。如果你的目标是覆盖绝大多数订单P90 比 MAX 靠谱因为 MAX 200 可能只是个别异常单。统计口径数值业务解读AVG48.5被 200 元大单拉高无法代表典型订单P5027.5一半订单低于该值一半高于该值P90约 80九成订单不高于该值MAX200极端长尾需要单独关注这就是分位数在业务里最大价值它不是描述“平均怎么样”而是描述“大多数怎么样”“尾部怎么样”。在 SQL 里实现这个逻辑难点不在理解定义而在怎么把“排序后的位置”翻译成一条能跑得动的查询。1.2 业务上高频出现的分位指标到底怎么解读P50 / 中位数用来衡量一般水平。比如城市薪资中位数、客单价中位数、页面浏览时长中位数。薪资分布往往右偏中位数比平均值更能反映大多数人的真实水平。P90 / P95用来衡量“绝大多数场景的上限”。比如页面首屏耗时 P95意味着 95% 的用户体验都被覆盖到了丢掉那 5% 的极端慢请求对优化更有指导意义。如果拿平均值当监控阈值接口偶尔一个长尾请求就能把均值拉高导致误报不断。P99重点关注尾部风险。网关、交易链路这类对稳定性敏感的指标常用 P99目的是把偶发劣化控制住因为它直接影响一小部分真实用户的体验。Q1 / Q3四分位数配合 IQR 做异常值识别箱线图就是基于这两个分位数画出来的。想要判断某个值是不是异常点先看它有没有超出 Q1 - 1.5 * IQR 到 Q3 1.5 * IQR 的范围本质上还是分位数问题。业务场景不同需要的分位点也不同。后面我会针对电商订单、接口监控、薪酬分析这三个最常见的场景给出可以直接改改就能用的 SQL。2. SQL 求分位数的四种主流玩法2.1 原生窗口 / 聚合函数PERCENTILE_CONT 与 PERCENTILE_DISC如果你用的数据库内置了分位数函数那是最省事的。PERCENTILE_CONT 是连续型算法结果可能不是原始表里的某个真实值而是在相邻排序值之间做线性插值PERCENTILE_DISC 是离散型算法返回排序后最接近目标分位点的一个真实值。比如一张订单表想算订单金额 P90SELECT percentile_cont(0.9) WITHIN GROUP (ORDER BY amount) AS p90 FROM sales_data;这是 PostgreSQL 风格的写法PERCENTILE_CONT 作为聚合函数使用。有些数据库要求窗口写法比如 SQL Server 里语法差不多但要再加一层 OVER()SELECT percentile_cont(0.9) WITHIN GROUP (ORDER BY amount) OVER() AS p90 FROM sales_data;执行逻辑并不复杂先按 ORDER BY 的目标字段排序再根据百分位参数计算目标位置位置落在两个相邻值之间时做插值或者直接取相邻值。这里需要注意不同的数据库在离散算法上对“取上一个值还是下一个值”可能有细微差别所以跨数据库对比结果时别指望两个库算出来的 P90 在小数位上永远一模一样。MySQL 8.0 这类只提供通用窗口函数、没有内置分位数聚合函数的数据库则需要用本章后面讲到的方法来模拟。2.2 分桶函数NTILE快速近似但容易踩边界NTILE 的思路是把排好序的数据尽量均匀地分成 N 个桶每个桶里的行数相同或相差 1。既然分成 100 桶那么第 90 桶的边界就近似 P90第 99 桶的边界就近似 P99。WITH bucketized AS ( SELECT amount, NTILE(100) OVER (ORDER BY amount) AS bucket FROM sales_data ) SELECT bucket, MIN(amount) AS lower_bound, MAX(amount) AS upper_bound, COUNT(*) AS row_cnt FROM bucketized GROUP BY bucket ORDER BY bucket;每次看第 90 桶或第 99 桶的 lower_bound 就得到对应的分位近似值。这个方案几乎所有主流数据库都支持简单直接但它有三个问题NTILE 是按行数均分不是按数值均分如果数据行数不是 100 的整数倍前几个桶的行数会多一行位置会有些许偏移。当数据中存在大量重复值时同一个数值可能被切到两个不同的桶里导致边界不稳定。如果数据量小于 100 行会出现大量空桶P99 这类的指标压根没有意义。所以 NTILE 更适合做“快速看一眼分布”尤其是把用户或订单切成等频分层来抽样不适合做成严谨的业务口径。2.3 比例定位法ROW_NUMBER COUNT兼容性最强如果数据库没有原生分位数函数我最常用的替代方案是 ROW_NUMBER COUNT。原理很朴素先算出目标位置是第几行再取出那一行的值。WITH ranked AS ( SELECT amount, ROW_NUMBER() OVER (ORDER BY amount) AS rn, COUNT(amount) OVER () AS total FROM sales_data ) SELECT amount AS p90 FROM ranked WHERE rn CEIL(0.9 * total);这段 SQL 的含义是把金额升序排列给每行编号第 90 百分位的位置近似为 0.9 * 总行数向上取整然后取那一行的金额。需要注意这里的 COUNT 我写的是 COUNT(amount) 而不是 COUNT(*)这样 NULL 行不会被计入有效总行数后面踩坑部分会专门展开。如果想算中位数并且要兼容“偶数行取中间两个数平均值”的统计口径可以这样写WITH ranked AS ( SELECT amount, ROW_NUMBER() OVER (ORDER BY amount) AS rn, COUNT(amount) OVER () AS total FROM sales_data ) SELECT AVG(amount) AS median FROM ranked WHERE rn IN (FLOOR((total 1) / 2.0), CEIL((total 1) / 2.0));这个方案几乎能在所有数据库里跑也是跨库迁移时最容易保持一致口径的方法。缺点是每行都要算一次窗口 COUNT在大表上会有一轮额外的排序和聚合开销后面优化部分会讲怎么处理。2.4 累计分布函数CUME_DIST适合做占比边界CUME_DIST() 返回当前行在排序分区内的累计分布比例意思是“小于等于当前值的行数占总行数的比例”。比如 10 行数据里排第 9 的行CUME_DIST 通常就是 0.9正好对应 P90 的位置。WITH cumulative AS ( SELECT amount, CUME_DIST() OVER (ORDER BY amount) AS cd FROM sales_data ) SELECT MIN(amount) AS p90 FROM cumulative WHERE cd 0.9;这段 SQL 是取“第一个累计比例达到 0.9 的金额”。它和 ROW_NUMBER 方案很相似区别在于 CUME_DIST 遇到重复值时会把所有相等值都算进占比所以如果某个金额出现频率很高累计比例会一下子超过 0.9导致取到的值偏大。提示用 CUME_DIST 求分位数时要提前和业务确认口径是“第一个超过目标比例的金额”还是“最后一个未超过目标比例的金额”两种口径差一档结果可能完全不同。2.5 四种方案的横向对比方案核心函数是否插值兼容性典型场景PERCENTILE_CONT原生聚合/窗口是需数据库原生支持平滑水位线、技术监控阈值PERCENTILE_DISC原生聚合/窗口否需数据库原生支持必须落在真实数据上NTILE 分桶窗口函数否几乎全支持快速分层、近似分布ROW_NUMBER COUNT窗口函数可控几乎全支持跨库通用、精确求值CUME_DIST窗口函数否窗口函数均支持累计占比边界、分桶判断3. 实操拆解三个最常见的分位数查询场景3.1 场景一订单金额 P50 / P90 / P99判断客单价结构先看一个最简单的整表分位。某电商团队想了解近期订单金额分布尤其想评估有没有出现极端大额订单于是写了一条查询SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS p50, percentile_cont(0.9) WITHIN GROUP (ORDER BY amount) AS p90, percentile_cont(0.99) WITHIN GROUP (ORDER BY amount) AS p99 FROM orders WHERE order_date BETWEEN 2026-01-01 AND 2026-01-10;如果业务还要按城市拆分就在 GROUP BY 后面加上城市维度SELECT city, percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS p50, percentile_cont(0.9) WITHIN GROUP (ORDER BY amount) AS p90, percentile_cont(0.99) WITHIN GROUP (ORDER BY amount) AS p99 FROM orders WHERE order_date BETWEEN 2026-01-01 AND 2026-01-10 GROUP BY city;结果解读要记住一个原则P99 和 MAX 的差距往往比 P90 本身更重要。如果 P99 是 800 元MAX 是 5 万元说明业务里确实存在极小概率的异常大单下游结算、财务分析都要多留一个心眼如果 P99 和 MAX 很接近说明最大的订单也就是前 1% 的水平整体分布没有离谱的长尾。我在做这类分析时还有个习惯把 P50、P90、P99 和上一次周期的结果放在同一张对比表里。只看绝对值没感觉一旦发现 P90 从 120 元涨到 160 元或者 P99 翻了一倍运营就能快速定位到客单价结构的变化。单纯看平均值往往掩盖了这种变化。3.2 场景二接口响应耗时 P95 / P99分组算慢请求水位接口耗时数据是典型的长尾分布大多数请求几十毫秒偶尔一次卡顿几秒钟能把平均值拉到很高却拉不动 P99。这样的指标用平均值做监控阈值会出现“明明监控一直在报警但用户体验大多数时候是好的”这种局面。所以生产环境通常只看 P95 和 P99。聚合写法如下SELECT service_name, percentile_cont(0.95) WITHIN GROUP (ORDER BY duration_ms) AS p95, percentile_cont(0.99) WITHIN GROUP (ORDER BY duration_ms) AS p99 FROM api_log WHERE ts 2026-01-01 00:00:00 AND ts 2026-01-02 00:00:00 AND status 200 GROUP BY service_name;如果用的数据库必须写成窗口形式比如 SQL Server 风格那么要格外小心一件事窗口函数不会折叠行查询结果的行数仍然等于明细行数每个服务有多少条日志分位值就会在这多少行上重复出现。一定要加 DISTINCT或者直接在外面套一层子查询去重SELECT DISTINCT service_name, percentile_cont(0.95) WITHIN GROUP (ORDER BY duration_ms) OVER (PARTITION BY service_name) AS p95 FROM api_log WHERE ts 2026-01-01 00:00:00 AND status 200;窗口写法也有一个聚合写法替代不了的价值想在明细行上直接标记“这条请求是否超过 P95”。比如找出所有超过 P95 的慢日志再和业务字段做关联窗口写法就非常顺手WITH log_with_p95 AS ( SELECT log_id, service_name, duration_ms, percentile_cont(0.95) WITHIN GROUP (ORDER BY duration_ms) OVER (PARTITION BY service_name) AS p95 FROM api_log WHERE ts 2026-01-01 00:00:00 ) SELECT log_id, service_name, duration_ms FROM log_with_p95 WHERE duration_ms p95;这个查询跑完之后拿到的就是每个服务里“那些超过了自身 P95 水位的慢请求”可以用来做链路分析、慢调用追踪。正是因为能在明细层保留分位数窗口形式在监控排查场景里反而更常用。3.3 场景三部门薪酬中位数DISC 和 CONT 到底怎么选薪酬数据也是右偏分布的典型少数高薪会拉高均值但大多数员工集中在中间层。某公司想对比各团队的薪酬中位数排查薪资倒挂或结构异常于是写出这样一条查询SELECT dept_id, percentile_disc(0.5) WITHIN GROUP (ORDER BY salary) AS median_disc, percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median_cont FROM employee GROUP BY dept_id;两个函数的结果在员工数为奇数时通常一致都是排序后正中间那个人的薪资但员工数为偶数时就会分家CONT 把中间两个值取平均DISC 返回中间两个值里靠前的那个不同数据库实现略有差异但基本都不会返回一个表中不存在的新值。选择标准我一般这样把握如果业务想要“一个真实员工拿到的工资”作为代表用 DISC因为它不会产生公司里没有任何人拿这个工资的虚拟值。如果业务想要和 Excel 中位数更接近、且愿意接受平滑的统计口径用 CONT因为连续插值本质上是把中间两档做平均。如果是做薪酬分位线、职级对标这类对外报告提前和人力确认口径最怕的是前后两次报表一次用 DISC、一次用 CONT结果偶有差异被误解成薪资调整。提示做薪酬分析前先过滤掉薪资为 0、离职标记异常之类的脏数据。否则一个 0 薪资会直接改变排序位置让整个部门的中位数失去参考意义。4. 从踩坑记录里整理出的六条实战经验4.1 NULL 值会让 ROW_NUMBER 方案直接算错分位数函数在计算时通常会忽略 NULL但如果你用 ROW_NUMBER COUNT() 来模拟问题就来了。假设表里有 10 行其中 1 行 amount 是 NULL真正有效数据只有 9 行。正确的 P90 应该基于 9 行计算也就是排序后第 8.1 位附近的值。可如果你用 COUNT()total 算出来还是 10那么 CEIL(0.9 * 10) 取第 9 行取到的已经是 9 行里的最大值等于 P100完全错了。解决办法有两个第一是写明 COUNT(amount) 而不是 COUNT(*)让窗口函数只统计非空值第二是干脆在源数据阶段加一层 WHERE amount IS NOT NULL。我自己的习惯是两手都用因为再加一层过滤还能让排序过程少处理几行。4.2 窗口函数和 GROUP BY 的执行顺序别凭直觉写SQL 里窗口函数是在 GROUP BY 之后执行的。因此想算“每天销售额的 P90”不能在一句 SQL 里同时完成“先按天聚合”和“对明细金额求分位”。比如下面这种写法就是逻辑混乱的-- 错误示例GROUP BY 之后 amount 已经不是明细值 SELECT sale_date, percentile_cont(0.9) WITHIN GROUP (ORDER BY amount) OVER () FROM sales GROUP BY sale_date;正确做法是先聚合出一张日销售额表再对这张表求分位WITH daily_sales AS ( SELECT sale_date, SUM(amount) AS daily_amount FROM sales GROUP BY sale_date ) SELECT percentile_cont(0.9) WITHIN GROUP (ORDER BY daily_amount) AS p90 FROM daily_sales;这种先聚合、再外层求分位的模式能避免 80% 的“分位数结果不对”问题。遇到看起来复杂的分位数需求先问自己一句我到底要给哪些行算分位先把这个集合定义清楚再写窗口。4.3 PERCENTILE_CONT 会算出表里不存在的虚拟值还是拿薪资举例。某部门 6 人薪资分别是 6000、8000、10000、15000、22000、30000。PERCENTILE_CONT 的中位数等于 (10000 15000) / 2 12500但这个部门没有任何人拿 12500。CONT 的优势是平滑代价是可能产生“虚拟值”。所以在交付分位数 SQL 之前我会先向业务方确认这个指标是用来做分界线还是用来代表典型值如果业务方拿着报表说“这个中位数怎么不是任何人的工资”那多半是他心理预期是 DISC而我们实现成了 CONT。为了避免来回扯皮最稳妥的做法是在查询结果里同时给出一列 DISC 和 CONT并在报表字段命名上写清楚比如 median_salary_disc / median_salary_cont。4.4 NTILE 模拟分位数的三个局限空桶、重复值、行数不均NTILE 按行均分的逻辑决定了它不是精确的分位数函数。数据只有 70 行时NTILE(100) 只能产生 70 个非空桶剩下 30 个桶为空有重复值时同一个数值可能被切开到两个桶取边界值时这桶和那桶会得到同一个数看起来是“分位数重叠”了。还有一个细节是总数不能用 100 整除时前面的桶会多一行这种位置偏移在数据量很大时影响不大在数据量小的时候却很明显。因此我一般只在两个场景用 NTILE一是快速给用户分层比如把用户按消费金额切成 10 个等频档位辅助运营做人群圈选二是做随机抽样前的分桶。真要输出 P95、P99 这样的指标还是优先用 PERCENTILE_CONT 或 ROW_NUMBER COUNT 定位法。4.5 大表上跑分位数排序开销是躲不掉的但有四条优化路分位数查询本质上需要一次全局排序不管哪种方案都躲不开。几百万行的表还好如果是几亿行的大宽表直接跑 P99查询延迟可能从秒级变成分钟级。我在实践里常用的优化手段如下第一步做过滤。时间范围、状态字段、NULL 值都先在 WHERE 里过滤掉让参与排序的行数尽量小。很多报表问题不是 SQL 写错了而是忘了过滤已经失效的历史数据。第二步利用分区裁剪。如果表是按天分区查询一定要带上明确的时间范围避免扫描全部分区。第三步预聚合。如果每天都要看 P99可以每天凌晨跑一个定时任务把当天分位数算好落成一张很小的日汇总表。后续报表直接查小表哪怕 SQL 写得糙一点也没关系。第四步接受近似。有些云数仓提供了近似分位数扩展函数比如基于 Sketch 结构的算法在数据量极大、误差允许在几个百分点以内时速度能快一个数量级。使用前一定要用抽样结果验证一下误差范围别直接上生产。4.6 不同数据库的语法差异写之前先花三分钟查文档分位数函数在不同数据库里的差异比想象中大。有的商用数据库要求 PERCENTILE_CONT 后面必须加 OVER有的把它当纯聚合函数只能配 GROUP BY有的数据库有专门的 MEDIAN 函数还有的只支持 NTILE。MySQL 8.0 虽然补齐了窗口函数却仍然没有内置 PERCENTILE_CONT只能靠 ROW_NUMBER 或 NTILE 模拟。正因为差异这么大团队内部最好约定一个统一标准。比如跨库系统里最稳的是直接封装一套 ROW_NUMBER COUNT(字段) 的通用逻辑做成视图或函数所有报表都走同一个入口。这样即使底层从某个数据库迁到另一个数据库分位数口径也不会跟着漂移。5. 常见问题速查分位数查询排错手册5.1 症状与处理对照表问题现象可能原因处理建议查询结果全是 NULL分区内没有数据先用 COUNT 验证数据量检查过滤条件P90 和 Excel 算出来不一样算法位置公式不同确认口径Excel 的 PERCENTILE.INC 接近 CONTNTILE 第 90 桶没有数据数据量不足 100 行改用 ROW_NUMBER COUNT 或原生分位数函数窗口写法结果每行重复没有去重外层加 DISTINCT或改用 GROUP BY 聚合大表查询超时全局排序开销过大过滤、预聚合、采样、近似函数中位数结果跳变偶数行使用了 DISC评估是否改用 CONT 取平均分组结果明显不对窗口函数漏了 PARTITION BY检查分组维度或改成 GROUP BY WITHIN GROUP结果和同事不一致一个用 DISC一个用 CONT统一口径最好同时展示两种算法列5.2 验证分位数结果对不对的最快方法不管用哪种 SQL 写法我都建议先构造一个从 1 到 10 的小样本手算一遍验证。比如WITH numbers AS ( SELECT generate_series(1, 10) AS val ) SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY val) AS p50_cont, percentile_disc(0.5) WITHIN GROUP (ORDER BY val) AS p50_disc, percentile_cont(0.9) WITHIN GROUP (ORDER BY val) AS p90_cont, percentile_disc(0.9) WITHIN GROUP (ORDER BY val) AS p90_disc FROM numbers;手算结果P50_CONT 是 5.5P50_DISC 在多数实现里是排序后中间偏小的那个值也就是 5 或 6不同数据库可能不同P90_CONT 大约是 9.1 附近插值P90_DISC 则通常返回 10。先跑出这几个数字再跑业务大表心里就有底了。进一步调试时可以把排序位置、累计分布都打印出来SELECT val, ROW_NUMBER() OVER (ORDER BY val) AS rn, COUNT(*) OVER () AS total, CUME_DIST() OVER (ORDER BY val) AS cd FROM numbers ORDER BY val;拿到这张明细后再回到正式查询比如“P90 到底取的是哪一行”逻辑就清晰多了。定位类的问题用这种“拆开看每一步”的方式往往比盯着聚合结果猜更快。最后分享一个小习惯交付任何分位数报表之前先跑一条只输出明细排序的 SQL手工框出目标位置附近五行的数据看一眼再跑正式聚合。这个动作多花几十秒却能避免整张报表上线之后才发现口径错了。分位数这东西定义差一点点结果就差一截而业务方只会记得“你上次给的那个数和别人不一样”。提前验证既是对自己的保护也是让数据真正可信的开始。
阅读完成 · 觉得有帮助？
