CUME_DIST vs PERCENT_RANK：SQL窗口函数排名与累积分布详解

上周搭销售报表时被同事问了一个很有意思的问题同一份数据某销售员业绩的百分位我用PERCENT_RANK()算出来是 0.4286他用CUME_DIST()算出来却是 0.625两个函数名都带“分布”或“排名”看着很像到底哪个才是对的两个都没错只是回答的问题不一样。CUME_DIST()全称累积分布函数PERCENT_RANK()全称百分比排名函数这两个都属于 SQL 标准里的窗口函数专门用来分析一条记录在整组数据里的相对位置。做过数据分析、报表开发、绩效统计的人基本都会碰到区别搞不清楚很容易被业务方追问一句“你这个百分比到底是怎么算出来的”到时候再去翻文档就尴尬了。这一篇是窗口函数实践笔记的第七篇。前几篇里已经陆陆续续聊过ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()这些常见函数这次把最后两个分布类函数放在一起讲。重点放在三件事它们各自的数学口径、什么时候用哪一个、以及实操中那些容易翻车的边界条件。1. 先搞清楚两个函数到底在算什么1.1 各自的数学定义CUME_DIST()计算的是把窗口内的所有行按指定字段排序后小于等于当前行排序值的行数除以窗口内的总行数。说人话就是当前这个值落在整组数据的什么累计位置。公式这样写CUME_DIST (小于等于当前值的总行数) / (窗口内总行数)PERCENT_RANK()计算的是当前行的排名减 1除以窗口内总行数减 1。这里的“排名”在 SQL 标准里明确指向RANK()的语义也就是说遇到并列值时排名会跳号。PERCENT_RANK (当前行的RANK - 1) / (窗口内总行数 - 1)光看公式已经能感受到差异了一个用“值的累计覆盖范围”做分母思想一个用“排名位置的相对偏移”做归一化。但真正让人犯晕的是它们在实际数据里算出来的结果经常看起来非常接近一旦出现并列值或数据倾斜差距立刻就显现出来了。1.2 用生活例子建立直觉想象一个班级考试出分之后你会关心两类问题第一类问题是“我考了 80 分全班不超过这个分数的人占多少比例”比如全班 50 人有 40 个人分数小于等于 80 分那这个比例就是 80%对应CUME_DIST()回答的是累计覆盖率。第二类问题是“按分数从低到高排我排第 10 名我的相对位置在哪个区间”第 10 名在 50 人中的位置用公式算一下是 (10 - 1) / (50 - 1) ≈ 0.184也就是从低到高大约 18.4% 的位置对应PERCENT_RANK()回答的是相对排名位置。注意这里的细节CUME_DIST()说的是“不超过当前值的人数比例”PERCENT_RANK()说的是“当前排名在整个有序队列中的相对偏移”。前者关注值本身覆盖了多少数据后者关注位置本身在整个序列中的相对路径。1.3 先给一张对比表记住差异对比项CUME_DIST()PERCENT_RANK()中文全称累积分布函数百分比排名函数计算公式(小于等于当前值的行数) / 总行数(当前RANK - 1) / (总行数 - 1)取值范围(0, 1]第一行永远大于 0[0, 1]第一行永远是 0并列值处理同值同行结果完全相同不跳值按 RANK 语义同排名结果相同回答的问题有多少比例的数据不超过这个值这条记录在整体中的相对排序位置生活类比成绩单上的“超过百分之多少的同学”排行榜上的“位置刻度”这张表请先存着后面所有细节都是围绕这几行展开的。2. 同一份数据两个函数算出的结果为什么差这么多2.1 准备一份可复现的示例数据为了把计算过程摊开看我用一个销售员业绩表来演示。数据很简单8 个销售员每人一个销售额其中特意安排了两个人销售额相同这样并列值的问题才能暴露出来。CREATE TABLE sales_performance ( employee_name VARCHAR(50), sales_amount DECIMAL(10, 2), region VARCHAR(20) ); INSERT INTO sales_performance VALUES (孙八, 15000.00, 华东), (张三, 12000.00, 华北), (周九, 10000.00, 华南), (李四, 8000.00, 华北), (王五, 8000.00, 华东), (吴十, 7000.00, 华南), (赵六, 5000.00, 华北), (钱七, 3000.00, 华东);接下来用一条查询把ROW_NUMBER()、RANK()、CUME_DIST()、PERCENT_RANK()四个函数同时算出来方便对照。注意我这里排序用的是ASC升序也就是销售额从低到高排列。SELECT employee_name, sales_amount, ROW_NUMBER() OVER (ORDER BY sales_amount ASC) AS row_no, RANK() OVER (ORDER BY sales_amount ASC) AS ranking, CUME_DIST() OVER (ORDER BY sales_amount ASC) AS cume, PERCENT_RANK() OVER (ORDER BY sales_amount ASC) AS pct_rank FROM sales_performance ORDER BY sales_amount ASC;2.2 手工推算一遍弄清楚每个数字的来源查询结果如下我按行解释employee_namesales_amountrow_norankingcumepct_rank钱七3000110.1250赵六5000220.250.1429吴十7000330.3750.2857李四8000440.6250.4286王五8000540.6250.4286周九10000660.750.7143张三12000770.8750.8571孙八15000881.01.0先看第一行钱七销售额 3000在升序排列下它是第一个。CUME_DIST()计算的是“小于等于 3000 的有多少人”只有他自己所以 1/8 0.125。PERCENT_RANK()计算的是 (1 - 1) / (8 - 1) 0。这就是最明显的差异同一个第一名CUME_DIST 不是 0PERCENT_RANK 永远是 0。再看李四和王五这对并列值两个人的销售额都是 8000。小于等于 8000 的行有 5 行钱七、赵六、吴十、李四、王五所以两个人的CUME_DIST()都是 5/8 0.625。但注意RANK()对他们的排名是并列第 4不是第 4 和第 5所以PERCENT_RANK()都是 (4 - 1) / (8 - 1) 0.4286。这里有个特别容易踩的误区如果你直接看row_no李四是 4王五是 5很容易下意识用(5 - 1) / 7 0.5714去算王五的PERCENT_RANK()但标准定义用的是排名不是物理行号。王五的ranking是 4和 李四 一样所以两个人结果相同。并列值越多这种“用 row_no 代入公式”的错觉就越危险。2.3 排序方向对 CUME_DIST 的影响比想象中大很多人在写CUME_DIST()时习惯把销售数据按降序排觉得“业绩高的排前面更直观”。但这里有个隐藏的坑CUME_DIST()的“小于等于”是严格依赖当前排序方向的。我上面用的是ORDER BY sales_amount ASC所以语义是“小于等于当前销售额的行数占比”。如果换成ORDER BY sales_amount DESC函数内部统计的就变成了“大于等于当前销售额的行数占比”。也就是说同样一个人同样的销售额升序和降序得到的CUME_DIST()值是不同的而且业务含义正好相反。升序时 0.625 表示“62.5% 的人业绩不超过 8000”降序时 0.625 表示“62.5% 的人业绩不低于 8000”。PERCENT_RANK()没有这个歧义因为它用的是排名位置只要排序方向定了排名就定了公式里的加减乘除不依赖“大于还是小于”的语义。实践建议是在写 SQL 之前先明确业务口径。你要的是“前 20% 的高业绩客户”那应该用ORDER BY sales_amount DESC配合CUME_DIST()找cume 0.2的行你要的是“价格低于某个水平的商品占比”那应该用ORDER BY price ASC。方向搞反结果完全不对。3. 业务场景里怎么选我的四个判断标准3.1 需求一判断某个值覆盖了多少数据用 CUME_DIST电商场景里经常要看“价格低于某个水平的商品占多少比例”。这是一个典型的累积分布问题边界条件非常清晰“低于”这个动作天然对应“小于等于当前值”。SELECT product_name, price, CUME_DIST() OVER (ORDER BY price ASC) AS price_cume FROM products;假设某件商品算出来price_cume 0.82可以直接解读为该商品价格不高于全站 82% 的商品。如果你想反着看也就是“高于这个价格的商品占多少比例”直接用1 - 0.82 0.18就行不需要再写一层查询。这是我用CUME_DIST()用得最频繁的场景。它的输出天生就是“覆盖率”不需要再做任何换算。3.2 需求二判断某条记录在团队中的相对位置用 PERCENT_RANK再举个例子HR 想看每个销售员在所属大区里的业绩处于什么位置。注意这里是“位置”不是“覆盖率”所以PERCENT_RANK()更贴合因为它本质上就是把排名映射到 0 到 1 的区间。SELECT employee_name, sales_amount, region, PERCENT_RANK() OVER ( PARTITION BY region ORDER BY sales_amount DESC ) AS region_pct_rank FROM sales_performance ORDER BY region, sales_amount DESC;这里我用DESC排序PERCENT_RANK()算出的 0.85 表示该销售员在大区内部的业绩排名处于从高到低大约 85% 的位置。注意这个表达是“位置”不是说“他超过了 85% 的人”。在没有并列值的情况下“超过 85% 的人”用CUME_DIST()会更准确。这两个表达在业务上经常被混用但代码层面必须分清楚。3.3 需求三给数据划分档位两个函数都能做但结果不同“分 A/B/C/D 档”这种需求我见过有人用PERCENT_RANK()做也有人用CUME_DIST()做都能实现但分档逻辑完全不同。用PERCENT_RANK()分档本质是按排名位置切成四段SELECT employee_name, sales_amount, CASE WHEN PERCENT_RANK() OVER (ORDER BY sales_amount DESC) 0.25 THEN A WHEN PERCENT_RANK() OVER (ORDER BY sales_amount DESC) 0.5 THEN B WHEN PERCENT_RANK() OVER (ORDER BY sales_amount DESC) 0.75 THEN C ELSE D END AS grade FROM sales_performance;用CUME_DIST()分档本质是按值覆盖范围去切SELECT employee_name, sales_amount, CASE WHEN CUME_DIST() OVER (ORDER BY sales_amount DESC) 0.25 THEN A WHEN CUME_DIST() OVER (ORDER BY sales_amount DESC) 0.5 THEN B WHEN CUME_DIST() OVER (ORDER BY sales_amount DESC) 0.75 THEN C ELSE D END AS grade FROM sales_performance;注意两段代码里PERCENT_RANK()第一个区间用的是 0.25CUME_DIST()用的是 0.25。原因在于PERCENT_RANK()第一名的值是 0CUME_DIST()第一名的值是 1/n如果都用第一行可能被排除到第二个区间之外边界逻辑就乱了。分档判断的边界条件必须结合函数取值范围去设计这是我踩过坑才记住的。3.4 需求四头部效应和二八分析CUME_DIST 更合适做精细化管理时经常要回答排名靠前的那部分数据到底覆盖了多少业务量比如“前 20% 的头部客户贡献了多少销售额”或者“价格最低的那 20% 商品覆盖了多少 SKU”。这类问题的本质是“累计覆盖”而CUME_DIST()本身就是累积分布非常适合用来筛选分位点边界。比如我想找到“销售额累计覆盖前 20% 的销售员”WITH sales_with_cume AS ( SELECT employee_name, sales_amount, CUME_DIST() OVER (ORDER BY sales_amount DESC) AS cume FROM sales_performance ) SELECT employee_name, sales_amount, cume FROM sales_with_cume WHERE cume 0.2 ORDER BY sales_amount DESC;这里用DESC排序配合cume 0.2找出来的是“业绩不低于某个水平的前 20% 人群”。如果用ASC排序配合cume 0.2找出来的就是“业绩低于 80% 人的末尾 20% 人群”。同一个函数配合不同排序方向提取的完全是两类样本。3.5 一个选择口诀我自己的判断口诀很简短问覆盖找 CUME问位置找 PERCENT要分档先定边界再选函数。一旦看清楚业务在问“累计覆盖率”还是“相对位置”选型就不会纠结。4. 边界情况与常见坑并列、NULL、小样本4.1 并列值PERCENT_RANK 的区分度会打折扣前面示例里已经看到李四和王五销售业绩相同PERCENT_RANK()返回了完全相同的结果。这本身符合标准但它带来一个问题当某个值重复出现多次时PERCENT_RANK 的中间区间会出现明显的“断层”和聚集。举个例子100 条数据里如果有一个值出现了 80 次那这 80 行的RANK()可能完全相同它们的PERCENT_RANK()就会集中在某个很小的区间里无法区分这 80 个人内部的差异。而CUME_DIST()对这些行也会给出相同的值但它的语义本来就不区分“内部差异”只是告诉你这个值覆盖了 80% 的数据所以不算缺陷。遇到大量并列数据时建议先想清楚到底要不要区分并列行。如果要区分通常应该回到RANK()或DENSE_RANK()或者干脆给并列行加二级排序键而不是指望PERCENT_RANK()来区分。4.2 NULL 排序规则不一致直接影响计算结果窗口函数在计算前先要排序有 NULL 值时不同数据库对 NULL 的默认处理完全不同数据库ORDER BY ASC 时 NULL 的位置ORDER BY DESC 时 NULL 的位置是否支持 NULLS FIRST/LASTMySQL 8.0NULL 排最前NULL 排最后不支持PostgreSQLNULL 排最后NULL 排最前支持SQL ServerNULL 视为最小值排最前NULL 视为最小值排最前不支持OracleNULL 排最后NULL 排最前支持这意味着同样的查询语句在 MySQL 和 PostgreSQL 上跑出来的CUME_DIST()第一行可能完全不同。如果你在计算销售业绩时没有对 NULL 销售额做处理CUME_DIST()的起点就会被 NULL 占据后面所有非 NULL 数据的累计分布位置都会整体偏移。我的处理习惯是计算之前先用COALESCE()或者WHERE sales_amount IS NOT NULL把数据清洗干净。如果是 PostgreSQL就直接在排序里加NULLS LAST显式声明避免依赖默认行为。4.3 样本量太小时算出来的分布没有统计意义窗口函数本身不关心样本量大小它只是按公式机械计算。但作为分析者你必须自己警惕小样本场景。比如一个PARTITION BY region分区里只有 3 个人那么PERCENT_RANK()的输出只会是 0、0.5、1 三个值如果只有 2 个人那就只有 0 和 1。这种数据拿去做绩效档位划分结果非常粗糙“0.5 的位置”到底比另一个人好多少完全说不清楚。CUME_DIST()在小样本下的表现同样有限3 条数据只能给出 0.333、0.667、1 这种颗粒度。遇到分区内行数很少的情况我的建议是要么把分区粒度放宽要么干脆放弃分布函数改用绝对值或者直接展示原始排名。4.4 单行窗口和跨分区比较的隐蔽问题一个分区里只有 1 行数据时PERCENT_RANK()的公式是 (1 - 1) / (1 - 1)分母为 0。标准规定这种情况返回 0主流的 MySQL、PostgreSQL、SQL Server 也是这么实现的但如果你用一些比较冷门的分析引擎最好先拿一条数据自测别想当然。跨分区比较更隐蔽。假设 A 分区有 100 人B 分区有 10 人A 区第 90 名和 B 区倒数第一名PERCENT_RANK()都可能落在 0.9 左右。但这两者的业务含义完全不同一个是“在 100 人里排第 90”一个是“在 10 人里基本垫底”。如果把这两个分区的PERCENT_RANK()放在一起横向比较很容易得出错误结论。跨分区比较百分位排名时必须同时展示分区内样本量和绝对排名。4.5 上线前自测用一行并列数据验证数据库实现前面说的都是标准定义但不同数据库、不同版本的实现可能藏着“惊喜”。我自己的做法是第一次在新环境里使用这两个函数时会故意造一条带并列值的数据去跑一下确认结果是否符合预期。WITH test_data AS ( SELECT 10 AS val UNION ALL SELECT 20 AS val UNION ALL SELECT 20 AS val UNION ALL SELECT 30 AS val ) SELECT val, CUME_DIST() OVER (ORDER BY val ASC) AS cume, PERCENT_RANK() OVER (ORDER BY val ASC) AS pct_rank FROM test_data;如果20对应的cume是 0.75pct_rank是 0.333那就说明实现符合标准如果pct_rank返回了 0.333 和 0.667 两个不同值说明这个引擎把PERCENT_RANK()按物理行号实现了后续所有涉及并列的分析都要重新校准。这条自测 SQL 值得存进自己的工具库定期在新的数据源上跑一遍。5. 进阶组合从“单点位置”到“整体分布”的分析5.1 六个排名窗口函数放在一张表里看CUME_DIST()和PERCENT_RANK()不孤立存在实际分析中经常和ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()一起用。我整理了一张横评表便于对比函数输出形态并列值处理典型用途ROW_NUMBER()1, 2, 3, ...不并列物理顺序分页、取前 N 条RANK()1, 1, 3, ...并列跳号竞赛排名DENSE_RANK()1, 1, 2, ...并列不跳号密度排名PERCENT_RANK()0 ~ 1同 RANK 语义相对位置归一化CUME_DIST()(0, 1]同值同结果累积分布覆盖率NTILE(n)1 ~ n尽量均分行数分桶、分位分层这几个函数在分析中的分工通常是ROW_NUMBER()负责精确行号RANK()和DENSE_RANK()负责具名排名NTILE()负责等量分桶CUME_DIST()负责累计覆盖PERCENT_RANK()负责位置归一化。一个复杂的分析需求往往需要两三个函数配合。5.2 用 PERCENT_RANK 配合 LAG/LEAD 观察排名漂移排名位置的变化趋势比某一期的绝对排名更有业务价值。比如连续几个月的销售排名若持续下滑说明团队的业绩或市场环境出现了问题。这时窗口函数嵌套子查询就非常有用了。WITH ranked_sales AS ( SELECT employee_name, sales_amount, sales_month, PERCENT_RANK() OVER ( PARTITION BY sales_month ORDER BY sales_amount DESC ) AS month_pct_rank FROM monthly_sales ) SELECT employee_name, sales_month, month_pct_rank, LAG(month_pct_rank) OVER ( PARTITION BY employee_name ORDER BY sales_month ) AS prev_month_pct, month_pct_rank - LAG(month_pct_rank) OVER ( PARTITION BY employee_name ORDER BY sales_month ) AS pct_rank_change FROM ranked_sales ORDER BY employee_name, sales_month;我一直强调窗口函数不能直接嵌套所以这里的做法是先用 CTE 算出每个月的PERCENT_RANK()第二步再用LAG()对比上个月的百分位。pct_rank_change如果是正数说明在当前月的相对位置比上个月靠后因为你用的是 DESC 排序如果是负数说明位置前移了。这个分析不需要关心具体销售额的波动幅度只看位置变化对业务决策来说非常直观。5.3 NTILE 分桶和 PERCENT_RANK 的差异分布不均匀时会给出不同结论很多人误以为NTILE(4)分出来的四等份就等同于PERCENT_RANK()小于 0.25、0.5、0.75。实际上它们只有在数据均匀分布时才近似等价。NTILE()的核心逻辑是把行数尽量均分PERCENT_RANK()和CUME_DIST()则是根据值的分布位置计算。数据倾斜时两张图的切割点完全不同。SELECT employee_name, sales_amount, NTILE(4) OVER (ORDER BY sales_amount DESC) AS quartile, PERCENT_RANK() OVER (ORDER BY sales_amount DESC) AS pct_rank, CUME_DIST() OVER (ORDER BY sales_amount DESC) AS cume FROM sales_performance ORDER BY sales_amount DESC;比如销售额呈幂律分布时头部几个大客户可能在NTILE()中被分到第 1 桶但在PERCENT_RANK()中占据很小的区间而在CUME_DIST()中覆盖了很大的累计比例。分析时要先想清楚你到底需要“把人群分成人数相同的几组”还是需要“按值的大小看分布位置”前者选NTILE()后者选PERCENT_RANK()或CUME_DIST()。5.4 一个综合示例找出连续两期排名下滑的销售员把前面几招组合起来做一个稍微完整的分析。假设monthly_sales表里存了多个销售员每月的业绩我们要找出“连续两个月相对排名下滑”的人。WITH monthly_rank AS ( SELECT employee_name, sales_month, sales_amount, CUME_DIST() OVER ( PARTITION BY sales_month ORDER BY sales_amount DESC ) AS month_cume FROM monthly_sales ), rank_lag AS ( SELECT employee_name, sales_month, month_cume, LAG(month_cume) OVER ( PARTITION BY employee_name ORDER BY sales_month ) AS prev_month_cume FROM monthly_rank ) SELECT employee_name, sales_month, month_cume, prev_month_cume FROM rank_lag WHERE prev_month_cume IS NOT NULL AND month_cume prev_month_cume ORDER BY employee_name, sales_month;这里用CUME_DIST()而不是PERCENT_RANK()是因为我想看到“业绩覆盖位置”月与月之间的累计分布变化更贴近“超过多少人”这一业务直觉。当month_cume大于prev_month_cume时说明这个月的相对覆盖率变大了也就是排名下滑了。再结合业绩环比去看基本就能定位出问题的销售员。这种组合分析方式才是窗口函数真正发挥价值的地方。不要停留在单独演示某个函数的语法试着把它们放进一个真实的分析链路里会发现很多以前要写好几段程序才能算出来的指标现在一段 SQL 就能搞定。最后分享一个自己的判断习惯。我每次写这类分布函数前都会先在纸上把业务问题翻译成一句话“我要找的是覆盖边界还是排名位置”如果要划一条线线的两侧分别是有没有达到某个水平那用CUME_DIST()如果要描述“这个人当前排到哪儿了”那用PERCENT_RANK()。这两个函数的差异就藏在公式里一个用总行数做分母一个用总行数减一但业务思维模型完全不同。想明白这个问题比背十个公式都有用。