Oracle 在多个条件之后更新一列

你好，

我有以下数据:

Id数量
1 100 99 XXX
2 200 99 XXX
3 0 99 YYY
4 100 99 YYY
5 200 99 YYY
6 0 99 ZZZ
7 100 99 ZZZ
8 200 99 ZZZ
9 300 99 ZZZ
10 0 99 WWW
11 0 99 WWW
12 100 99 WWW
13 100 99 WWW
14 0 99存款准备金率
15 100 99 RRR
16 100 99 RRR
17 200 99 RRR

create table test(id number,amount number, no_partition number,id_strategy varchar2(20));

insert into TEST (id, amount, no_partition, id_strategy)
values (1, 100, 99, 'XXX');
insert into TEST (id, amount, no_partition, id_strategy)
values (2, 200, 99, 'XXX');
insert into TEST (id, amount, no_partition, id_strategy)
values (3, 0, 99, 'YYY');
insert into TEST (id, amount, no_partition, id_strategy)
values (4, 100, 99, 'YYY');
insert into TEST (id, amount, no_partition, id_strategy)
values (5, 200, 99, 'YYY');
insert into TEST (id, amount, no_partition, id_strategy)
values (6, 0, 99, 'ZZZ');
insert into TEST (id, amount, no_partition, id_strategy)
values (7, 100, 99, 'ZZZ');
insert into TEST (id, amount, no_partition, id_strategy)
values (8, 200, 99, 'ZZZ');
insert into TEST (id, amount, no_partition, id_strategy)
values (9, 300, 99, 'ZZZ');
insert into TEST (id, amount, no_partition, id_strategy)
values (10, 0, 99, 'WWW');
insert into TEST (id, amount, no_partition, id_strategy)
values (11, 0, 99, 'WWW');
insert into TEST (id, amount, no_partition, id_strategy)
values (12, 100, 99, 'WWW');
insert into TEST (id, amount, no_partition, id_strategy)
values (13, 100, 99, 'WWW');
insert into TEST (id, amount, no_partition, id_strategy)
values (14, 0, 99, 'RRR');
insert into TEST (id, amount, no_partition, id_strategy)
values (15, 100, 99, 'RRR');
insert into TEST (id, amount, no_partition, id_strategy)
values (16, 100, 99, 'RRR');
insert into TEST (id, amount, no_partition, id_strategy)
values (17, 200, 99, 'RRR');
insert into TEST (id, amount, no_partition, id_strategy)
values (18, 100, 99, 'QQQ');
insert into TEST (id, amount, no_partition, id_strategy)
values (19, 100, 99, 'QQQ');
commit;

请求是根据以下算法更新金额列:

1.行将按no_partition分组，id_strategy
2.我们正在寻找第二个最大值
例如: 对于ID_strategy = 'XXX'，我们保持金额 = 100 (id = 1)
对于ID_strategy = 'YYY'，我们保持金额 = 100 (id = 4)
对于ID_strategy = 'ZZZ'，我们保持金额 = 200 (id = 8)
对于ID_strategy = 'RRR'，我们保持金额 = 100 (id = 15和id = 16)

3.如果第二个最大值不存在，我们将保留最大值
例如: 对于ID_strategy = 'WWW'，我们保持金额 = 100 (id = 12和id = 13)
对于ID_strategy = 'qq'，我们保持金额 = 100 (id = 18和id = 19)

4.如果我们有多个第二个最大值，我们将保留最小ID
例如: 对于ID_strategy = 'RRR'，我们保持金额 = 100 (id = 15)

5.如果要考虑最大金额或第二个最大金额，则该金额必须为正 (>0)

最后，数据必须这样更新:

Id数量 (之前) 数量 (之后) 否 _ 计算Id _ 策略
1 100 100 99 XXX
2 200 0 99 XXX
3 0 0 99 YYY
4 100 100 99 YYY
5 200 0 99 YYY
6 0 0 99 ZZZ
7 100 100 99 ZZZ
8 200 0 99 ZZZ
9 300 0 99 ZZZ
10 0 0 99 WWW
11 0 0 99 WWW
12 100 100 99 WWW
13 100 0 99 WWW
14 0 0 99存款准备金率
15 100 100 99 RRR
16 100 0 99存款准备金率
17 200 0 99存款准备金率

我试图为所有情况创建一个选择，但我没有成功。
我确定了3个案例:
1.Update only the lines where we can find 2 rows for each id_strategy(Ex: id_strategy = ‘QQQ’)

