SQL分位数计算实战指南：从内置函数到精确与近似实现

如果在SQL里让你算分位数先别急着写PERCENTILE_CONT。很多人第一反应是“这还不简单排序取位置就行”但真上手做光“位置”怎么定、要不要插值、不同数据库函数能不能直接用就能卡住半天。这篇文章我把自己这些年踩过的坑和沉淀下来的写法整理成一份完整清单覆盖内置函数、手写SQL、近似分位数、分组计算、常见翻车场景适合所有需要处理数据分析或报表需求的同学。1. 分位数到底在算什么业务场景与SQL实现的第一个坑1.1 分位数的含义和应用场景分位数是把一组数据按数值大小升序排列后切成100份第p百分位就是“有p%的数据小于等于它有(100-p)%的数据大于等于它”的那个值。P50就是中位数P90和P99在性能监控、订单金额分布、接口耗时分析里出现频率极高。举个例子运营要看“平台订单金额P90是多少”并不是要你算出平均值而是想知道“90%的订单金额都低于这个数”用来评估消费力分层、优惠券阈值设计或者判断是否存在少数大额订单扭曲整体。做监控的同学也很熟悉接口P99耗时超过500ms意味着99%请求都在500ms内剩下1%是长尾需要重点优化。这些场景在SQL里都需要一个可靠的分位数计算方式。1.2 为什么“第p百分位”不一定是一个确定的数很多人在这一步开始晕。假如你有100个订单金额P90就是排序后第90个值看起来没问题。但如果数据量不是100而是101个那P90到底取第90个还是第91个如果数据量是50个P90的位置是500.945直接取第45个值吗那P50呢500.525取第25个问题在于位置如果是小数必须决定是向下取整、向上取整还是做线性插值。常见的计算口径就有好几种位置 p * n然后向上取整位置 p * (n - 1) 1然后取整或插值位置 p * (n - 1)然后线性插值PERCENTILE_CONT采用的是线性插值PERCENTILE_DISC返回的是实际存在的数据点两者在同样的p下可能给出不同结果。更麻烦的是不同数据库对“位置”的解释还略微不同。你拿Excel算一个P90再拿SQL算结果对不上不一定是你写错可能只是口径不同。1.3 SQL实现分位数要迈过三道坎第一道坎很多数据库根本没有内置分位数函数。MySQL直到8.0都没有PERCENTILE_CONTMariaDB类似这让不少人以为SQL做不到分位数只能把数据拉出来到Python里算。第二道坎就算有函数也要搞清楚它是聚合函数还是窗口函数是精确算法还是近似算法。用错位置轻则语法报错重则结果偏差但完全看不出来。第三道坎没有函数时手写SQL排序、编号、取上下界、插值整套逻辑很容易写复杂尤其还要按部门、按城市、按商品ID分组算代码会迅速失控。2. 内置分位数函数先看你的数据库有没有“现成饭”2.1 窗口函数版分位数PERCENTILE_CONT 和 PERCENTILE_DISC如果你的数据库支持这两种函数优先直接使用。PERCENTILE_CONT(p)是连续分位数结果允许是小数算法是线性插值PERCENTILE_DISC(p)是离散分位数返回输入数据里真实存在的一个值。p的范围是0到10.5表示中位数0.9表示P90。在支持窗口函数语法的数据库里大概长这样SELECT DISTINCT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY amount) OVER () AS p90 FROM orders WHERE status paid;如果按渠道分组SELECT DISTINCT channel, PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY amount) OVER (PARTITION BY channel) AS p90 FROM orders WHERE status paid;注意有的数据库写PERCENTILE_CONT时会要求WITHIN GROUP在前、OVER在后顺序不能反。而且窗口函数会为每一行都返回同一个结果所以外面一般要加DISTINCT否则结果集会膨胀。2.2 数据库支持情况速查用之前先确认你手里的数据库支持到哪一步。下面是我常用的对照表建议收藏数据库精确分位数函数近似分位数函数语法要点PostgreSQLpercentile_cont / percentile_disc无内置近似函数聚合函数写法GROUP BY分组SQL ServerPERCENTILE_CONT / PERCENTILE_DISC无窗口函数需OVEROraclePERCENTILE_CONT / PERCENTILE_DISC无支持聚合和窗口功能最全MySQL 8.0无无需手写SQL或使用窗口函数模拟Hivepercentile / percentile_approxpercentile_approxpercentile只支持整数列浮点用approxSpark SQLpercentile / percentile_approxpercentile_approx新版本可用精确percentileClickHousequantileExact / quantilequantile / quantileTimingquantile是近似quantileExact是精确Doris无percentile_approx目前主要提供近似分位函数这张表列的是常见情况版本不同会有细微差别。比如PostgreSQL的percentile_cont是聚合函数不能用OVER()当窗口函数用但是Oracle里它可以做分析函数这俩写起来完全不是一回事。2.3 内置函数也存在精度和口径差异PERCENTILE_CONT本身是精确插值不会为了性能牺牲精度。但Hive的percentile_approx、ClickHouse的quantile、Presto的approx_percentile走的是近似算法牺牲少量精度来换取内存和速度在海量数据下优势明显。就算都是精确函数PERCENTILE_CONT和PERCENTILE_DISC的结果也可能不一样。比如一组数据[1, 2, 3, 4, 5]P50用PERCENTILE_CONT算出来是3因为位置是0.5*(5-1)2正好对应第3个值。但如果换成[1, 2, 3, 4]P50用插值就是2.5而PERCENTILE_DISC可能会返回2或3取决于具体实现。这就是很多人“明明用了官方函数怎么还是跟预期不一样”的原因。3. 没有内置函数怎么办手写SQL实现精确分位数3.1 核心思路先编号再找位置最后插值手写精确分位数绕不开排序和编号。好在现代MySQL、PostgreSQL都支持窗口函数ROW_NUMBER()负责编号COUNT(*) OVER()负责统计总行数。分位数计算可以拆成四步按目标字段升序排序并编号算出当前分位点在排好序数据中的浮点位置pos p * (n - 1)取出pos向下取整所对应的下界值和下一行的上界值用小数部分做线性插值下界值 (上界值 - 下界值) * 小数部分通行的位置公式是p * (n - 1)也就是把第一个值当作0号位置最后一个值当作n-1号位置这样P0就是最小值P100就是最大值。这个公式和很多数据分析工具里的PERCENTILE.INC一致。3.2 用ROW_NUMBER和LEAD实现中位数先拿中位数练手因为中位数是最常见的分位数。SQL可以这样写WITH ranked AS ( SELECT amount, ROW_NUMBER() OVER (ORDER BY amount) AS rn, COUNT(*) OVER () AS cnt, LEAD(amount, 1, amount) OVER (ORDER BY amount) AS next_amount FROM orders WHERE status paid ), calc AS ( SELECT amount, next_amount, cnt, (cnt - 1) * 0.5 AS pos FROM ranked ) SELECT amount * (1 - (pos - FLOOR(pos))) next_amount * (pos - FLOOR(pos)) AS median FROM calc WHERE rn FLOOR(pos) 1;解释一下这个写法。LEAD(amount, 1, amount)是取当前行的下一行值如果已经是最后一行就返回当前行本身这个默认值很关键能避免边界位置出现NULL导致结果全变NULL。calc里算出浮点位置pos然后只保留下界那一行。如果pos是整数小数部分是0公式退化成直接取下界值如果pos带小数下一行参与插值。对[1, 2, 3, 4]这组数据n4pos1.5下界rn2对应值2上界rn3对应值3插值结果2.5和PERCENTILE_CONT一致。3.3 任意分位数通用模板把0.5换成你需要的p值一套模板解决P90、P95、P99。WITH ranked AS ( SELECT amount, ROW_NUMBER() OVER (ORDER BY amount) AS rn, COUNT(*) OVER () AS cnt, LEAD(amount, 1, amount) OVER (ORDER BY amount) AS next_amount FROM orders WHERE status paid AND amount IS NOT NULL ), calc AS ( SELECT amount, next_amount, (cnt - 1) * 0.9 AS pos FROM ranked ) SELECT amount * (1 - (pos - FLOOR(pos))) next_amount * (pos - FLOOR(pos)) AS p90 FROM calc WHERE rn FLOOR(pos) 1;想要一次算出多个分位数可以改成CASE WHEN或把多个CTE合并不过我更推荐写成一个通用参数化SQL需要哪个p值就替换哪个。实际工作里我通常把这段逻辑包装成视图或存储过程避免每次重写。记得一定要在源数据里过滤掉NULL值否则ORDER BY amount会把NULL放在最前面取决于数据库默认NULL排序规则编号位置全乱算出来的分位数毫无意义。3.4 分组分位数按部门、按渠道分别算业务上极少只算一个整体分位数更多是“每个城市P90”“每个类目P99”。只要把窗口函数都加上PARTITION BY模板几乎不用变。WITH ranked AS ( SELECT channel, amount, ROW_NUMBER() OVER (PARTITION BY channel ORDER BY amount) AS rn, COUNT(*) OVER (PARTITION BY channel) AS cnt, LEAD(amount, 1, amount) OVER (PARTITION BY channel ORDER BY amount) AS next_amount FROM orders WHERE status paid AND amount IS NOT NULL ), calc AS ( SELECT channel, amount, next_amount, (cnt - 1) * 0.9 AS pos FROM ranked ) SELECT channel, amount * (1 - (pos - FLOOR(pos))) next_amount * (pos - FLOOR(pos)) AS p90 FROM calc WHERE rn FLOOR(pos) 1;这个查询会按channel分组每组内部独立排序、独立计数最后每组返回一行。核心点在于ROW_NUMBER、COUNT、LEAD三个窗口函数必须使用相同的PARTITION BY漏掉任何一个组之间的边界都会错乱。分组后可能出现小分组数据量极少的情况比如某个渠道只有1条订单。此时cnt1pos0rn1LEAD返回自身结果等于该订单金额本身语义上没问题。4. 不想精确算的时候NTILE、近似函数和使用场景取舍4.1 NTILE估算分位数的真相有些同学图省事直接用NTILE(100) OVER (ORDER BY amount)分桶然后取第90个桶里的最小值当P90。这个方法不是完全不行但要清楚它有精度损失。NTILE(n)会把有序数据尽量均匀地分成n个桶桶数不能整除时前面的桶会比后面的桶多一行。比如101条数据分100桶因为101/100不是整数前1个桶会分配到2行剩下的桶各1行。此时第90桶代表的根本不是严格意义上的90%位置。用NTILE估算分位数的问题在极端分桶下尤其明显。如果你只有50条数据却分成100桶NTILE会报错或无法保证桶数量。所以NTILE更适合做简单的等频分箱比如给用户分十档而不是用来精确算P99。在需要严谨的分位数数值时还是用前面那套精确SQL。4.2 大数据引擎里的近似分位数函数当数据量到了亿级以上精确分位数意味着全量排序无论内存还是耗时都很难接受。近似算法应运而生常见的实现思路包括TDigest、Greenwald-Khanna算法、Reservoir Sampling等。它们不要求完整排序只维护一个压缩的摘要结构因此能在大数据量下快速返回一个误差可控的估计值。Hive和Spark SQL里最常用的是percentile_approx-- Hive SELECT channel, percentile_approx(amount, 0.9, 1000) AS p90 FROM orders WHERE status paid GROUP BY channel;第三个参数是精度控制越大越准内存消耗也越高。默认值在不同版本里不一样业务要求不严实时可以靠它快速出数。ClickHouse则是quantile系列SELECT channel, quantile(0.9)(amount) AS p90_approx, quantileExact(0.9)(amount) AS p90_exact FROM orders WHERE status paid GROUP BY channel;quantile是近似计算性能极高quantileExact走精确路径数据量大时会更重。Doris目前主要提供percentile_approx同样适合报表场景。这类函数最大的好处是内存占用稳定不随数据量线性膨胀。你给一张100亿行的大表算P99用近似函数几分钟就能出结果精确算法可能直接拖垮节点。4.3 精确和近似的差距有多大近似分位数的误差并非不可控。以一个实际压测场景模拟假设某系统有100万次请求耗时精确算P99是213ms近似函数默认参数算出来可能是215ms到220ms之间对于监控告警和容量规划完全够用。但如果你的场景是财务对账、金额精确分摊差一分钱都不能接受那就老老实实走精确SQL。近似函数适合的场景是快速看趋势、监控阈值判断、大促实时大屏精确函数适合出正式报表、财务结算、需要对外发布的数据。5. 实操案例给订单表算P50、P90、P995.1 准备模拟数据用订单表做例子表结构如下CREATE TABLE orders ( order_id INT, channel VARCHAR(20), amount DECIMAL(10,2), status VARCHAR(10) ); INSERT INTO orders VALUES (1, app, 10.00, paid), (2, app, 20.00, paid), (3, app, 30.00, paid), (4, app, 40.00, paid), (5, web, 50.00, paid), (6, web, 60.00, paid), (7, web, 70.00, paid), (8, web, 80.00, paid), (9, web, 90.00, paid);这个数据故意设计成两个渠道各4条和5条记录方便核对分位数逻辑。5.2 一次算出多个分位数先在MySQL里用窗口函数实现整体P50、P90、P99WITH ranked AS ( SELECT amount, ROW_NUMBER() OVER (ORDER BY amount) AS rn, COUNT(*) OVER () AS cnt, LEAD(amount, 1, amount) OVER (ORDER BY amount) AS next_amount FROM orders WHERE status paid ) SELECT MAX(CASE WHEN rn FLOOR((cnt - 1) * 0.5) 1 THEN amount * (1 - ((cnt - 1) * 0.5 - FLOOR((cnt - 1) * 0.5))) next_amount * ((cnt - 1) * 0.5 - FLOOR((cnt - 1) * 0.5)) END) AS p50, MAX(CASE WHEN rn FLOOR((cnt - 1) * 0.9) 1 THEN amount * (1 - ((cnt - 1) * 0.9 - FLOOR((cnt - 1) * 0.9))) next_amount * ((cnt - 1) * 0.9 - FLOOR((cnt - 1) * 0.9)) END) AS p90, MAX(CASE WHEN rn FLOOR((cnt - 1) * 0.99) 1 THEN amount * (1 - ((cnt - 1) * 0.99 - FLOOR((cnt - 1) * 0.99))) next_amount * ((cnt - 1) * 0.99 - FLOOR((cnt - 1) * 0.99)) END) AS p99 FROM ranked;这段代码看着长本质就是前面单分位模板做了三次再用MAX(CASE WHEN)把多行拼成一行。如果你用的数据库支持PERCENTILE_CONT这句话的工作量可以大幅简化但在MySQL里只能这么写。分组版本只需要把PARTITION BY channel加进去结果如下WITH ranked AS ( SELECT channel, amount, ROW_NUMBER() OVER (PARTITION BY channel ORDER BY amount) AS rn, COUNT(*) OVER (PARTITION BY channel) AS cnt, LEAD(amount, 1, amount) OVER (PARTITION BY channel ORDER BY amount) AS next_amount FROM orders WHERE status paid ) SELECT channel, MAX(CASE WHEN rn FLOOR((cnt - 1) * 0.5) 1 THEN amount * (1 - ((cnt - 1) * 0.5 - FLOOR((cnt - 1) * 0.5))) next_amount * ((cnt - 1) * 0.5 - FLOOR((cnt - 1) * 0.5)) END) AS p50, MAX(CASE WHEN rn FLOOR((cnt - 1) * 0.9) 1 THEN amount * (1 - ((cnt - 1) * 0.9 - FLOOR((cnt - 1) * 0.9))) next_amount * ((cnt - 1) * 0.9 - FLOOR((cnt - 1) * 0.9)) END) AS p90 FROM ranked GROUP BY channel;5.3 结果怎么验算拿app渠道的数据做手动验证。app有4条记录金额分别是10、20、30、40。P50的位置是0.5*(4-1)1.5下界是第2个值20上界是第3个值30插值得到25所以app的P50是25。P90的位置是0.9*32.7下界第3个值30上界第4个值40插值得到30 (40-30)*0.7 37。这个结果说明app渠道90%的订单金额在37元以下。如果直接把两条SQL跑一下对比结果完全一致。在实际业务中我强烈建议先用小数据集手工验算一遍确认口径无误再上线特别是从手写SQL切到内置函数或者从内置函数切到近似函数时。6. 常见问题与排查经验6.1 排序方向反了全盘皆输分位数默认是按升序排列。如果你要算的是“耗时最高的1%”很容易下意识用ORDER BY amount DESC结果取到的是最小值的P99差之千里。我一般会在SQL注释里明确写“升序”并在验算时把跑出来的P50和平均值对比一下通常中位数会接近或略低于平均值如果P50比平均值还高很多先怀疑排序和NULL处理。6.2 NULL值到底怎么处理NULL在排序里的位置因数据库而异。Oracle默认NULLS LASTSQL Server默认NULL最小MySQL 8默认NULL也是最小。若你的金额字段有NULL而没过滤分位数会被这些NULL挤到最前导致正常数据整体错位。最稳妥的方法是在子查询里加WHERE amount IS NOT NULL或者只对非空数据计算。6.3 手写SQL在边界为什么算错最容易出错的一行是LEAD没有设置默认值。当p1的时候posn-1此时下界正好是最后一行LEAD返回NULL整个插值表达式变成NULL。我给LEAD加默认值的习惯就是从这个坑里养出来的。另外FLOOR(pos)1可能超过总行数一旦超过说明p值不在0到1之间需要检查参数是否传错。6.4 窗口函数和GROUP BY混用的问题如果是先分组再算分位数窗口函数里的PARTITION BY是关键。有人会写成先GROUP BY channel再在SELECT里用窗口函数结果通常会报错或者算出一个全局分位数。记住分位数需要原始行的上下文窗口函数应该作用在明细行上分组聚合放在外层或者干脆用PARTITION BY。6.5 精确计算太慢怎么办数据超过千万行时精确分位数的全排序会让数据库吃紧。我的经验是如果数据能通过过滤条件缩小范围先过滤如果目标字段是金额、耗时这类数值可以按天或渠道拆分后分段算然后再合并如果业务允许误差直接用近似函数。还有一个思路基于采样数据先出一个大概值再做小范围精确验证很多报表场景并不需要每次都精确。最后分享一个实战习惯我做了这么多分位数需求最大的体会是在写任何分位数SQL之前先明确业务接受的口径和误差范围。是做运营看趋势还是做财务结算决定了你该用内置函数、手写精确SQL还是近似算法。同一张表给运营看P90可以用percentile_approx给财务出账单就必须精确到分。另一个小技巧是把分位数口径固化成文档写清楚位置公式是p*(n-1)还是p*n排序是升序NULL怎么处理每次接新需求直接套用避免反复争论结果差异。这套方法我在MySQL、PostgreSQL、Hive、ClickHouse上都验证过希望也能帮你少踩几个坑。