一、PERCENT_RANK() 是什么一句话解释“百分比排名你在群体中的相对位置0% - 100%”就像考试后老师说“你的成绩超过了全班 80% 的同学”这就是 PERCENT_RANK。公式(当前排名 - 1) / (总行数 - 1) 张三: 第 1 名 → (1-1)/(10-1) 0/9 0.00 (0%) 李四: 第 2 名 → (2-1)/(10-1) 1/9 0.11 (11%) 王五: 第 5 名 → (5-1)/(10-1) 4/9 0.44 (44%) 赵六: 第 10 名 → (10-1)/(10-1) 9/9 1.00 (100%)特点最小值永远是0第 1 名最大值永远是1最后一名结果是小数0.00 - 1.00乘以 100 就是百分比二、和 RANK/DENSE_RANK 的区别函数返回值范围含义示例RANK()整数1, 2, 3…绝对排名第 5 名DENSE_RANK()整数1, 2, 3…紧凑排名第 5 名PERCENT_RANK()小数0.0 - 1.0相对位置超过 44% 的人直观对比SELECTname,score,RANK()OVER(ORDERBYscoreDESC)ASrank,COUNT(*)OVER()AStotal,ROUND(PERCENT_RANK()OVER(ORDERBYscoreDESC)::DECIMAL*100,2)ASpercentileFROMstudents;-- 结果假设 10 人:-- name | score | rank | total | percentile-- ---------------------------------------- 张三 | 100 | 1 | 10 | 0.00 ← 第 1 名0%-- 李四 | 95 | 2 | 10 | 11.11 ← 超过 11% 的人-- 王五 | 90 | 3 | 10 | 22.22-- ...-- 赵六 | 60 | 10 | 10 | 100.00 ← 最后一名100%三、8 个实用场景场景 1计算百分位最经典需求告诉员工你的业绩超过了公司多少比例的人SELECTemp_name,sales_amount,ROUND(PERCENT_RANK()OVER(ORDERBYsales_amountASC)::DECIMAL*100,2)ASpercentileFROMsales_performanceORDERBYpercentileDESC;解读percentile 90你的业绩超过了 90% 的人顶尖水平percentile 50你的业绩中等超过了一半的人percentile 10你的业绩较低只超过了 10% 的人注意ORDER BY ASC时数值越大percentile 越高ORDER BY DESC时相反。场景 2找出前/后 N% 的数据需求找出业绩最好的前 10% 员工SELECT*FROM(SELECTemp_name,sales_amount,PERCENT_RANK()OVER(ORDERBYsales_amountDESC)ASprFROMsales_performance)tWHEREpr0.1;-- 前 10%其他用法-- 后 20%需要改进的员工WHEREpr0.8-- 中间 60%中等水平WHEREprBETWEEN0.2AND0.8-- 去掉最高和最低各 5%排除异常值WHEREprBETWEEN0.05AND0.95场景 3分组百分位部门内对比需求计算每个员工在部门内的百分位SELECTdept_name,emp_name,sales_amount,ROUND(PERCENT_RANK()OVER(PARTITIONBYdept_nameORDERBYsales_amountDESC)::DECIMAL*100,2)ASdept_percentileFROMemployeesORDERBYdept_name,dept_percentileDESC;结果示例dept_name | emp_name | sales_amount | dept_percentile -------------------------------------------------- 销售部 | 张三 | 100000 | 0.00 ← 销售部第 1 销售部 | 李四 | 95000 | 33.33 ← 超过销售部 33% 的人 技术部 | 王五 | 80000 | 0.00 ← 技术部第 1 技术部 | 赵六 | 75000 | 50.00 ← 技术部中等优势跨部门对比更公平销售部的 10 万可能不如技术部的 8 万难拿场景 4标准化不同量纲的数据需求把销售额万元和客户数个统一成百分位方便综合评分SELECTemp_name,sales_amount,customer_count,ROUND(PERCENT_RANK()OVER(ORDERBYsales_amountDESC)::DECIMAL*100,2)ASsales_pct,ROUND(PERCENT_RANK()OVER(ORDERBYcustomer_countDESC)::DECIMAL*100,2)AScustomer_pct,ROUND((PERCENT_RANK()OVER(ORDERBYsales_amountDESC)PERCENT_RANK()OVER(ORDERBYcustomer_countDESC))/2*100,2)AScombined_scoreFROMemployee_performance;为什么用 PERCENT_RANK销售额范围10万 - 1000万客户数范围5 - 500直接相加没意义转换成百分位后可以加权平均场景 5检测数据分布需求分析销售业绩的分布是否均匀SELECTCASEWHENPERCENT_RANK()OVER(ORDERBYsales_amountDESC)0.25THENQ1 前25%WHENPERCENT_RANK()OVER(ORDERBYsales_amountDESC)0.50THENQ2 25-50%WHENPERCENT_RANK()OVER(ORDERBYsales_amountDESC)0.75THENQ3 50-75%ELSEQ4 后25%ENDASquartile,COUNT(*)ASemp_count,AVG(sales_amount)ASavg_sales,MIN(sales_amount)ASmin_sales,MAX(sales_amount)ASmax_salesFROMsales_performanceGROUPBYquartileORDERBYquartile;用途快速了解业绩集中度是否有二八定律现象场景 6动态阈值划分等级需求根据实际分布划分 ABCD 等级而非固定阈值SELECTemp_name,sales_amount,CASEWHENPERCENT_RANK()OVER(ORDERBYsales_amountDESC)0.1THENS 级 (前10%)WHENPERCENT_RANK()OVER(ORDERBYsales_amountDESC)0.3THENA 级 (10-30%)WHENPERCENT_RANK()OVER(ORDERBYsales_amountDESC)0.6THENB 级 (30-60%)WHENPERCENT_RANK()OVER(ORDERBYsales_amountDESC)0.9THENC 级 (60-90%)ELSED 级 (后10%)ENDASgradeFROMsales_performance;优势自动适应数据变化不需要手动调整阈值场景 7异常值检测需求找出极端高或极端低的订单金额SELECTorder_no,amount,ROUND(PERCENT_RANK()OVER(ORDERBYamountASC)::DECIMAL*100,2)ASpercentileFROMordersWHEREPERCENT_RANK()OVER(ORDERBYamountASC)0.01-- 最低 1%ORPERCENT_RANK()OVER(ORDERBYamountASC)0.99;-- 最高 1%用途风控系统识别可疑交易场景 8生成累积分布图数据需求为报表生成累积分布数据SELECTROUND(PERCENT_RANK()OVER(ORDERBYsales_amountASC)::DECIMAL*100,2)ASx_axis,sales_amountASy_axisFROMsales_performanceORDERBYx_axis;用途在 Excel 或 BI 工具中绘制累积分布曲线四、核心语法PERCENT_RANK()OVER(PARTITIONBYcolumn1,column2-- 可选分组依据ORDERBYcolumn3ASC/DESC-- 必填排序规则)关键点不需要参数PERCENT_RANK()括号里是空的必须配合 OVER()声明这是窗口函数ORDER BY 必填决定排名方向返回值是小数0.0 - 1.0通常乘以 100 转成百分比五、计算公式详解PERCENT_RANK (rank - 1) / (total_rows - 1) 其中 - rank RANK() 的值从 1 开始 - total_rows 窗口内的总行数示例推导-- 假设有 5 行数据SELECTname,score,RANK()OVER(ORDERBYscoreDESC)ASrank,COUNT(*)OVER()AStotal,(RANK()OVER(ORDERBYscoreDESC)-1)::DECIMAL/(COUNT(*)OVER()-1)ASmanual_pr,PERCENT_RANK()OVER(ORDERBYscoreDESC)ASauto_prFROMstudents;-- 结果:-- name | score | rank | total | manual_pr | auto_pr-- ------------------------------------------------ 张三 | 100 | 1 | 5 | 0/40.00 | 0.00-- 李四 | 95 | 2 | 5 | 1/40.25 | 0.25-- 王五 | 90 | 3 | 5 | 2/40.50 | 0.50-- 赵六 | 85 | 4 | 5 | 3/40.75 | 0.75-- 钱七 | 80 | 5 | 5 | 4/41.00 | 1.00特殊情况如果只有 1 行(1-1)/(1-1) 0/0PostgreSQL 返回NULL如果有并列使用RANK()的排名会跳号六、性能优化1. 避免重复计算-- ❌ 慢多次调用 PERCENT_RANKSELECTemp_name,PERCENT_RANK()OVER(ORDERBYsalesDESC)ASpr,CASEWHENPERCENT_RANK()OVER(ORDERBYsalesDESC)0.1THEN优秀ELSE普通ENDASlevelFROMemployees;-- ✅ 快用子查询或 CTEWITHrankedAS(SELECTemp_name,sales,PERCENT_RANK()OVER(ORDERBYsalesDESC)ASprFROMemployees)SELECTemp_name,pr,CASEWHENpr0.1THEN优秀ELSE普通ENDASlevelFROMranked;2. 合理使用索引-- 为 ORDER BY 字段创建索引CREATEINDEXidx_employees_salesONemployees(sales_amount);-- 为 PARTITION BY ORDER BY 创建复合索引CREATEINDEXidx_emp_dept_salesONemployees(dept_id,sales_amount);七、常见错误错误 1忘记乘以 100-- ❌ 不直观0.85 是什么意思SELECTPERCENT_RANK()OVER(ORDERBYscore)ASprFROMstudents;-- ✅ 清晰85.00% 一目了然SELECTROUND(PERCENT_RANK()OVER(ORDERBYscore)::DECIMAL*100,2)ASpercentileFROMstudents;错误 2混淆 ASC 和 DESC-- 场景分数越高越好SELECTname,score,PERCENT_RANK()OVER(ORDERBYscoreDESC)ASpr_desc,-- 高分 pr 低PERCENT_RANK()OVER(ORDERBYscoreASC)ASpr_asc-- 高分 pr 高FROMstudents;-- 结果:-- name | score | pr_desc | pr_asc-- -------------------------------- 张三 | 100 | 0.00 | 1.00 ← 最高分-- 李四 | 60 | 1.00 | 0.00 ← 最低分-- 记忆技巧-- ORDER BY DESC降序第 1 名 pr0最后一名 pr1-- ORDER BY ASC升序第 1 名 pr1最后一名 pr0错误 3在 WHERE 中直接使用-- ❌ 错误SELECT*FROMemployeesWHEREPERCENT_RANK()OVER(ORDERBYsalaryDESC)0.1;-- ✅ 正确用子查询SELECT*FROM(SELECT*,PERCENT_RANK()OVER(ORDERBYsalaryDESC)ASprFROMemployees)tWHEREpr0.1;错误 4单行数据返回 NULL-- 如果窗口内只有 1 行SELECTPERCENT_RANK()OVER()ASprFROMsingle_row_table;-- 结果pr NULL因为除以 0-- ✅ 处理用 COALESCESELECTCOALESCE(PERCENT_RANK()OVER(),0)ASprFROMsingle_row_table;八、PERCENT_RANK vs CUME_DIST这两个函数很像但有细微差别函数公式最小值最大值并列处理PERCENT_RANK()(rank-1)/(n-1)01并列值相同CUME_DIST()小于等于当前值的行数/n1/n1并列值相同对比示例SELECTname,score,ROUND(PERCENT_RANK()OVER(ORDERBYscoreDESC)::DECIMAL*100,2)ASpr,ROUND(CUME_DIST()OVER(ORDERBYscoreDESC)::DECIMAL*100,2)AScdFROMstudents;-- 结果假设有并列:-- name | score | pr | cd-- ---------------------------- 张三 | 100 | 0.00 | 33.33 ← 3 个人并列第 1-- 李四 | 100 | 0.00 | 33.33-- 王五 | 100 | 0.00 | 33.33-- 赵六 | 95 | 37.50 | 66.67-- 钱七 | 90 | 75.00 | 100.00-- 区别-- PERCENT_RANK 基于排名位置-- CUME_DIST 基于累积比例九、记忆口诀PERCENT_RANK 百分位相对位置看得清 零到一百是范围小数乘百变百分比 前百分之几好判断异常检测也轻松 ASC DESC 要注意高低方向别搞混十、总结核心要点PERCENT_RANK() 百分比排名0% - 100%适用场景 百分位计算、前 N% 筛选、标准化数据、异常检测返回值 小数 0.0 - 1.0通常乘以 100 转成百分比计算公式 (排名 - 1) / (总行数 - 1)使用时机 需要相对位置而非绝对排名时快速参考-- 基本模板SELECT字段列表,ROUND(PERCENT_RANK()OVER(PARTITIONBY分组字段-- 可选ORDERBY排序字段DESC-- 必填)::DECIMAL*100,2)ASpercentileFROM表名;-- 前 10%SELECT*FROM(SELECT*,PERCENT_RANK()OVER(ORDERBYsalesDESC)ASprFROMemployees)tWHEREpr0.1;-- 分组百分位SELECTdept,name,sales,ROUND(PERCENT_RANK()OVER(PARTITIONBYdeptORDERBYsalesDESC)::DECIMAL*100,2)ASpctFROMemployees;实战速查-- 1. 计算百分位SELECTname,score,ROUND(PERCENT_RANK()OVER(ORDERBYscoreDESC)::DECIMAL*100,2)||%ASpercentileFROMstudents;-- 2. 找前 20%SELECT*FROM(SELECT*,PERCENT_RANK()OVER(ORDERBYrevenueDESC)ASprFROMcompanies)tWHEREpr0.2;-- 3. 部门内对比SELECTdept,emp,sales,ROUND(PERCENT_RANK()OVER(PARTITIONBYdeptORDERBYsalesDESC)::DECIMAL*100,2)ASdept_pctFROMemployees;-- 4. 异常值检测SELECT*FROM(SELECT*,PERCENT_RANK()OVER(ORDERBYamountASC)ASprFROMtransactions)tWHEREpr0.01ORpr0.99;