update test tt
              set tt.amount = 0
            where tt.id in
                  (select k.id              
                      from (select min(mm.id) over(partition by mm.id_strategy, mm.rk ) minu,
                                    mm.cnt_total,
                                    mm.cnt_total_rk,
                                    mm.rk,
                                    mm.id,
                                    mm.amount,
                                    mm.id_strategy
                               from (
                    select count(*) over(partition by yy.id_strategy ) cnt_total,
                           count(*) over(partition by yy.id_strategy,yy.amount order by yy.amount desc ) cnt_total_rk,
                           dense_rank() over(partition by yy.id_strategy order by yy.amount desc) rk,
                           amount,
                           max(amount) over(partition by yy.id_strategy) mx,
                           yy.id,
                           yy.id_strategy
                      from test yy
                     where yy.no_partition = 99
                     )
                     mm
                    ) k
                     where k.cnt_total = 2 
                           and k.cnt_total_rk=2
                           and k.amount>0
                           and k.minu != k.id)
              and tt.no_partition = 99;  

2. Update only the lines where I can find second max = 0 for each id_strategy(Ex: id_strategy = ‘WWW’)

update test tt
            set tt.amount = 0
          where tt.id in
                (select k.id
                   from (select dense_rank() over(partition by mm.id_strategy, mm.amount order by mm.id) rk2,
                                mm.amount vv,
                                mm.*
                           from (select max(amount) over(partition by yy.id_strategy) mx,
                                        yy.*
                                   from test yy
                                  where yy.no_partition = 99) mm) k
                  where k.rk2 != 1
                    and k.mx = k.vv
                    and k.id_strategy in
                        (select distinct id_strategy
                           from (select min(mm.amount) over(partition by mm.id_strategy, mm.rk ) as min_amount_lv2,
                                        mm.rk,
                                        mm.id_strategy
                                   from (select dense_rank() over(partition by yy.id_strategy order by yy.amount desc) rk,
                                                amount,
                                                max(amount) over(partition by yy.id_strategy) mx,
                                                yy.id,
                                                yy.id_strategy
                                           from test yy
                                          where yy.no_partition = 99
                                         ) mm) k
                          where k.rk = 2
                            and k.min_amount_lv2 = 0
                           
                         ))
            and tt.no_partition = 99;

3. Update all rows that have second max > 0 (Ex: id_strategy in XXX,YYY,ZZZ,RRR)

update test tt
           set tt.amount = 0
         where tt.id not in
               (with res as
                (select k.id
                   from (select min(mm.id) over(partition by mm.id_strategy, mm.rk ) minu,
                                 mm.*
                            from (select dense_rank() over(partition by yy.id_strategy order by yy.amount desc) rk,
                                          amount,
                                          max(amount) over(partition by yy.id_strategy ) mx,
                                          yy.id,
                                          yy.id_strategy
                                     from test yy
                                    where yy.no_partition = 99
                                 ) mm) k
                  where (k.minu = k.id and k.rk = 2 and k.amount > 0))
                 select res.id from res
                )
           and tt.id_strategy in
               (with res as
                (select k.id_strategy
                   from (select min(mm.id) over(partition by mm.id_strategy, mm.rk ) minu,
                                 mm.*
                            from (select dense_rank() over(partition by yy.id_strategy order by yy.amount desc) rk,
                                          amount,
                                          max(amount) over(partition by yy.id_strategy) mx,
                                          yy.id,
                                          yy.id_strategy
                                     from test yy
                                    where  yy.no_partition = 99
                                 ) mm) k
                  where (k.minu = k.id and k.rk = 2 and k.amount > 0))
                 select res.id_strategy from res
                )
           and tt.no_partition = 99;

My question is:
It is possible to update all this data in a single UPDATE clause?
If it is possible , please give me an advise.


提前非常感谢，
玛丽安

我不确定我是否确切了解您在寻找什么...

但是，如果要将第二行以外的所有行的金额设置为零，则按金额desc，每个no_partition和id_strategy的id排序，则可以使用nth_value。

使用它返回集合中的第二行的ID。并更新所有这些不在此:

update test
set    amount = 0
where  id not in ( 
  select * from (
    select nth_value ( id, 2 ) over ( 
             partition by no_partition, id_strategy
             order by amount desc, id
           ) second_max_id
    from   test t
  )
  where  second_max_id is not null
);

select * from test
order  by id;

ID   AMOUNT   NO_PARTITION   ID_STRATEGY   
   1      100             99 XXX           
   2        0             99 XXX           
   3        0             99 YYY           
   4      100             99 YYY           
   5        0             99 YYY           
   6        0             99 ZZZ           
   7        0             99 ZZZ           
   8      200             99 ZZZ           
   9        0             99 ZZZ           
  10        0             99 WWW           
  11        0             99 WWW           
  12        0             99 WWW           
  13      100             99 WWW           
  14        0             99 RRR           
  15      100             99 RRR           
  16        0             99 RRR           
  17        0             99 RRR           
  18        0             99 QQQ           
  19      100             99 QQQ 

我不确定这是否正是您要寻找的; 我不清楚在这些情况下应该发生什么:

-我们更新了WWW的第12行而不是13行是否重要？
-如果RRR的另一行的金额 = 200，则金额 = 100 (15和16) 的行是否仍为 “第二”？

根据这些答案，您可能需要使用rank/dense_rank/row_number的某种组合。但是原理是相同的-更新此集合中不是 “第二” 的所有行。