面试必问的SQL窗口函数：row_number、rank、dense_rank实战避坑指南
面试必问的SQL窗口函数：row_number、rank、dense_rank实战避坑指南
在技术面试中，SQL窗口函数几乎是必考内容，尤其是row_number、rank和dense_rank这三个排序函数。很多求职者虽然能背出它们的区别，但在实际应用中却经常踩坑。本文将结合LeetCode高频题和真实业务场景，带你深入理解这三个函数的使用技巧和常见陷阱。
1. 核心概念解析：三个函数的本质区别
1.1 基础定义与行为差异
这三个函数都用于为结果集中的行分配排名，但处理相同值的方式截然不同：
- row_number()：为每一行分配唯一的序号，即使值相同也会获得不同排名
- rank()：相同值获得相同排名，但会留下"空缺"（如两个第一名后直接是第三名）
- dense_rank()：相同值获得相同排名，且排名连续不跳号（如两个第一名后是第二名）
-- 示例数据 CREATE TABLE sales ( salesperson VARCHAR(50), region VARCHAR(50), amount DECIMAL(10,2) ); INSERT INTO sales VALUES ('张三', '华东', 5000), ('李四', '华东', 5000), ('王五', '华东', 4000), ('赵六', '华南', 6000), ('钱七', '华南', 5500); -- 三种排名方式对比 SELECT salesperson, region, amount, ROW_NUMBER() OVER(ORDER BY amount DESC) AS row_num, RANK() OVER(ORDER BY amount DESC) AS rank_val, DENSE_RANK() OVER(ORDER BY amount DESC) AS dense_rank_val FROM sales;执行结果对比：
| salesperson | region | amount | row_num | rank_val | dense_rank_val |
|---|---|---|---|---|---|
| 赵六 | 华南 | 6000 | 1 | 1 | 1 |
| 钱七 | 华南 | 5500 | 2 | 2 | 2 |
| 张三 | 华东 | 5000 | 3 | 3 | 3 |
| 李四 | 华东 | 5000 | 4 | 3 | 3 |
| 王五 | 华东 | 4000 | 5 | 5 | 4 |
1.2 分区排序的实际应用
窗口函数的真正威力在于PARTITION BY子句，它允许我们在不同分组内独立计算排名：
-- 按区域分组后的排名 SELECT salesperson, region, amount, ROW_NUMBER() OVER(PARTITION BY region ORDER BY amount DESC) AS region_rank FROM sales;提示：
PARTITION BY可以指定多个列，如PARTITION BY region, year，这在处理多维数据时特别有用。
2. 高频面试题实战解析
2.1 分组取Top N问题
这是面试中最常见的问题类型之一。假设我们需要找出每个区域销售额前两名的销售：
-- 正确解法：使用ROW_NUMBER WITH ranked_sales AS ( SELECT salesperson, region, amount, ROW_NUMBER() OVER(PARTITION BY region ORDER BY amount DESC) AS rank_val FROM sales ) SELECT * FROM ranked_sales WHERE rank_val <= 2;常见错误：
- 使用
RANK可能导致返回多于N条记录（如果有并列） - 忘记在子查询或CTE中先计算排名，直接尝试在WHERE中过滤
2.2 连续登录天数问题
另一个经典问题是计算用户的连续登录天数。假设有登录记录表user_logins：
-- 找出连续登录超过7天的用户 WITH login_dates AS ( SELECT user_id, login_date, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) AS row_num FROM user_logins ), date_groups AS ( SELECT user_id, login_date, DATE_ADD(login_date, INTERVAL -row_num DAY) AS group_date FROM login_dates ) SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS consecutive_days FROM date_groups GROUP BY user_id, group_date HAVING COUNT(*) >= 7;这个解决方案巧妙地利用了ROW_NUMBER来识别连续的日期序列。
3. 真实业务场景中的陷阱
3.1 销售排行榜的坑
假设你要生成月度销售排行榜，前10名获得奖励。如果简单地使用：
SELECT salesperson, amount, RANK() OVER(ORDER BY amount DESC) AS ranking FROM sales WHERE ranking <= 10; -- 错误！WHERE不能引用窗口函数结果正确的做法是使用子查询或CTE：
WITH sales_ranking AS ( SELECT salesperson, amount, DENSE_RANK() OVER(ORDER BY amount DESC) AS ranking FROM sales ) SELECT * FROM sales_ranking WHERE ranking <= 10;注意：这里使用
DENSE_RANK而非RANK，可以避免因并列导致的奖励名额不足问题。
3.2 分页查询的性能问题
在实现分页时，很多人会这样写：
-- 低效的分页写法 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(ORDER BY create_time DESC) AS rn FROM large_table ) t WHERE rn BETWEEN 10001 AND 10100;对于大数据量表，这种写法性能很差。更好的方式是：
-- 高效分页：使用keyset分页 SELECT * FROM large_table WHERE create_time < :last_seen_time ORDER BY create_time DESC LIMIT 100;4. 高级技巧与最佳实践
4.1 多维度复合排序
在实际业务中，经常需要按多个字段排序：
SELECT product_id, category, sales_volume, profit_margin, ROW_NUMBER() OVER( PARTITION BY category ORDER BY sales_volume DESC, profit_margin DESC ) AS rank_in_category FROM products;4.2 窗口帧的灵活应用
窗口函数还支持定义帧范围，这在计算移动平均等场景非常有用：
-- 计算每个销售最近3个月的平均销售额 SELECT salesperson, month, amount, AVG(amount) OVER( PARTITION BY salesperson ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg FROM monthly_sales;4.3 性能优化建议
- 索引策略：为
PARTITION BY和ORDER BY中的列创建合适索引 - 减少数据量：先过滤再排序，避免对大结果集计算排名
- 替代方案：对于简单Top N查询，有时
LIMIT可能比窗口函数更高效
-- 优化示例：先过滤再排序 WITH filtered_data AS ( SELECT * FROM large_table WHERE department = 'IT' ) SELECT *, ROW_NUMBER() OVER(ORDER BY salary DESC) AS rank_val FROM filtered_data;在实际项目中，我发现窗口函数最常见的错误是混淆它们的排名行为。特别是在处理奖励、资格筛选等场景时，选择错误的函数可能导致业务逻辑错误。例如，在一次促销活动中，我们错误地使用了RANK导致实际获奖人数超过了预算名额，就是因为没有考虑到并列情况。