Oracle高级函数实战：利用rank()和row_number()实现高效分组排序

1. 为什么需要分组排序函数？

在日常数据处理中，我们经常遇到这样的需求：既要按照某个字段分组，又要在组内按照另一个字段排序。比如统计每个班级的学生成绩排名、计算各部门员工的薪资排名、分析各品类商品的销量排行等。这类需求如果只用基础的GROUP BY和ORDER BY组合，往往难以完美实现。

我刚开始接触Oracle时就踩过这个坑。当时需要统计每个部门的业绩前三名员工，结果用GROUP BY+ORDER BY折腾了半天，发现根本达不到预期效果。后来才知道Oracle提供了rank()和row_number()这类高级分析函数，专门解决这类分组排序问题。

这两个函数的核心优势在于：

• 分组和排序一体化：不需要先GROUP BY再ORDER BY的繁琐操作
• 灵活处理并列排名：rank()会自动处理相同值的排名问题
• 高效执行：相比子查询等复杂写法，性能更好

2. row_number()函数详解

2.1 基本语法与原理

row_number()函数的完整语法是这样的：

row_number() over(partition by 分组列 order by 排序列 [asc|desc])

这个函数的工作原理很有意思 - 它会在执行完WHERE、GROUP BY这些基础操作后，再对结果集进行分组编号。也就是说，over()里面的操作是在查询的最后阶段执行的。

举个实际例子，假设我们有学生成绩表：

-- 创建表 CREATE TABLE student_scores ( id INT PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), score INT ); -- 插入测试数据 INSERT INTO student_scores VALUES (1, 'Alice', 'A', 85); INSERT INTO student_scores VALUES (2, 'Bob', 'A', 90); INSERT INTO student_scores VALUES (3, 'Charlie', 'B', 95); INSERT INTO student_scores VALUES (4, 'David', 'B', 80); INSERT INTO student_scores VALUES (5, 'Emma', 'A', 75); INSERT INTO student_scores VALUES (6, 'Frank', 'B', 85);

2.2 三种典型用法

第一种：单纯排序不分组

select id,name,class,score, row_number() over(order by score desc) ranking from student_scores

这个查询会给所有学生按成绩降序编号，不考虑班级分组。适合全校排名场景。

第二种：先分组再排序

select id,name,class,score, row_number() over(partition by class order by score desc) ranking from student_scores

这才是真正的分组排序 - 先按class分组，再在每个班级内按成绩排序编号。

第三种：取每组前N名

select * from ( select id,name,class,score, row_number() over(partition by class order by score desc) ranking from student_scores ) where ranking < 2

通过子查询包装，我们可以轻松取出每个班级的第一名。把2改成3就是取前三名，非常实用。

3. rank()函数的特殊之处

3.1 rank() vs row_number()

很多初学者分不清rank()和row_number()的区别。其实关键就在于如何处理相同值：

• row_number()：即使值相同，也会分配连续的不同序号（1,2,3,4...）
• rank()：相同值会得到相同序号，下一个不同值会跳过相应数量的序号（1,2,2,4...）

来看具体例子：

-- 使用rank() SELECT id, name, class, score, RANK() OVER(PARTITION BY class ORDER BY score DESC) AS ranking FROM student_scores; -- 使用row_number() SELECT id, name, class, score, row_number() OVER(PARTITION BY class ORDER BY score DESC) AS ranking FROM student_scores;

在A班，Bob 90分第一，Alice 85分第二，Emma 75分第三。但如果A班有两个90分，rank()会并列第一，下一个是第三名；而row_number()仍然是第一、第二。

3.2 实际业务场景选择

选择哪个函数取决于业务需求：

• 需要严格区分名次：比如抽奖、考试录取，用row_number()
• 允许并列排名：比如体育比赛、销售排名，用rank()
• 需要密集排名（无间隔）：可以用dense_rank()

我曾经做过一个销售奖励系统，就因为选错函数闹过笑话。用row_number()导致两个业绩相同的销售员一个拿一等奖一个拿二等奖，后来改用rank()才解决问题。

4. 高级应用技巧

4.1 多列分组排序

partition by支持多个字段，实现更复杂的分组逻辑：

-- 按班级和性别双重分组后排序 select id,name,class,gender,score, row_number() over(partition by class,gender order by score desc) ranking from student_scores

4.2 结合其他分析函数

这些排序函数可以和其他分析函数组合使用：

-- 计算每个班级的平均分，并标注高于平均分的学生 select id,name,class,score, avg(score) over(partition by class) as avg_score, case when score > avg(score) over(partition by class) then 'Y' else 'N' end as above_avg from student_scores

4.3 性能优化建议

虽然这些函数很强大，但在大数据量下要注意：

1. 确保order by的列有索引
2. 避免过度分区，partition by的列不宜过多
3. 考虑先用where条件过滤数据，减少处理量

我在处理一个百万级数据表时就遇到过性能问题。后来在排序列上加了索引，查询时间从15秒降到了0.5秒。

5. 常见问题排查

5.1 为什么结果不符合预期？

新手常遇到的几个坑：

• 忘记写order by导致随机排序
• 排序列有null值导致结果异常
• 混淆partition by和group by的用法

5.2 窗口函数执行顺序

要特别注意这些函数的执行顺序：

1. 先执行FROM和WHERE子句
2. 然后执行GROUP BY
3. 接着执行HAVING
4. 最后才执行窗口函数（over里面的内容）

5.3 与其他SQL语法的配合

窗口函数可以和其他SQL语法自由组合，但要注意：

• 不能直接在WHERE中使用窗口函数结果
• 可以放在SELECT子句或ORDER BY中
• 需要通过子查询或CTE来过滤窗口函数结果

6. 真实案例：销售数据分析

去年我帮一个电商客户做过销售分析系统，核心需求是：

• 按商品品类分组
• 统计每个品类下商品的销量排名
• 标记出每个品类的前三名

最终解决方案是这样的：

WITH sales_ranking AS ( SELECT product_id, category, sales, RANK() OVER(PARTITION BY category ORDER BY sales DESC) as rank FROM product_sales WHERE sales_date BETWEEN '2023-01-01' AND '2023-12-31' ) SELECT product_id, category, sales, CASE WHEN rank <= 3 THEN 'TOP3' ELSE 'OTHER' END as tag FROM sales_ranking

这个查询不仅解决了分组排序问题，还用CASE语句给TOP3商品打了标签，后续做可视化分析非常方便。