根据 - Oracle添加位置列

Question

我有以下记录的表：

table1

id      ele_id_1    ele_val      ele_id_2       
1       2           123          1  
1       1           abc          1  
1       4           xyz          2  
1       4           456          1  
2       5           22           1
2       4           344          1  
2       3           6            1  
2       2           Test Name    1          
2       1           Hello        1  

当ele_id_1和ele_id_2由ASC订购时，我正在尝试为每个id添加位置。

这是输出：

id      ele_id_1    ele_val      ele_id_2       position
1       2           123          1              2
1       1           abc          1              1
1       4           xyz          2              4
1       4           456          1              3
2       5           22           1              5
2       4           344          1              4
2       3           6            1              3
2       2           Test Name    1              2
2       1           Hello        1              1

我在Table1中有3400万行，因此想使用一种有效的方法来执行此操作。

关于如何添加值的位置的任何想法？

Answer 1

我认为您想要这样使用的row_number()：

select row_number() over (partition by id
                          order by ele_id_1, ele_id_2
                         ) as position

Oracle可以在(id, ele_id_1, ele_id_2)上使用索引。

我应该注意，对于您的示例，数据order by ele_id_1, ele_id_2和order by ele_id_2, ele_id_1产生相同的结果。您的问题表明您想要第一个。

所以，你会得到

id      ele_id_1    ele_val      ele_id_2       position
1       1           123          2          2
1       1           abc          1          1
1       4           xyz          2          4
1       4           456          1          3

而不是：

id      ele_id_1    ele_val      ele_id_2       position
1       1           123          2          3
1       1           abc          1          1
1       4           xyz          2          4
1       4           456          1          2

编辑：

如果您要更新数据，则merge可能是最好的方法。

MERGE INTO <yourtable> dest
   USING (select t.*,
                 row_number() over (partition by id
                                    order by ele_id_1, ele_id_2
                                   ) as new_position
          from <yourtable> t
         ) src
         ON dest.id = src.id AND
            dest.ele_id_1 = src.ele_id_1 AND
            dest.ele_id_2 = src.ele_id_2          
   WHEN MATCHED THEN UPDATE
        SET desc.postition = src.new_position;

请注意，更新表中的所有行都是一个昂贵的操作。截断桌子并重新创建它可能更容易：

create table temp_t as 
    select t.*, 
           row_number() over (partition by id
                              order by ele_id_1, ele_id_2
                             ) as new_position
    from t;
truncate table t;
insert into t ( . . . )
    select . . .  -- all columns but position
    from temp_t;

但是，如果截断表，请非常小心。请务必先备份！