一、什么是窗口函数大白话版“窗口不是窗户而是视野范围”普通聚合函数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)tWHERErn1;-- 只取每个用户的第一条最新的原理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)tWHERErn3;-- 每个部门前 3 名场景 4删除重复数据需求清理重复的用户记录只保留 ID 最小的DELETEFROMusersWHEREidIN(SELECTidFROM(SELECTid,email,ROW_NUMBER()OVER(PARTITIONBYemailORDERBYidASC)ASrnFROMusers)tWHERErn1-- 保留 rn1 的删除其他的);场景 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_asc1ANDrn_desc1;-- 去掉最低和最高场景 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、4SELECTemp_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 跳过 2DENSE 不跳-- 赵六 | 90 | 4 | 4 | 3六、性能优化建议1. 避免全表扫描-- ❌ 慢全表编号后再过滤SELECT*FROM(SELECT*,ROW_NUMBER()OVER(ORDERBYcreated_at)ASrnFROMorders)tWHERErn10;-- ✅ 快先过滤再编号SELECT*,ROW_NUMBER()OVER(ORDERBYcreated_at)ASrnFROMordersWHEREcreated_at2026-01-01ORDERBYcreated_atLIMIT10;2. 合理使用索引-- 为 PARTITION BY 和 ORDER BY 字段创建索引CREATEINDEXidx_orders_user_createdONorders(user_id,created_atDESC);-- 这样查询会很快SELECT*FROM(SELECT*,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYcreated_atDESC)ASrnFROMorders)tWHERErn1;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)tWHERErn1;错误 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表名)tWHERErn1;-- 或其他条件