# 案例：改写SQL排除剪枝干扰

#### 现象描述

 ```1 2 3 4``` ```select count(1) from t_ddw_f10_op_cust_asset_mon b1 where b1.year_mth between to_char(add_months(to_date(''20170222'','yyyymmdd'), -11),'yyyymm') and substr(''20170222'',1 ,6 ); ```

 ``` 1 2 3 4 5 6 7 8 9 10 11 12``` ```CREATE OR REPLACE FUNCTION ADD_MONTHS(date, integer) RETURNS date AS \$\$ SELECT CASE WHEN (EXTRACT(day FROM \$1) = EXTRACT(day FROM (date_trunc('month', \$1) + INTERVAL '1 month - 1 day'))) THEN date_trunc('month', \$1) + CAST(\$2 + 1 || ' month - 1 day' as interval) ELSE \$1 + CAST(\$2 || ' month' as interval) END \$\$ LANGUAGE SQL IMMUTABLE; ```

#### 优化说明

`Filter: (((year_mth)::text <= '201702'::text) AND ((year_mth)::text >= to_char(add_months(to_date('20170222'::text, 'YYYYMMDD'::text), (-11)), 'YYYYMM'::text)))`

 ```1 2 3 4``` ```select count(1) from t_ddw_f10_op_cust_asset_mon b1 where b1.year_mth between(substr(ADD_MONTHS('20170222'::date, -11), 1, 4)||substr(ADD_MONTHS('20170222'::date, -11), 6, 2)) and substr(''20170222'',1 ,6 ); ```

*必选