news 2026/8/14 22:40:56

Oracle排序函数实战：ROW_NUMBER、DENSE_RANK和RANK的区别与应用场景

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
Oracle排序函数实战：ROW_NUMBER、DENSE_RANK和RANK的区别与应用场景

Oracle排序函数实战：ROW_NUMBER、DENSE_RANK和RANK的区别与应用场景

在数据处理和分析过程中，排序是最基础也是最常用的操作之一。Oracle数据库提供了多种排序函数，其中ROW_NUMBER、DENSE_RANK和RANK是最常用的三种。这些函数看似相似，但在实际应用中却有着微妙的差异，选择不当可能导致完全不同的结果。本文将深入探讨这三种函数的区别，并通过实际案例展示它们在不同业务场景下的应用。

1. 排序函数基础概念

Oracle的窗口函数（Window Functions）是一类特殊的SQL函数，它们不是对查询结果的每一行单独计算，而是基于一组相关的行（称为窗口或框架）进行计算。排序函数是窗口函数中最常用的子集，主要包括：

  • ROW_NUMBER()：为结果集中的每一行分配一个唯一的序号，即使存在相同的排序值也会分配不同的序号
  • DENSE_RANK()：为结果集中的行分配序号，相同值的行获得相同序号，但序号是连续的
  • RANK()：为结果集中的行分配序号，相同值的行获得相同序号，但序号可能不连续

这三种函数的基本语法相似：

函数名() OVER ([PARTITION BY 列名] ORDER BY 列名 [ASC|DESC])

其中：

  • PARTITION BY子句可选，用于将结果集分成多个分区，函数在每个分区内独立计算
  • ORDER BY子句指定排序的列和顺序
  • ASC（升序，默认）或DESC（降序）指定排序方向

2. 三种排序函数的详细对比

2.1 基本行为差异

为了直观展示三种函数的区别，我们创建一个简单的测试表并插入数据：

CREATE TABLE sales ( id NUMBER, salesperson VARCHAR2(50), amount NUMBER ); INSERT INTO sales VALUES (1, '张三', 1000); INSERT INTO sales VALUES (2, '李四', 1500); INSERT INTO sales VALUES (3, '王五', 1500); INSERT INTO sales VALUES (4, '赵六', 2000); INSERT INTO sales VALUES (5, '钱七', 2000); INSERT INTO sales VALUES (6, '孙八', 2000); INSERT INTO sales VALUES (7, '周九', 1800);

现在，我们分别使用三种函数对销售金额进行排序：

SELECT salesperson, 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;

执行结果如下：

SALESPERSONAMOUNTROW_NUMRANK_VALDENSE_RANK_VAL
赵六2000111
钱七2000211
孙八2000311
周九1800442
李四1500553
王五1500653
张三1000774

从结果可以清晰看出三种函数的区别：

  1. ROW_NUMBER：为每一行分配唯一的序号，不考虑值是否相同
  2. RANK：相同值的行获得相同序号，但会留下"空缺"（如没有2、3名）
  3. DENSE_RANK：相同值的行获得相同序号，且序号是连续的（没有空缺）

2.2 性能考量

虽然这三种函数在语法上相似，但它们的性能特征有所不同：

函数计算复杂度内存使用适用场景
ROW_NUMBER低低需要唯一序号时
RANK中中需要反映真实排名位置时
DENSE_RANK高高需要连续排名且不考虑空缺时

在实际应用中，如果数据量很大，这些性能差异可能会变得明显。特别是在处理数百万行数据时，选择正确的函数可以显著影响查询性能。

3. 实际应用场景分析

3.1 分页查询（ROW_NUMBER的典型应用）

在Web应用中，分页是常见需求。ROW_NUMBER函数非常适合实现高效的分页查询：

-- 第一页，每页3条记录 SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY create_time DESC) AS rn FROM articles t ) WHERE rn BETWEEN 1 AND 3; -- 第二页 SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY create_time DESC) AS rn FROM articles t ) WHERE rn BETWEEN 4 AND 6;

这种实现方式比传统的ROWNUM方法更灵活，特别是在需要复杂排序时。

3.2 排名统计（RANK和DENSE_RANK的应用）

在成绩排名、销售排名等场景中，RANK和DENSE_RANK更为适用。考虑一个学生成绩排名的例子：

SELECT student_name, score, RANK() OVER (ORDER BY score DESC) AS rank_position, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_position FROM exam_results;

假设有以下成绩：

STUDENT_NAMESCORERANK_POSITIONDENSE_RANK_POSITION
张三9511
李四9222
王五9222
赵六9043
钱七8854
  • 使用RANK时，李四和王五并列第二，下一个名次是第四
  • 使用DENSE_RANK时，李四和王五并列第二，下一个名次是第三

在教育场景中，通常使用DENSE_RANK更为合理，因为名次空缺可能会引起误解。

3.3 分区排序（PARTITION BY的应用）

三种函数都可以与PARTITION BY子句结合使用，实现分组内的排序。例如，计算每个部门的员工工资排名：

SELECT department_id, employee_name, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_row_num, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_dense_rank FROM employees;

这种分区排序在部门绩效评估、区域销售分析等场景中非常有用。

