Analysis function rewrites not in

1.OLD:

SELECT card.c_cust_id, card.TYPE, card.n_all_money FROM card WHERE card.c_cust_id NOT IN (SELECT c_cust_id FROM card WHERE TYPE IN ('11','12') '13,' 14') AND flag ='1') AND card.TYPE IN ('11,'12') '1300,' 14') AND card.flag ='F'

two。 Optimization direction

(1)。 The main query uses the same table as the subquery, and the conditions are similar. Consider merging.

(2)。

Use the parse function to find the same c_cust_id: card.flag ='F' and flag ='1' or just satisfy flag ='1' and filter out this part of the record.

When the grouping result card.flag ='F' also flag ='1' min (flag) over (partition by card.c_cust_id) ='1'

When the grouping result flag ='1' min (flag) over (partition by card.c_cust_id) ='1'

When the grouping result flag ='F' min (flag) over (partition by card.c_cust_id) ='F' (required)

Select card.c_cust_id, card.TYPE, card.n_all_moneyfrom (select card.c_cust_id, card.TYPE, card.n_all_money, min (flag) over (partition by card.c_cust_id) from card where card.TYPE IN ('11','12' '1300,' 14') and card.flag in ('1pm, F')) where card.flag =' F'