SQL窗口函数：ROW_NUMBER、RANK、DENSE_RANK与NTILE核心用法与实战

1. 项目概述为什么SQL排序函数是数据处理的“瑞士军刀”在数据库的日常操作里排序和排名几乎是绕不开的需求。无论是生成销售排行榜、计算学生成绩排名还是对数据进行分页处理我们都需要一种高效、灵活的方式来为数据集中的每一行赋予一个有序的标识。很多朋友刚开始接触SQL时一提到排序可能立刻想到的就是ORDER BY。没错ORDER BY能帮我们把结果集按照某个字段排好序展示出来但它有一个“硬伤”它只改变了结果的显示顺序并没有给每一行数据一个明确的、可以用于后续逻辑判断的“序号”。想象一下这个场景老板让你从销售数据中找出每个地区销售额排名前三的销售代表。你用ORDER BY 销售额 DESC排好了序眼睛盯着屏幕手动去数第一、第二、第三如果数据有几千行或者需要定期自动跑这个报表这种方法显然不现实。这时窗口函数中的排序函数就该登场了。它们就像是给排好队的每个人发了一个号码牌这个号码牌序号直接作为结果集的一列存在你可以用它来做筛选、连接或者其他更复杂的分析。今天要聊的ROW_NUMBER()、RANK()、DENSE_RANK()以及常常被忽略但很有用的NTILE()就是这组“发号码牌”的核心工具。掌握它们意味着你能用更简洁清晰的SQL语句解决那些原本需要多层子查询或复杂程序逻辑才能搞定的问题真正把数据玩的转。2. 核心排序函数深度解析与对比SQL中的排序函数属于“窗口函数”的范畴。窗口函数的核心思想是在保持原有行数据不变的前提下在某个特定的数据窗口由OVER子句定义内进行计算。排序函数就是这个计算逻辑为“赋予序号”的特例。理解它们的关键在于吃透OVER子句和各个函数在处理“并列”情况时的细微差别。2.1 ROW_NUMBER最严格的唯一序号生成器ROW_NUMBER()函数的功能最直观在指定的窗口内为每一行分配一个唯一的、连续的整数序号从1开始递增。它的核心规则是即使两行数据在排序字段上完全相等即并列ROW_NUMBER()也会强制给出不同的序号。这个序号的生成顺序在排序值相同的情况下是不确定的除非你在ORDER BY中提供了额外的、能唯一区分行的排序列。基本语法ROW_NUMBER() OVER ( [PARTITION BY partition_column1, partition_column2, ...] ORDER BY sort_column1 [ASC|DESC], sort_column2 [ASC|DESC], ... ) AS row_numPARTITION BY可选。用于将数据分成不同的“组”或“分区”。ROW_NUMBER()会在每个分区内独立地从1开始重新编号。如果省略则整个结果集视为一个分区。ORDER BY必需。定义在每个分区内行与行之间的排序顺序。序号将根据这个顺序生成。典型应用场景与实操数据去重删除重复项这是ROW_NUMBER()一个非常经典的应用。假设有一张orders表由于系统原因可能存在完全重复的记录所有字段值相同。我们只想保留一条。WITH DuplicateCTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id, customer_id, order_date, amount ORDER BY (SELECT NULL)) AS rn FROM orders ) DELETE FROM DuplicateCTE WHERE rn 1;注意ORDER BY (SELECT NULL)在这里表示排序顺序不重要因为所有字段都相同。关键是PARTITION BY包含了所有需要去重的列。这样完全相同的行会被分到同一个分区并得到rn1,2,3...删除rn1的行即可。高效分页查询在Web应用或API中实现“下一页”功能。相比用LIMIT ... OFFSET在大数据量时性能差的问题使用ROW_NUMBER()或更好的OFFSET-FETCH是更优选择。-- 假设获取按时间倒序排列的第21到30条文章 WITH PagedData AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY publish_time DESC) AS seq FROM articles ) SELECT * FROM PagedData WHERE seq BETWEEN 21 AND 30;实操心得ROW_NUMBER()生成的序号是确定性的吗不一定。当ORDER BY子句中的列不能唯一确定行顺序时即存在并列数据库在不同时间执行可能会为并列的行分配不同的序号。如果你需要绝对确定性的序号必须在ORDER BY中包含一个唯一键如主键ID。在PARTITION BY中使用它进行分组内排序时性能开销需要留意。如果分区数据量极大且没有合适的索引支持可能会成为性能瓶颈。2.2 RANK 与 DENSE_RANK处理并列排名的兄弟函数当排序字段值出现相同时如何分配序号RANK()和DENSE_RANK()给出了两种不同的策略。它们都会给并列的行分配相同的序号但后续序号的增长方式不同。RANK()函数规则排序值相同的行获得相同排名并且下一个排名数字会“跳跃”。即如果有并列第1名那么下一行的排名就是第3名跳过了第2名。类比就像学校运动会颁奖如果有两个并列冠军金牌那么下一个名次就是季军铜牌亚军银牌位置空着。DENSE_RANK()函数规则排序值相同的行获得相同排名但下一个排名数字是连续的。即如果有并列第1名那么下一行的排名就是第2名。类比更像是班级内部的学号分配成绩并列第一的同学都算1号下一个成绩的同学就是2号序号连续。为了彻底弄清区别我们用一个简单的成绩表scores来演示student_idscore195292392488585SELECT student_id, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, RANK() OVER (ORDER BY score DESC) AS rank, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank FROM scores;查询结果如下表所示student_idscorerow_numrankdense_rank195111292222392322488443585554场景化选择指南何时用RANK()当你需要反映“位置”的跳跃时。例如计算选手在比赛中的官方排名并列会导致名次空缺这符合大多数体育赛事的排名认知。何时用DENSE_RANK()当你需要连续的、用于分类或分组的序号时。例如将员工按绩效得分划分为“A级前10%”、“B级10%-30%”等。使用DENSE_RANK()可以确保等级之间没有空档便于后续计算百分比或分配等级标签。-- 使用DENSE_RANK进行绩效分级 WITH RankedEmployees AS ( SELECT employee_id, performance_score, DENSE_RANK() OVER (ORDER BY performance_score DESC) AS perf_rank, COUNT(*) OVER () AS total_count FROM employee_performance ) SELECT employee_id, performance_score, CASE WHEN perf_rank / total_count 0.1 THEN A WHEN perf_rank / total_count 0.3 THEN B ELSE C END AS performance_grade FROM RankedEmployees;2.3 NTILE数据等分与分桶的利器NTILE(n)函数可能不如前三个常用但其功能独特且强大。它的作用是将有序分区中的行尽可能平均地分配到指定数量的“桶”中并为每一行标记其所属的桶编号从1开始。基本语法NTILE(4) OVER (ORDER BY sales_amount DESC) AS quartile这行代码会按销售额降序排列然后将所有销售记录分成4个桶即四分位每个记录会被标记为属于第1、2、3或4个桶。核心应用场景数据分位数计算如四分位、十分位这是NTILE()最典型的用途。用于数据分析中的离散化识别数据的分布情况。-- 将产品按销售额分成4个等级四分位 SELECT product_id, sales_amount, NTILE(4) OVER (ORDER BY sales_amount DESC) AS sales_quartile FROM product_sales;结果中sales_quartile1代表销售额最高的前25%的产品。均匀抽样当需要从一个大表中按照某种顺序均匀抽取样本时可以使用NTILE()。例如想按时间顺序将日志均匀分成100份然后从每份中取第一条记录。WITH LogBuckets AS ( SELECT *, NTILE(100) OVER (ORDER BY log_time) AS bucket_id FROM huge_log_table ) SELECT * FROM LogBuckets WHERE bucket_id 1; -- 获取第一个桶的所有数据近似1%的均匀样本 -- 或者如果想每个桶只取一个样本可以结合ROW_NUMBER WITH LogBuckets AS ( SELECT *, NTILE(100) OVER (ORDER BY log_time) AS bucket_id, ROW_NUMBER() OVER (PARTITION BY NTILE(100) OVER (ORDER BY log_time) ORDER BY log_time) AS rn_in_bucket FROM huge_log_table ) SELECT * FROM LogBuckets WHERE rn_in_bucket 1;注意事项与避坑“尽可能平均”的含义如果总行数N不能被桶数n整除那么前N % n个桶会比后面的桶多一行。例如11行数据NTILE(4)桶的大小分布会是3, 3, 3, 2。性能考虑NTILE()需要完整的排序和全局计算来确定分桶边界在超大数据集上可能比较耗时。在PARTITION BY子句中使用时计算会在每个分区内独立进行。3. 高级应用与组合实战掌握了单个函数的用法后将它们组合起来或者应用到更复杂的业务逻辑中才能真正释放威力。下面通过几个实战案例来深入理解。3.1 组合应用解决复杂业务问题案例找出每个部门内工资排名前两高的员工如果并列则都入选。这个需求结合了分组(PARTITION BY)、并列排名(RANK())和筛选。使用RANK()可以正确处理并列情况。WITH DeptSalaryRank AS ( SELECT employee_id, employee_name, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM employees ) SELECT * FROM DeptSalaryRank WHERE salary_rank 2;这里使用RANK()而不是ROW_NUMBER()是关键。如果部门内第二高的工资有两人并列ROW_NUMBER()只会随机选一个标记为2另一个标记为3导致漏掉一个人。而RANK()会给这两个人都标记为2从而都被筛选出来。案例计算移动平均或累计求和结合窗口框架排序函数常与其他窗口函数如SUM(),AVG()以及窗口框架子句结合。例如计算每个员工按月份排序的累计销售额SELECT employee_id, sale_month, sales_amount, SUM(sales_amount) OVER (PARTITION BY employee_id ORDER BY sale_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales, ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY sale_month) AS month_seq FROM monthly_sales;在这个例子中ROW_NUMBER()用来生成每个员工内部的月份序列号而SUM(... OVER ...)中的ORDER BY sale_month配合ROWS BETWEEN ...框架实现了从第一个月到当前月的累计求和。3.2 性能优化与编写技巧窗口函数的性能很大程度上依赖于PARTITION BY和ORDER BY子句中的字段是否有合适的索引。索引策略为OVER子句中使用的列创建复合索引通常以PARTITION BY的列在前ORDER BY的列在后。例如对于OVER (PARTITION BY dept_id ORDER BY salary DESC)创建索引(dept_id, salary DESC)会显著提升性能。避免在WHERE子句中直接使用窗口函数列窗口函数是在SELECT阶段计算的而WHERE子句在之前执行。因此不能直接写WHERE ROW_NUMBER() 10。必须使用公共表表达式CTE或子查询先计算窗口函数再过滤。-- 正确写法 WITH Ranked AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM large_table ) SELECT * FROM Ranked WHERE rn BETWEEN 1000000 AND 1000010; -- 错误写法会报语法错误 SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM large_table WHERE rn BETWEEN 1000000 AND 1000010;理解执行计划在复杂的查询中使用EXPLAIN或EXPLAIN ANALYZE命令查看执行计划。关注是否有不必要的全表排序Sort操作并尝试通过调整索引或改写查询来消除它。4. 常见问题排查与经验实录在实际使用中你可能会遇到一些意想不到的情况或错误。这里记录几个我踩过的坑和解决方法。问题1结果集中的序号顺序和预想的不一样现象使用ROW_NUMBER() OVER (ORDER BY column_a)发现column_a值相同的行其row_number在不同时间执行查询时可能不同。根因当ORDER BY列表中的列不能唯一确定行的顺序时存在重复值数据库优化器可能会选择不同的执行路径来生成序号导致并列行的顺序不确定。这不是bug是SQL标准允许的行为。解决确保ORDER BY子句包含一个唯一的键例如主键或唯一索引列。ORDER BY column_a, id。这样即使column_a相同id也能提供一个确定的排序从而保证序号生成的绝对确定性。问题2在包含GROUP BY的查询中使用窗口函数报错或结果不对现象窗口函数计算的范围和GROUP BY聚合后的行数不符。根因逻辑执行顺序。WHERE-GROUP BY- 聚合函数 -HAVING- 窗口函数 -ORDER BY。窗口函数是在GROUP BY聚合之后才计算的它看到的是聚合后的结果行。如果你想要基于原始明细行计算序号然后分组聚合就需要把窗口函数放在子查询或CTE里。示例想先给每个订单明细按金额编号再按订单汇总总金额和最早的编号-- 错误思路直接在GROUP BY中使用 SELECT order_id, SUM(amount) as total_amount, MIN(ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY amount DESC)) as min_rank -- 这里会出错或逻辑混乱 FROM order_details GROUP BY order_id; -- 正确做法先编号再聚合 WITH RankedDetails AS ( SELECT order_id, amount, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY amount DESC) as rn FROM order_details ) SELECT order_id, SUM(amount) as total_amount, MIN(rn) as min_rank -- 现在rn已经是计算好的一列 FROM RankedDetails GROUP BY order_id;问题3NTILE分桶结果不均匀最后一个桶特别小现象用NTILE(10)想把1000行数据分成10个桶期望每桶100行但发现前9个桶是101行最后一个桶只有91行。根因这是NTILE()算法的正常行为。当总行数N不能被桶数n整除时1000 / 10 100能整除所以不会出现此问题。假设是1003行分10桶余数r N % n1003 % 10 3。那么前r个桶即前3个桶每个会多分配一行大小为floor(N/n) 1后面的桶大小为floor(N/n)。理解这不是错误而是设计如此。NTILE()保证的是“尽可能平均”并且桶号小的桶行数不少于桶号大的桶。在数据离散化分析中这种微小的不均衡通常是可以接受的。一个实用的调试技巧当你写的窗口函数查询结果异常复杂、难以理解时一个很好的方法是先运行不带窗口函数的那部分查询看看OVER()子句所定义的数据窗口分区和排序是否正确。然后再逐步加上窗口函数并SELECT出来查看中间结果。例如先运行SELECT *, PARTITION BY dept_id ORDER BY salary DESC所对应的数据子集确认分组和排序是否符合预期再计算RANK()。