ROWNUM 与 ROW_NUMBER() OVER ()

我非夏日

于 2025-03-10 09:26:34 发布

阅读量1.1k

点赞数 23

分类专栏：数据库文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/qq_60872637/article/details/146144071

版权

数据库专栏收录该内容

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 ()，可以实现更灵活的行号生成逻辑，但务必注意排序明确性和性能影响，特别是在生产环境的大数据量场景中。