PostgreSQL ROW_NUMBER() 窗口函数完全解析
一、什么是"窗口函数"？（大白话版）
“窗口"不是窗户，而是"视野范围”！
- 普通聚合函数（SUM、COUNT）：把多行压缩成一行，你看不到原始数据了
- 窗口函数（ROW_NUMBER）：在每行旁边附加计算结果，原始数据还在
比喻：
普通聚合：把全班成绩汇总成平均分 → 你看不出每个人的分数 窗口函数：在每个人旁边标注"班级第几名" → 既看到分数，又看到排名为什么叫"窗口"？
因为你可以定义一个"滑动窗口"（比如"当前行 + 前2行"），在这个范围内计算。
二、ROW_NUMBER() 一句话解释
给每组数据编个号：1、2、3、4…，从 1 开始连续递增。
三、9 个最实用场景
场景 1：去重（保留最新/最早的一条）
需求：用户可能有多条订单，只保留每个用户的最新订单
SELECT*FROM(SELECTuser_id,order_no,created_at,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYcreated_atDESC)ASrnFROMorders)tWHERErn=1;-- 只取每个用户的第一条（最新的）原理：
PARTITION BY user_id：按用户分组ORDER BY created_at DESC：每组内按时间倒序ROW_NUMBER()：编号 1、2、3…WHERE rn = 1：只要第一条
场景 2：分页查询（高效分页）
需求：查询第 11-20 条记录
SELECT*FROM(SELECTid,name,created_at,ROW_NUMBER()OVER(ORDERBYcreated_atDESC)ASrnFROMusers)tWHERErnBETWEEN11AND20;优势：比LIMIT/OFFSET在大数据量时更快（尤其是深分页）
场景 3：找出每组的前 N 名
需求：每个部门工资最高的 3 个人
SELECT*FROM(SELECTdept_name,emp_name,salary,ROW_NUMBER()OVER(PARTITIONBYdept_nameORDERBYsalaryDESC)ASrnFROMemployees)tWHERErn<=3;-- 每个部门前 3 名场景 4：删除重复数据
需求：清理重复的用户记录，只保留 ID 最小的
DELETEFROMusersWHEREidIN(SELECTidFROM(SELECTid,email,ROW_NUMBER()OVER(PARTITIONBYemailORDERBYidASC)ASrnFROMusers)tWHERErn>1-- 保留 rn=1 的，删除其他的);场景 5：对比当前行和上一行
需求：计算每日销售额环比增长
SELECTsale_date,daily_amount,LAG(daily_amount)OVER(ORDERBYsale_date)ASprev_day_amount,ROUND((daily_amount-LAG(daily_amount)OVER(ORDERBYsale_date))/LAG(daily_amount)OVER(ORDERBYsale_date)*100,2)ASgrowth_rateFROMdaily_sales;注意：这里用LAG()更适合，但ROW_NUMBER()也可以实现类似效果。
场景 6：标记首次/最后一次行为
需求：标记用户的首次登录和最后登录
SELECTuser_id,login_time,CASEWHENROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlogin_timeASC)=1THEN'首次登录'WHENROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlogin_timeDESC)=1THEN'最后登录'ELSE'普通登录'ENDASlogin_typeFROMuser_logins;场景 7：分组后取中间值
需求：去掉最高分和最低分，取中间的平均分
SELECTstudent_id,AVG(score)ASavg_scoreFROM(SELECTstudent_id,score,ROW_NUMBER()OVER(PARTITIONBYstudent_idORDERBYscoreASC)ASrn_asc,ROW_NUMBER()OVER(PARTITIONBYstudent_idORDERBYscoreDESC)ASrn_desc,COUNT(*)OVER(PARTITIONBYstudent_id)AStotal_countFROMexam_scores)tWHERErn_asc>1ANDrn_desc>1;-- 去掉最低和最高场景 8：检测数据连续性
需求：找出用户连续登录的天数
SELECTuser_id,login_date,login_date-(ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlogin_date)||' days')::INTERVALASgrpFROMuser_loginsGROUPBYuser_id,login_date;-- 相同的 grp 表示连续登录SELECTuser_id,MIN(login_date)ASstart_date,MAX(login_date)ASend_date,COUNT(*)ASconsecutive_daysFROM(-- 上面的子查询)tGROUPBYuser_id,grpHAVINGCOUNT(*)>=3;-- 连续登录 3 天以上场景 9：排行榜（带并列处理）
需求：生成销售排行榜，相同业绩排名相同
-- ROW_NUMBER()：即使分数相同，排名也不同（1、2、3、4）SELECTemp_name,sales_amount,ROW_NUMBER()OVER(ORDERBYsales_amountDESC)ASrankFROMsales_performance;-- 如果需要并列排名，用 RANK() 或 DENSE_RANK()-- RANK(): 1, 2, 2, 4 (跳过 3)-- DENSE_RANK(): 1, 2, 2, 3 (不跳过)四、核心语法拆解
ROW_NUMBER()OVER(PARTITIONBYcolumn1,column2-- 可选：分组依据ORDERBYcolumn3DESC-- 必填：排序规则)三个关键点：
OVER()：声明这是窗口函数PARTITION BY：可选，类似GROUP BY，但不压缩行数ORDER BY：必填，决定编号顺序
五、ROW_NUMBER vs RANK vs DENSE_RANK
| 函数 | 相同值处理 | 示例 | 适用场景 |
|---|---|---|---|
ROW_NUMBER() | 强制不同 | 1, 2, 3, 4 | 去重、分页 |
RANK() | 并列，跳号 | 1, 2, 2, 4 | 排行榜（允许空缺） |
DENSE_RANK() | 并列，不跳号 | 1, 2, 2, 3 | 排行榜（紧凑排名） |
示例对比：
SELECTname,score,ROW_NUMBER()OVER(ORDERBYscoreDESC)ASrow_num,RANK()OVER(ORDERBYscoreDESC)ASrank,DENSE_RANK()OVER(ORDERBYscoreDESC)ASdense_rankFROMstudents;-- 结果:-- name | score | row_num | rank | dense_rank-- ------+-------+---------+------+------------- 张三 | 100 | 1 | 1 | 1-- 李四 | 100 | 2 | 1 | 1 ← 并列第一-- 王五 | 95 | 3 | 3 | 2 ← RANK 跳过 2，DENSE 不跳-- 赵六 | 90 | 4 | 4 | 3六、性能优化建议
1. 避免全表扫描
-- ❌ 慢：全表编号后再过滤SELECT*FROM(SELECT*,ROW_NUMBER()OVER(ORDERBYcreated_at)ASrnFROMorders)tWHERErn<=10;-- ✅ 快：先过滤再编号SELECT*,ROW_NUMBER()OVER(ORDERBYcreated_at)ASrnFROMordersWHEREcreated_at>='2026-01-01'ORDERBYcreated_atLIMIT10;2. 合理使用索引
-- 为 PARTITION BY 和 ORDER BY 字段创建索引CREATEINDEXidx_orders_user_createdONorders(user_id,created_atDESC);-- 这样查询会很快SELECT*FROM(SELECT*,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYcreated_atDESC)ASrnFROMorders)tWHERErn=1;3. 避免不必要的 PARTITION BY
-- ❌ 如果不需要分组，不要加 PARTITION BYROW_NUMBER()OVER(PARTITIONBY1ORDERBYid)-- 多余！-- ✅ 直接全局编号ROW_NUMBER()OVER(ORDERBYid)七、常见错误
错误 1：忘记 ORDER BY
-- ❌ 错误：窗口函数必须包含 ORDER BYROW_NUMBER()OVER(PARTITIONBYuser_id)-- ✅ 正确ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYcreated_at)错误 2：在 WHERE 中直接使用
-- ❌ 错误：窗口函数不能在 WHERE 中使用SELECT*FROMordersWHEREROW_NUMBER()OVER(ORDERBYid)=1;-- ✅ 正确：用子查询SELECT*FROM(SELECT*,ROW_NUMBER()OVER(ORDERBYid)ASrnFROMorders)tWHERErn=1;错误 3：误解 PARTITION BY
-- ❌ 错误理解：以为 PARTITION BY 会分组返回SELECTuser_id,COUNT(*)FROMordersGROUPBYuser_id;-- 这才是分组-- ✅ 正确理解：PARTITION BY 不减少行数SELECTuser_id,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYid)ASrnFROMorders;-- 行数不变，只是加了编号八、记忆口诀
ROW_NUMBER 编序号，分组排序不能少 去重分页最常用，子查询里套一层 PARTITION 是分堆，ORDER 决定谁在前 WHERE 不能直接调，外层过滤才正确九、总结
核心要点
- 窗口函数= 在不压缩行的前提下，附加计算结果
- ROW_NUMBER()= 给每组数据编连续序号（1、2、3…）
- 最常用场景= 去重、分页、取前 N 名
- 必须配合=
OVER()+ORDER BY - 使用时机= 需要"组内排名"或"唯一标识"时
快速参考
-- 基本模板SELECT*FROM(SELECT字段列表,ROW_NUMBER()OVER(PARTITIONBY分组字段-- 可选ORDERBY排序字段DESC-- 必填)ASrnFROM表名)tWHERErn=1;-- 或其他条件