面试必问的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;执行结果对比salespersonregionamountrow_numrank_valdense_rank_val赵六华南6000111钱七华南5500222张三华东5000333李四华东5000433王五华东40005541.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不能引用窗口函数结果正确的做法是使用子查询或CTEWITH 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导致实际获奖人数超过了预算名额就是因为没有考虑到并列情况。