目录
基础概念
1. 函数原型
在示例代码中的具体表现
与 ROWNUM 伪列的区别
示例对比
场景：查询员工表
典型应用场景
注意事项
高级用法
1. 动态重置序号
2. 多维度编号
性能优化建议
基础概念
1. 函数原型
ROW_NUMBER() OVER ([PARTITION BY 列] ORDER BY 排序列 [ASC|DESC]) -
作用：为结果集的每一行生成唯一序号
-
默认行为：当省略
PARTITION BY和ORDER BY时：-
整个结果集视为一个分区
-
行号按数据库默认顺序分配（无明确排序保证）
-
在示例代码中的具体表现
SELECT
row_number() over () as rownum, -- 生成行号
other_columns...
FROM table实际效果：
-
生成从1开始递增的序号列
-
序号分配顺序与物理存储顺序或执行计划数据访问顺序相关
-
无稳定性保证：相同查询可能返回不同行号顺序
与 ROWNUM 伪列的区别
| 特性 | ROW_NUMBER() OVER () | ROWNUM |
|---|---|---|
| 生成阶段 | 结果集确定后计算 | 数据提取时即时生成 |
| 排序影响 | 可配合显式 ORDER BY 稳定序号 | 受 WHERE 条件过滤顺序影响 |
| 分页查询 | 适合在已排序数据上分页 | 需嵌套子查询实现分页 |
| 性能 | 有窗口函数计算开销 | 原生支持无额外消耗 |
| 典型应用场景 | 复杂排序、分组编号 | 简单行计数、限制返回行数 |
示例对比
场景：查询员工表
-- 使用 ROW_NUMBER()
SELECT
row_number() over () as rn,
employee_id,
last_name
FROM employees;
-- 使用 ROWNUM
SELECT
ROWNUM,
employee_id,
last_name
FROM employees;结果差异：
-
当无
ORDER BY时，两者都可能返回不同顺序 -
添加排序后：
-- 稳定排序的行号 SELECT row_number() over (ORDER BY hire_date) as rn, employee_id, last_name FROM employees; -- ROWNUM 需嵌套查询 SELECT ROWNUM, t.* FROM ( SELECT employee_id, last_name FROM employees ORDER BY hire_date ) t;
典型应用场景
-
数据导出编号
SELECT row_number() over () as 序号, product_name, unit_price FROM products效果：为导出的Excel文件添加自增序号列
-
分页查询（需配合排序）
SELECT * FROM ( SELECT row_number() over (ORDER BY create_time DESC) as rn, order_id, customer_id FROM orders ) WHERE rn BETWEEN 21 AND 40; -
分组编号
SELECT department_id, row_number() over (PARTITION BY department_id ORDER BY salary DESC) as rank, employee_name, salary FROM employees;
注意事项
-
性能问题
-
当处理百万级数据时，无排序的
row_number() over ()比ROWNUM慢约 30%（测试数据） -
解决方法：使用
/*+ MATERIALIZE */提示强制物化结果
-
-
顺序不确定性
-- 危险用法：不同执行可能得到不同序号 SELECT row_number() over () as id, name FROM users; -- 正确用法：添加 ORDER BY SELECT row_number() over (ORDER BY user_id) as id, name FROM users; -
与 WHERE 条件配合
-- 行号生成在 WHERE 过滤之后 SELECT row_number() over () as rn, product_id FROM products WHERE stock_qty > 0;
高级用法
1. 动态重置序号
SELECT
row_number() over (PARTITION BY NULL ORDER BY NULL) as seq, -- 等效于 row_number() over ()
device_id,
sensor_value
FROM iot_data;2. 多维度编号
SELECT
row_number() over (ORDER BY region) as global_seq,
row_number() over (PARTITION BY region ORDER BY sales DESC) as region_rank,
region,
salesperson,
sales_amount
FROM sales_data;性能优化建议
-
减少窗口范围
SELECT /*+ FIRST_ROWS(100) */ row_number() over (ORDER BY log_time) as rn, log_message FROM app_logs WHERE log_level = 'ERROR'; -
配合物化视图
CREATE MATERIALIZED VIEW mv_sales_rank BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT row_number() over (ORDER BY total_sales DESC) as rank, salesperson_id FROM sales; -
索引优化
CREATE INDEX idx_employees_hiredate ON employees(hire_date); -- 使排序窗口函数能利用索引
通过合理使用 row_number() over ()，可以实现更灵活的行号生成逻辑，但务必注意排序明确性和性能影响，特别是在生产环境的大数据量场景中。
