SQL窗口函数实战：row_number、rank、dense_rank的排序逻辑与业务场景选择

1. 窗口函数入门为什么需要row_number、rank和dense_rank刚接触SQL窗口函数时很多人都会有这样的疑问既然已经有ORDER BY了为什么还需要row_number、rank和dense_rank这些排序函数这个问题我也曾经困惑过直到在实际项目中遇到了几个典型场景才恍然大悟。想象一下这样的业务需求你需要给销售团队做月度业绩排名前三名有奖金。如果直接用ORDER BY当出现并列第二名时怎么办奖金该发给几个人又或者在做用户活跃度分析时需要筛选出每个地区活跃度前10%的用户这个前10%该如何准确定义这些场景下简单的ORDER BY就显得力不从心了。窗口函数的精髓在于它能在保留原始数据行的同时为每行数据计算一个排名值。这个排名值可以像标签一样附加在原始数据上让我们既能查看完整数据又能知道每条记录在排序中的位置。我刚开始使用时经常混淆这三个函数直到做了个简单的测试表才彻底明白-- 测试数据有重复分数的情况 CREATE TABLE student_scores ( student_id INT, name VARCHAR(50), score INT ); INSERT INTO student_scores VALUES (1, 张三, 90), (2, 李四, 85), (3, 王五, 90), (4, 赵六, 80), (5, 钱七, 85);当我对这个表分别用三个函数按分数降序排名时得到了完全不同的结果。这个简单的例子让我意识到选择哪种排序函数本质上取决于业务对相同值如何处理的需求。2. 三大排序函数的核心区别2.1 row_number简单的行号分配row_number是最直接的排序函数它的逻辑简单粗暴按照指定的排序规则给每一行分配一个唯一的序号即使值完全相同也会分配不同序号。在实际项目中我发现它特别适合需要绝对唯一排名的场景。比如电商平台的订单编号生成即使两个订单在同一毫秒创建我们也需要给它们分配不同的序号。row_number保证了这个需求的实现SELECT student_id, name, score, row_number() OVER (ORDER BY score DESC) AS rank FROM student_scores;执行结果会是student_id | name | score | rank ----------------------------- 1 | 张三 | 90 | 1 3 | 王五 | 90 | 2 2 | 李四 | 85 | 3 5 | 钱七 | 85 | 4 4 | 赵六 | 80 | 5注意看虽然张三和王五分数相同但他们得到了不同的排名。这在需要严格区分每条记录的场合非常有用。2.2 rank体育比赛式的排名rank函数的表现更像我们熟悉的体育比赛排名相同分数的人获得相同名次但会留下空缺。这种排名方式在报表展示时更符合人类直觉。继续用学生成绩的例子SELECT student_id, name, score, rank() OVER (ORDER BY score DESC) AS rank FROM student_scores;结果会是student_id | name | score | rank ----------------------------- 1 | 张三 | 90 | 1 3 | 王五 | 90 | 1 2 | 李四 | 85 | 3 5 | 钱七 | 85 | 3 4 | 赵六 | 80 | 5这里出现了两个第一名然后直接跳到第三名没有第二名这就是rank的特点。我在做销售排行榜时就遇到过这种情况两个销售并列第一下一个直接是第三名虽然逻辑正确但有些业务方会觉得这样看起来不连续。2.3 dense_rank连续的排名dense_rank解决了rank的不连续问题。当数值相同时给相同排名但后续排名会连续而不会跳过数字。这在需要美观连续排名的报表中特别有用。SELECT student_id, name, score, dense_rank() OVER (ORDER BY score DESC) AS rank FROM student_scores;结果student_id | name | score | rank ----------------------------- 1 | 张三 | 90 | 1 3 | 王五 | 90 | 1 2 | 李四 | 85 | 2 5 | 钱七 | 85 | 2 4 | 赵六 | 80 | 3现在排名变成了1,1,2,2,3看起来更加紧凑。我在做会员等级划分时就采用了dense_rank因为领导希望等级编号是连续的不要出现空缺。3. 典型业务场景下的函数选择3.1 排行榜场景rank vs dense_rank在做各类排行榜时rank和dense_rank是最常用的两个函数。根据我的经验选择哪个主要取决于业务需求如果业务接受并列后跳过名次如奥运奖牌榜用rank如果业务需要名次连续不间断如内部员工绩效排名用dense_rank举个实际案例某游戏公司要做玩家积分榜前10名有奖励。如果使用rank当有3个玩家并列第一时第4名玩家会显示为第4名还是第2名这个细节需要与产品经理明确-- 方案1允许名次跳跃 SELECT player_id, score, rank() OVER (ORDER BY score DESC) AS rank FROM player_scores WHERE rank 10; -- 注意实际执行会报错需要子查询 -- 正确写法 SELECT * FROM ( SELECT player_id, score, rank() OVER (ORDER BY score DESC) AS rank FROM player_scores ) t WHERE rank 10;3.2 分组Top N查询row_number的绝对优势当需要从每个分组中选取前N条记录时row_number是唯一选择。因为它的排名绝对不会重复可以精确控制每个分组取多少条。我在电商数据分析中经常用这个功能比如找出每个商品类别下销量前3的产品SELECT * FROM ( SELECT product_id, category, sales, row_number() OVER (PARTITION BY category ORDER BY sales DESC) AS rank FROM products ) t WHERE rank 3;这里不能用rank或dense_rank因为如果同一类别下有多个产品销量相同可能会返回多于3条记录。曾经有个同事用了rank结果在某些类别返回了5条记录导致下游报表出错。3.3 数据去重与抽样row_number的妙用row_number在数据清洗中也有独特价值。比如从重复数据中保留最新的一条-- 保留每个用户最近的一次登录记录 DELETE FROM user_logins WHERE (user_id, login_time) NOT IN ( SELECT user_id, login_time FROM ( SELECT user_id, login_time, row_number() OVER (PARTITION BY user_id ORDER BY login_time DESC) AS rn FROM user_logins ) t WHERE rn 1 );这个技巧我在数据仓库ETL过程中经常使用比用GROUP BY更灵活因为可以基于任意排序条件选择要保留的记录。4. 高级应用与性能考量4.1 组合使用多种排序函数在实际复杂报表中我经常组合使用多个排序函数。比如既要显示当前排名又要显示与上一名的差距SELECT player_id, score, rank() OVER (ORDER BY score DESC) AS current_rank, score - LAG(score, 1) OVER (ORDER BY score DESC) AS gap_with_previous FROM player_scores;这种组合用法可以创造出非常丰富的分析视角满足不同业务角色的需求。4.2 分区排序的威力PARTITION BY子句让窗口函数更加强大。我做过一个销售分析报表需要同时显示每个销售人员在团队中的排名在全国同职位人员中的排名在所在地区的排名SELECT salesperson_id, team_id, region, position, sales_amount, rank() OVER (PARTITION BY team_id ORDER BY sales_amount DESC) AS team_rank, rank() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS region_rank, rank() OVER (PARTITION BY position ORDER BY sales_amount DESC) AS position_rank FROM sales_data;这种多维度排名可以帮助管理者从不同角度评估员工表现。4.3 性能优化实践窗口函数虽然强大但在大数据量下可能成为性能瓶颈。我总结了几点优化经验尽量减少窗口定义中的排序字段数量对分区字段建立合适的索引避免在窗口函数中使用复杂计算考虑先过滤数据再应用窗口函数曾经优化过一个执行缓慢的查询原来是在窗口函数中使用了复杂的JSON解析改为先解析再排序后性能提升了10倍。