PostgreSQL PERCENT_RANK() 窗口函数完全解析
一、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)tWHEREpr<=0.1;-- 前 10%其他用法：
-- 后 20%（需要改进的员工）WHEREpr>=0.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.25THEN'Q1 前25%'WHENPERCENT_RANK()OVER(ORDERBYsales_amountDESC)<0.50THEN'Q2 25-50%'WHENPERCENT_RANK()OVER(ORDERBYsales_amountDESC)<0.75THEN'Q3 50-75%'ELSE'Q4 后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.1THEN'S 级 (前10%)'WHENPERCENT_RANK()OVER(ORDERBYsales_amountDESC)<=0.3THEN'A 级 (10-30%)'WHENPERCENT_RANK()OVER(ORDERBYsales_amountDESC)<=0.6THEN'B 级 (30-60%)'WHENPERCENT_RANK()OVER(ORDERBYsales_amountDESC)<=0.9THEN'C 级 (60-90%)'ELSE'D 级 (后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/4=0.00 | 0.00-- 李四 | 95 | 2 | 5 | 1/4=0.25 | 0.25-- 王五 | 90 | 3 | 5 | 2/4=0.50 | 0.50-- 赵六 | 85 | 4 | 5 | 3/4=0.75 | 0.75-- 钱七 | 80 | 5 | 5 | 4/4=1.00 | 1.00特殊情况：
- 如果只有 1 行：
(1-1)/(1-1) = 0/0，PostgreSQL 返回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,CASEWHENpr<=0.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 名 pr=0，最后一名 pr=1-- ORDER BY ASC（升序）：第 1 名 pr=1，最后一名 pr=0错误 3：在 WHERE 中直接使用
-- ❌ 错误SELECT*FROMemployeesWHEREPERCENT_RANK()OVER(ORDERBYsalaryDESC)<=0.1;-- ✅ 正确：用子查询SELECT*FROM(SELECT*,PERCENT_RANK()OVER(ORDERBYsalaryDESC)ASprFROMemployees)tWHEREpr<=0.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) | 0 | 1 | 并列值相同 |
CUME_DIST() | 小于等于当前值的行数/n | 1/n | 1 | 并列值相同 |
对比示例：
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)tWHEREpr<=0.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)tWHEREpr<=0.2;-- 3. 部门内对比SELECTdept,emp,sales,ROUND(PERCENT_RANK()OVER(PARTITIONBYdeptORDERBYsalesDESC)::DECIMAL*100,2)ASdept_pctFROMemployees;-- 4. 异常值检测SELECT*FROM(SELECT*,PERCENT_RANK()OVER(ORDERBYamountASC)ASprFROMtransactions)tWHEREpr<0.01ORpr>0.99;