SQL窗口函数实战_ROW_NUMBER_RANK_DENSE_RANK

SQL窗口函数实战ROW_NUMBER、RANK、DENSE_RANK 到底该用哪个这篇文章不是教科书是我当年做数据分析时踩过坑后的总结先把最容易搞混的三个窗口函数讲清楚。做数据分析那会儿我特别喜欢用窗口函数。倒不是因为它多高级而是有些需求用它写起来太顺手了——比如每个分组取前 N 条“累计求和”“排名次”。但说实话我刚开始学的时候也晕过。ROW_NUMBER、RANK、DENSE_RANK三个名字长得差不多出来的结果老是差那么一点点。今天就用一个学生成绩表把这三个函数彻底讲明白。先建一张学生成绩表CREATETABLEscores(score_idINTPRIMARYKEY,class_nameVARCHAR(20),student_nameVARCHAR(20),scoreINT);INSERTINTOscoresVALUES(1,一班,张三,90),(2,一班,李四,85),(3,一班,王五,90),(4,一班,赵六,78),(5,二班,孙七,92),(6,二班,周八,88),(7,二班,吴九,92),(8,二班,郑十,76);注意一班有两个 90 分二班也有两个 92 分。这种并列分数是后面讲排名的关键。1. ROW_NUMBER最简单的排名并列也硬排需求按班级给学生成绩排名每个班排一名。SELECTclass_name,student_name,score,ROW_NUMBER()OVER(PARTITIONBYclass_nameORDERBYscoreDESC)ASrnFROMscores;结果大概长这样class_namestudent_namescorern一班张三901一班王五902一班李四853一班赵六784看到没两个 90 分张三排第 1王五排第 2。ROW_NUMBER 不管并列硬生生给每个人一个唯一编号。我什么时候用它取每个班第一名的时候最干净。因为 ROW_NUMBER 不会并列所以用WHERE rn 1永远不会出现多条结果。SELECT*FROM(SELECTclass_name,student_name,score,ROW_NUMBER()OVER(PARTITIONBYclass_nameORDERBYscoreDESC)ASrnFROMscores)tWHERErn1;踩坑记录有一次我做每个区最新一条数据用了 RANK结果同一个区出来了两条一样的记录下游去重搞了半天。后来改成 ROW_NUMBER 才干净。2. RANK真正的比赛排名并列跳号同样是按班级排名但用 RANKSELECTclass_name,student_name,score,RANK()OVER(PARTITIONBYclass_nameORDERBYscoreDESC)ASrkFROMscores;class_namestudent_namescorerk一班张三901一班王五901一班李四853一班赵六784两个 90 分并列第 1下一名直接跳到第 3。这就是体育比赛里的排名逻辑——两个冠军没有亚军。我什么时候用它做比赛排名榜单这种场景。比如公司销售业绩排名两个销售并列第一那第三名就是第三名没有第二名。3. DENSE_RANK并列不跳号SELECTclass_name,student_name,score,DENSE_RANK()OVER(PARTITIONBYclass_nameORDERBYscoreDESC)ASdrFROMscores;class_namestudent_namescoredr一班张三901一班王五901一班李四852一班赵六783两个 90 分并列第 1下一名是第 2。我什么时候用它做等级划分。比如成绩前 2 名的同学评优如果按 DENSE_RANK两个 90 分都算第 1 等级85 分算第 2 等级。这样更符合按成绩档次而不是按名次的需求。一张表看懂三个函数的区别函数并列怎么处理是否跳号典型使用场景ROW_NUMBER硬排不并列不跳取每组前 N 条要求唯一RANK并列同一名次跳号比赛排名、榜单DENSE_RANK并列同一名次不跳号等级划分、档次分组4. 窗口函数里最容易漏的PARTITION BY很多人第一次写窗口函数会写成这样-- 错的写法没有 PARTITION BY所有数据一起排名SELECTstudent_name,score,ROW_NUMBER()OVER(ORDERBYscoreDESC)ASrnFROMscores;这样出来的结果是所有学生混在一起排名不分班级。如果你要的是每个班内部排名必须加PARTITION BY class_name。-- 对的写法SELECTstudent_name,score,ROW_NUMBER()OVER(PARTITIONBYclass_nameORDERBYscoreDESC)ASrnFROMscores;我的经验是先想清楚你的窗口范围是什么再把 PARTITION BY 写上去。窗口范围是整个表还是一个组这个想错了结果一定不对。5. 实战取每个班级前 2 名这个需求在面试和实际工作中都很常见。用 ROW_NUMBER 最稳SELECT*FROM(SELECTclass_name,student_name,score,ROW_NUMBER()OVER(PARTITIONBYclass_nameORDERBYscoreDESC)ASrnFROMscores)tWHERErn2;如果需求是每个班成绩前 2 个档次那就用 DENSE_RANK如果是比赛前三用 RANK。课后练习下面这三道题你自己敲一遍比看十遍都强练习 1查询每个班级成绩最高的学生只取 1 人并列时任意取一个。练习 2查询每个班级排名前 2 的学生并列时都要列出来。练习 3给每个班级学生的成绩划分等级前 1/3 为 A中间 1/3 为 B后 1/3 为 C提示用 NTILE(3)。答案我放在评论区做完再对照。写在最后窗口函数看起来就那几个关键字但真正用好关键是要想清楚你的窗口在哪里。是先分组PARTITION BY再排序还是直接全局排序是要唯一编号、跳跃排名还是连续排名我刚工作的时候最怕的不是不会写而是三个函数长得像、用法又像最后选错了还不知道。希望这篇文章能帮你把这三个一次分清楚。下一篇我打算写窗口函数里的累计求和与滑动平均——做时间序列分析时离不开。如果你也有想聊的 SQL 话题评论区告诉我。