使用row_number()更新ID列：同user_id下相同user_name行ID一致

现有一张包含user_id、user_name、rec列的表，需填充ID列，规则为：

• 按user_id分组（分区），为每个组内的不同user_name分配连续的ID值（从1开始到n）
• 同一user_id下，所有user_name相同的行必须使用同一个ID值

示例预期结果：

此前使用row_number()按user_id分区分配ID的SQL如下：

UPDATE table dst
SET id = (
           SELECT seq
           FROM   (
             SELECT row_number() over (partition by user_id order by user_id) as seq 
             FROM   test_csv
           ) src
           WHERE src.ROWID = dst.ROWID  
         ); 

需调整SQL以满足新的ID分配规则。

要实现同一user_id下相同user_name共享ID的需求，需替换row_number()为dense_rank()函数，按user_id分区，并以user_name作为排序依据（确保同一user_name的行获得相同序号）。修改后的UPDATE语句如下：

UPDATE test_csv dst
SET id = (
           SELECT seq
           FROM   (
             SELECT 
               dense_rank() OVER (PARTITION BY user_id ORDER BY user_name) AS seq,
               ROWID
             FROM   test_csv
           ) src
           WHERE src.ROWID = dst.ROWID  
         );

关键说明 #

• dense_rank()函数会在每个user_id分区内，为不同的user_name分配连续的序号，相同user_name的所有行序号完全一致，且序号不会出现断层
• 若需要按记录的原始出现顺序分配ID（而非按user_name排序），可以将ORDER BY user_name替换为实际的排序字段（比如rec列或记录插入时间字段），只要保证同一user_name的行排序顺序一致即可
• 请将语句中的test_csv替换为你的实际表名

内容的提问来源于stack exchange，提问作者nav