ROWNUM 与 ROW_NUMBER() OVER ()

基础概念

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()
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;

典型应用场景

1. 数据导出编号

   SELECT 
     row_number() over () as 序号,
     product_name,
     unit_price
   FROM products

   效果 ：为导出的Excel文件添加自增序号列

2. 分页查询（需配合排序）

   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;

3. 分组编号

   SELECT 
     department_id,
     row_number() over (PARTITION BY department_id ORDER BY salary DESC) as rank,
     employee_name,
     salary
   FROM employees;

注意事项

1. 性能问题

   • 当处理百万级数据时，无排序的 row_number() over () 比 ROWNUM 慢约 30%（测试数据）
   • 解决方法：使用 /*+ MATERIALIZE */ 提示强制物化结果

2. 顺序不确定性

   -- 危险用法：不同执行可能得到不同序号
   SELECT row_number() over () as id, name FROM users;
   
   -- 正确用法：添加 ORDER BY
   SELECT row_number() over (ORDER BY user_id) as id, name FROM users;

3. 与 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;

性能优化建议

1. 减少窗口范围

   SELECT /*+ FIRST_ROWS(100) */ 
     row_number() over (ORDER BY log_time) as rn,
     log_message
   FROM app_logs
   WHERE log_level = 'ERROR';

2. 配合物化视图

   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;

3. 索引优化

   CREATE INDEX idx_employees_hiredate ON employees(hire_date);
   -- 使排序窗口函数能利用索引

通过合理使用 row_number() over () ，可以实现更灵活的行号生成逻辑，但务必注意排序明确性和性能影响，特别是在生产环境的大数据量场景中。