Hello,
I have the following data:
Id AMAOUNT NO_PRATITION ID_STRATEGY
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 RRR
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;
The request is to update amount column, according with the following algorithm:
1. The rows will be grouping by no_partition,id_strategy.
2. We RE LOOKING FOR THE SECOND MAX VALUE
Ex: for ID_strategy = 'XXX' we keep amount=100 (id=1)
for ID_strategy = 'YYY' we keep amount=100 (id=4)
for ID_strategy = 'ZZZ' we keep amount=200 (id=8)
for ID_strategy = 'RRR' we keep amount=100 (id=15 and id=16)
3. If the second max value does not exist, we will keep the max value
Ex: for ID_strategy = 'WWW' we keep amount=100 (id=12 and id=13)
for ID_strategy = 'QQQ' we keep amount=100 (id=18 and id=19)
4. If we have multiple second max values, we will keep the min ID
Ex: for ID_strategy = 'RRR' we keep amount=100 (id=15)
5. If max amount or second max amount to be take in consideration, the amount must be positive(>0)
At the end the data must be updated like that:
Id AMAOUNT(before) AMAOUNT(after) NO_PRATITION ID_STRATEGY
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 RRR
15 100 100 99 RRR
16 100 0 99 RRR
17 200 0 99 RRR
I tried to create only one select for all the cases, but I don’t succeded.
I identified 3 cases:
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.
Thanks a lot in advance,
Marian