SQL语句中 row_number() over() 函数的用法

row_number() over() 是 SQL 中的一个窗口函数（Window Function），它用于为结果集中的每一行分配一个唯一的序号，通常用于处理分组数据或为结果集中的行进行排序和编号。

基本用法

ROW_NUMBER() 函数的基本语法如下：

ROW_NUMBER() OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY columnA, columnB, ...
)

解释：

●PARTITION BY：可选部分，用于将结果集分成多个部分（分区），然后在每个分区内独立地为行编号。如果不使用 PARTITION BY，则所有行都被视为一个单一分区。
●ORDER BY：必须部分，指定了行编号的依据，即按照哪些列的值来排序。

示例

示例 1：不使用 PARTITION BY

假设有一个名为 employees 的表，包含列 department_id 和 hire_date。如果你想为每个部门的员工按雇佣日期排序，并为每行分配一个唯一的序号，可以这样写：

SELECT department_id, hire_date,
       ROW_NUMBER() OVER (ORDER BY hire_date) AS row_num
FROM employees;

这个查询将为所有员工按 hire_date 排序并分配一个行号，不考虑 department_id。

示例 2：使用 PARTITION BY

如果想要在每个部门内部按雇佣日期排序，并为每个部门的员工分配一个行号，可以这样写：

SELECT department_id, hire_date,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date) AS row_num
FROM employees;

这个查询将为每个 department_id 分组内的员工按 hire_date 排序，并在每个部门内部独立地为每行分配一个行号。

其它示例

merge into zzy_acc_tran_2025 b
using (select card_id,trans_id,card_counter,eqp_id,trans_date,trans_time,trans_amt,settle_date,trans_value,discount_amt,unit_id,
       row_number() over (partition by card_id,trans_id,eqp_id,trans_date,trans_time,trans_amt order by settle_date desc) as rn 
from isztsys.tran_normal_info_tran_1231@sztcore where unit_id between 30002 and 30019 and inner_trans_id not in ('9903')) a
 on (a.card_id = b.card_id
 and a.trans_id = b.trade_type
 and a.eqp_id = b.terminal_id
 and a.trans_date = b.trade_date
 and a.trans_time = b.trade_time
 and a.trans_amt = b.trade_money
 and a.rn = 1
)
when matched then
update set b.settle_date = a.settle_date,
           b.trans_value  = a.trans_value,
           b.discount_amt = a.discount_amt,
           b.unit_id = a.unit_id,
           b.flag = 1;
commit;

注意事项

1、唯一性：ROW_NUMBER() 为每一行分配一个唯一的序号，即使两行在排序依据上完全相同，它们也会得到不同的序号。
2、性能：在使用 ROW_NUMBER() 时，特别是在大数据集上，考虑到其排序和分区的操作，可能会有性能影响。确保 ORDER BY 子句中的列有适当的索引，以优化查询性能。
3、窗口函数：ROW_NUMBER() 是窗口函数的一种，这意味着它可以与 OVER() 子句一起使用，以定义其操作的窗口。窗口函数允许对数据集的子集进行操作，这在处理复杂的数据分析任务时非常有用。