4. 高级应用技巧

4.1 处理NULL值

排序函数对NULL值的处理是一个需要注意的问题。默认情况下：

  • 在ORDER BY子句中，NULL值会被视为最大的值（在DESC排序时排在最前，ASC排序时排在最后）
  • 可以使用NULLS FIRST或NULLS LAST明确指定NULL值的位置
-- NULL值排在最后 SELECT employee_name, bonus, ROW_NUMBER() OVER (ORDER BY bonus NULLS LAST) AS rn FROM employees; -- NULL值排在最前 SELECT employee_name, bonus, ROW_NUMBER() OVER (ORDER BY bonus DESC NULLS FIRST) AS rn FROM employees;

4.2 动态排序

在实际应用中，可能需要根据用户选择动态改变排序方式。可以通过CASE语句实现：

SELECT product_id, product_name, price, sales_volume FROM ( SELECT t.*, ROW_NUMBER() OVER ( ORDER BY CASE WHEN :sort_by = 'price' THEN price WHEN :sort_by = 'sales' THEN sales_volume ELSE product_id END ) AS rn FROM products t ) WHERE rn BETWEEN :start_row AND :end_row;

4.3 性能优化建议

  1. 索引优化：为ORDER BY子句中的列创建适当索引
  2. 减少分区大小：PARTITION BY子句中的列应该有较高的区分度
  3. 限制结果集：在外层查询中使用WHERE条件限制返回的行数
  4. 避免过度使用：窗口函数计算成本较高，不应滥用
-- 优化示例：只为需要的行计算排名 SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn FROM students t WHERE graduation_year = 2023 ) WHERE rn <= 10;

5. 常见问题与解决方案

5.1 相同值但不同排序的问题

当ORDER BY子句中的列有相同值时，ROW_NUMBER的排序结果可能不稳定（相同值的行可能得到不同的序号）。要确保稳定的排序，应该在ORDER BY中包含足够唯一的列：

-- 不稳定的排序 SELECT ROW_NUMBER() OVER (ORDER BY department_id) FROM employees; -- 稳定的排序 SELECT ROW_NUMBER() OVER (ORDER BY department_id, employee_id) FROM employees;

5.2 分页时的性能问题

对于深度分页（如第1000页），ROW_NUMBER方法可能效率低下。可以考虑以下优化：

-- 优化深度分页 SELECT * FROM ( SELECT /*+ FIRST_ROWS(100) */ t.*, ROW_NUMBER() OVER (ORDER BY create_time DESC) AS rn FROM articles t WHERE create_time <= :last_page_max_time ) WHERE rn BETWEEN 1001 AND 1020;

5.3 分区排序的内存消耗

当PARTITION BY的分区很多时，可能会消耗大量内存。可以通过以下方式缓解：

  1. 增加PGA内存
  2. 使用/*+ GATHER_PLAN_STATISTICS */提示分析内存使用
  3. 考虑在应用层实现分区逻辑

在实际项目中，我发现合理使用这三种排序函数可以解决90%的数据排序需求。特别是在报表生成和数据导出场景中，它们能够提供灵活而强大的排序能力。

版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/7/14 15:58:47

Audio Pixel Studio部署教程：Docker Compose编排TTS+UVR服务集群方案

Audio Pixel Studio部署教程&#xff1a;Docker Compose编排TTSUVR服务集群方案 想快速搭建一个集语音合成和人声分离于一体的音频处理工作站吗&#xff1f;Audio Pixel Studio就是为你准备的。它把复杂的音频处理技术打包成一个简洁的Web应用&#xff0c;让你在浏览器里点点鼠…

作者头像 李华
网站建设 2026/7/14 15:58:46

革新OpenCore配置：3大核心功能让Hackintosh部署效率提升60%

革新OpenCore配置&#xff1a;3大核心功能让Hackintosh部署效率提升60% 【免费下载链接】OCAuxiliaryTools Cross-platform GUI management tools for OpenCore&#xff08;OCAT&#xff09; 项目地址: https://gitcode.com/gh_mirrors/oc/OCAuxiliaryTools OCAuxiliary…

作者头像 李华
网站建设 2026/7/14 15:59:01

用Mambaforge加速Python包管理：实测比Conda快10倍的配置教程

用Mambaforge加速Python包管理&#xff1a;实测比Conda快10倍的配置教程 如果你曾经被Conda缓慢的包管理速度折磨过——等待依赖解析时盯着进度条发呆&#xff0c;或者在CI/CD流水线中因为包安装超时而重试——那么Mambaforge可能是你一直在寻找的解决方案。作为一名每天需要处…

作者头像 李华
网站建设 2026/7/14 15:58:58

深度学习与卡尔曼滤波的融合：动态系统中的智能状态估计

1. 当深度学习遇上卡尔曼滤波&#xff1a;动态系统的黄金搭档 我第一次在自动驾驶项目里尝试结合这两种技术时&#xff0c;就像发现咖啡和牛奶的绝妙搭配。当时我们的目标检测模型在雨天频繁误判行人位置&#xff0c;工程师们争论是该增加训练数据还是调整网络结构。直到有位老…

作者头像 李华