I need some help in achieving a efficient way(We might run this on a table with 1+ billion records) to handle positive and negative values as explained below.
Basically *"if set of records has a negative value then i need to exclude those negative values and also exclude corresponding positive values(it could be exact positive value or sum of positive values)"*
Oracle Version
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Example
with temp_data as
(select 1 seq, 'A' ident, -101 value from dual union all
select 2, 'A', 100 from dual union all
select 3, 'A', 200 from dual union all
select 4, 'A', 101 from dual union all
select 5, 'B', 100 from dual union all
select 6, 'C', -100 from dual union all
select 7, 'D', -100 from dual union all
select 8, 'D', 101 from dual union all
select 9, 'D', 99 from dual union all
select 10, 'D', 1 from dual union all
select 11, 'E', 1 from dual union all
select 12, 'E', 2 from dual union all
select 13, 'E', 3 from dual union all
select 14, 'E', -10 from dual union all
select 15, 'E', 7 from dual union all
select 16, 'E', 8 from dual
)
select * from temp_data;
set of records are identified with column ident
For Ident ='A' exclude seq 1,4
For Ident ='B' no exclusions
For Ident ='C' exclude 6
For Ident ='D' exclude 7,(9 & 10)
For Ident ='E' exclude 14,(13 & 15) or (12 & 16)
Note: For Ident ='E' only one set of positive values needs to be excluded.
Final output should look like
Either
seq ident value
2 A 100
3 A 200
5 B 100
8 D 101
11 E 1
12 E 2
16 E 8
OR
seq ident value
2 A 100
3 A 200
5 B 100
8 D 101
11 E 1
13 E 2
15 E 8
Any suggestions would be highly appreciated.