企业官网建设流程全解析

如果在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.9=45，直接取第45个值吗？那P50呢，500.5=25，取第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_CONT，MariaDB类似，这让不少人以为SQL做不到分位数，只能把数据拉出来到Python里算。

第二道坎：就算有函数，也要搞清楚它是聚合函数还是窗口函数，是精确算法还是近似算法。用错位置，轻则语法报错，重则结果偏差但完全看不出来。

第三道坎：没有函数时手写SQL，排序、编号、取上下界、插值整套逻辑很容易写复杂，尤其还要按部门、按城市、按商品ID分组算，代码会迅速失控。

2. 内置分位数函数：先看你的数据库有没有“现成饭”

2.1 窗口函数版分位数：PERCENTILE_CONT 和 PERCENTILE_DISC

如果你的数据库支持这两种函数，优先直接使用。PERCENTILE_CONT(p)是连续分位数，结果允许是小数，算法是线性插值；PERCENTILE_DISC(p)是离散分位数，返回输入数据里真实存在的一个值。p的范围是0到1，0.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 数据库支持情况速查

用之前先确认你手里的数据库支持到哪一步。下面是我常用的对照表，建议收藏：

这张表列的是常见情况，版本不同会有细微差别。比如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()负责统计总行数。

分位数计算可以拆成四步：

1. 按目标字段升序排序并编号
2. 算出当前分位点在排好序数据中的浮点位置：pos = p * (n - 1)
3. 取出pos向下取整所对应的下界值，和下一行的上界值
4. 用小数部分做线性插值：下界值 + (上界值 - 下界值) * 小数部分

通行的位置公式是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]这组数据，n=4，pos=1.5，下界rn=2对应值2，上界rn=3对应值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条订单。此时cnt=1，pos=0，rn=1，LEAD返回自身，结果等于该订单金额本身，语义上没问题。

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、P99

5.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、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' ) 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*3=2.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 LAST，SQL Server默认NULL最小，MySQL 8默认NULL也是最小。若你的金额字段有NULL而没过滤，分位数会被这些NULL挤到最前，导致正常数据整体错位。最稳妥的方法是在子查询里加WHERE amount IS NOT NULL，或者只对非空数据计算。

6.3 手写SQL在边界为什么算错

最容易出错的一行是LEAD没有设置默认值。当p=1的时候，pos=n-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上都验证过，希望也能帮你少踩几个坑。