what best way have below logic in single netezza sql. implemented logic in loop data set taking long time in netezza (say 47 mins complete loop) have 2 tables, “table - a” (sector_id | value) , “table b” holds sector_id intersected other sector_id combination.
now, table-a sorted descending on value, , need take each highest sector_id table , eliminate corresponding intersected sector_id point in table- b.
for example,
table – (after sorting) sector_id value(desc) deleted rows 6 150 1 140 deleted 4 50 2 45 deleted 3 15 table – b sector_id intersected_id deleted rows 6 6 6 1 deleted 6 2 deleted 1 1 deleted 1 4 deleted 1 2 deleted 4 4 4 1 deleted 2 6 deleted 2 1 deleted 2 2 deleted 3 3 now remaining values in table – desired output. please suggest. db using netezza.
i'm going attempt restate problem, if not accurate let me know in comments can formulate (and answer).
you need remove records in table_a when table_a.sector_id appears in list of previous table_b.intersected_ids given table_b sorted table_a.value.
solution
note solution not relegated netezza-only, rather relational algebra. also, far know, faster cursor or loop-based approach rdbms.
the biggest chore setting list of sector_ids need deleted table_a. see in-line comments descriptions.
create temporary table table_b_extended tba ( --enhance table_a include row number. select row_number() on (order sector_value desc) rwn ,* table_a ), tab ( --join tables , b attach sorting key. select tba.rwn table_a_rwn ,row_number() on (order tba.rwn) table_b_rwn ,tbb.* ,case when tbb.sector_id = tbb.intersected_id 1 else 0 end sector_is_intersected tba join table_b tbb on tba.sector_id = tbb.sector_id ) select * tab distribute on (sector_id); -- find out row intersected id first appears. create temporary table table_b_first_appearance select intersected_id sector_id ,min(table_b_rwn) first_appearance table_b_extended sector_id <> intersected_id group 1 distribute on random; create temporary table table_a_deletes pid ( --get previous intersected_ids. select distinct tab.* ,case --see if row after intersected_id's first appearance. when app.first_appearance < tab.table_b_rwn 1 else 0 end sector_in_previous table_b_extended tab left outer join table_b_first_appearance app using (sector_id) ), vld ( --select records qualify delete table_a. select distinct intersected_id pid --if hasn't been seen , isn't equal intersected_id, delete it. sector_is_intersected + sector_in_previous = 0 ) select * vld distribute on random; given initial input table_b:
+------------+----------------+ | sector_id | intersected_id | +------------+----------------+ | 6 | 6 | | 1 | 2 | | 1 | 1 | | 1 | 4 | | 4 | 4 | | 4 | 1 | | 2 | 6 | | 2 | 1 | | 2 | 2 | | 3 | 3 | | 6 | 1 | | 6 | 2 | +------------+----------------+ this generates table, table_a_deletes, 2 values: 1 , 2. deleting table_a simple.
delete table_a tbl tbl.sector_id in (select sector_id table_a_deletes); and i'm not sure if deleted flags in table_b need replicated or not, if so:
delete table_b tbl tbl.sector_id in (select sector_id table_a_deletes) or tbl.sector_id <> tbl.intersected_id; performance
on 8 spu test system, including delete steps:
test 1
table_asize 14976table_bsize 179427095- runtime 2:50
test 2
table_asize 14976table_bsize 196240063- runtime 3:16
test 3
table_asize 19919table_bsize 317428924- runtime 5:28
by far longest time creating _extended table. if can use other comparator rather establishing new id field, best.
extra cases
here different cases show works in variety of situations. in cases, have modified table_b, since changing table_a trivial case.
case 1
table_b
+--+-------------+------------------+--+ | | sector_id | intersected_id | | +--+-------------+------------------+--+ | | 6 | 6 | | | | 1 | 1 | | | | 1 | 2 | | | | 4 | 4 | | | | 4 | 1 | | | | 2 | 6 | | | | 2 | 1 | | | | 2 | 2 | | | | 3 | 3 | | +--+-------------+------------------+--+ deletes 1 , 2.
case 2
table_b
+--+-------------+------------------+--+ | | sector_id | intersected_id | | +--+-------------+------------------+--+ | | 6 | 6 | | | | 6 | 1 | | | | 1 | 1 | | | | 1 | 2 | | | | 4 | 4 | | | | 4 | 1 | | | | 2 | 6 | | | | 2 | 1 | | | | 2 | 2 | | | | 3 | 3 | | +--+-------------+------------------+--+ deletes 1.
case 3
table_b
+--+-------------+------------------+--+ | | sector_id | intersected_id | | +--+-------------+------------------+--+ | | 6 | 6 | | | | 1 | 1 | | | | 1 | 4 | | | | 4 | 4 | | | | 4 | 1 | | | | 2 | 6 | | | | 2 | 1 | | | | 2 | 2 | | | | 3 | 3 | | +--+-------------+------------------+--+ deletes 1, 4, , 6.
Comments
Post a Comment