用with改写优化sql

网友发来一案例,sql如下,表名及部分字段名作了处理,这个sql要跑20分钟。因为本次改写与plan无关,所以plan也就不发了

WITH SAWITH0 AS
 (select distinct T_A.T_A_SHORT_NAME as c1,
                  cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER) as c2,
                  T_B.CAL_MONTH_NAME as c3,
                  T_A.EFFECTIVE_MONTH_ACT as c4,
                  T_A.EFFECTIVE_MONTH_BGT as c5,
                  T_A.T_A_CODE as c6,
                  T_A.T_A_COOPER as c7,
                  T_B.CAL_MONTH_ID as c8
    from T_B T_B,
         T_C T_C,
         (SELECT CHAIN_T_A_SK,
                 HEADER_ID,
                 OFFICE_CODE,
                 T_A_CODE,
                 T_A_SHORT_NAME,
                 T_A_NAME,
                 T_A_ADDRESS,
                 T_A_PHONE,
                 T_A_PROPERITY,
                 COMPANY_PROPERTY,
                 T_A_AREA,
                 PROVINCE_SEQ,
                 T_A_PROVINCE,
                 CITY_SEQ,
                 T_A_CITY,
                 T_A_COUNTY,
                 T_A_COOPER,
                 T_A_BRAND,
                 T_A_NATURE,
                 LEASE_BUILDING_AREA,
                 CONS_BUILDING_AREA,
                 ROOM_QUANTITY,
                 SEATS_QUANTITY,
                 MEAL_QUANTITY,
                 BUSINESS_LICENSE_DATE,
                 SIGN_DATE,
                 START_DATE,
                 END_DATE,
                 case
                   when SIGN_DATE >= PRE_BUSINESS_DATE then
                    SIGN_DATE
                   else
                    PRE_BUSINESS_DATE
                 end PRE_BUSINESS_DATE,
                 BUSINESS_DATE,
                 LAST_UPDATE_DATE,
                 LAST_UPDATED_BY,
                 CREATION_DATE,
                 CREATED_BY,
                 LAST_UPDATE_LOGIN,
                 T_A_SEGMENT,
                 T_A_AREA_1,
                 T_A_TYPE,
                 ORG_CODE,
                 BUSINESS_LICENSE_END_DATE,
                 PMS_CODE,
                 ORG_ID,
                 PRO_HANDOVER_DATE,
                 FOREIGN_RENT_AREA,
                 SIGN_YEAR,
                 COMPANY_NAME,
                 REMOVE_DATE,
                 EFFECTIVE_DATE,
                 EXPIRATION_DATE,
                 CURRENT_FLAG,
                 ETL_DATE,
                 OLD_T_A_NAME,
                 BRAND_CODE,
                 EQUITY_NAME,
                 (EQUITY_RATE / 100.00) AS EQUITY_RATE,
                 nvl(BUSINESS_DATE, PRE_BUSINESS_DATE) UNI_BUSINESS_DATE,
                 floor(pre_business_date - sign_date) AS DIFF_PRE_SIGN,
                 floor(business_date - pre_business_date) AS DIFF_B_PRE,
                 floor(business_date - sign_date) AS DIFF_B_SIGN,
                 CASE
                   WHEN TERMINATION_DATE IS NULL THEN
                    'N'
                   ELSE
                    'Y'
                 END IS_FLAG,
                 TERMINATION_DATE,
                 to_char(add_months(effective_date, -1), 'YYYY-MM') effective_month_act,
                 to_char(add_months(expiration_date, -1), 'YYYY-MM') expiration_month_act,
                 to_char(add_months(effective_date, 12), 'YYYY-MM') effective_month_bgt,
                 to_char(add_months(expiration_date, 12), 'YYYY-MM') expiration_month_bgt
            FROM T_A,
                 (select t.T_A_code u_T_A_code,
                         max(t.effective_date) u_effective_date
                    from T_A t
                   group by t.T_A_code, to_char(t.effective_date, 'YYYY-MM')) t
           WHERE T_A.T_A_code = t.u_T_A_code
             and T_A.effective_date = t.u_effective_date) T_A
   where (T_C.T_A_CODE = T_A.T_A_CODE and
         T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and
         T_C.PERIOD_MON < T_A.EXPIRATION_MONTH_ACT and
         T_B.CAL_YEAR_ID = '2013' and
         T_C.PERIOD_MON >= T_A.EFFECTIVE_MONTH_ACT and
         T_C.PERIOD_MON >= '2013-05' and
         (substr(T_C.T_A_CODE, 1, 1) in ('A', 'B')) and
         (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and
         (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'), '9999'), '-'), TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >=
         T_C.PERIOD_MON or T_A.TERMINATION_DATE is null) and
         T_B.CAL_MONTH_NAME >= '2013-05')),
SACOMMON3694452 AS
 (select sum(T_C.AVHIRE_QTY_ACT) as c9,
         sum(T_C.RMHIRE_QTY_ACT) as c10,
         T_A.EFFECTIVE_MONTH_ACT as c11,
         T_A.EFFECTIVE_MONTH_BGT as c12,
         T_A.T_A_CODE as c13,
         T_A.T_A_COOPER as c14,
         cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER) as c15,
         T_B.CAL_MONTH_ID as c16,
         grouping_id(T_A.T_A_CODE,
                     T_A.T_A_COOPER,
                     T_A.EFFECTIVE_MONTH_ACT,
                     T_A.EFFECTIVE_MONTH_BGT,
                     cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER),
                     T_B.CAL_MONTH_ID) as c17
    from T_B T_B,
         T_C T_C,
         (SELECT CHAIN_T_A_SK,
                 HEADER_ID,
                 OFFICE_CODE,
                 T_A_CODE,
                 T_A_SHORT_NAME,
                 T_A_NAME,
                 T_A_ADDRESS,
                 T_A_PHONE,
                 T_A_PROPERITY,
                 COMPANY_PROPERTY,
                 T_A_AREA,
                 PROVINCE_SEQ,
                 T_A_PROVINCE,
                 CITY_SEQ,
                 T_A_CITY,
                 T_A_COUNTY,
                 T_A_COOPER,
                 T_A_BRAND,
                 T_A_NATURE,
                 LEASE_BUILDING_AREA,
                 CONS_BUILDING_AREA,
                 ROOM_QUANTITY,
                 SEATS_QUANTITY,
                 MEAL_QUANTITY,
                 BUSINESS_LICENSE_DATE,
                 SIGN_DATE,
                 START_DATE,
                 END_DATE,
                 case
                   when SIGN_DATE >= PRE_BUSINESS_DATE then
                    SIGN_DATE
                   else
                    PRE_BUSINESS_DATE
                 end PRE_BUSINESS_DATE,
                 BUSINESS_DATE,
                 LAST_UPDATE_DATE,
                 LAST_UPDATED_BY,
                 CREATION_DATE,
                 CREATED_BY,
                 LAST_UPDATE_LOGIN,
                 T_A_SEGMENT,
                 T_A_AREA_1,
                 T_A_TYPE,
                 ORG_CODE,
                 BUSINESS_LICENSE_END_DATE,
                 PMS_CODE,
                 ORG_ID,
                 PRO_HANDOVER_DATE,
                 FOREIGN_RENT_AREA,
                 SIGN_YEAR,
                 COMPANY_NAME,
                 REMOVE_DATE,
                 EFFECTIVE_DATE,
                 EXPIRATION_DATE,
                 CURRENT_FLAG,
                 ETL_DATE,
                 OLD_T_A_NAME,
                 BRAND_CODE,
                 EQUITY_NAME,
                 (EQUITY_RATE / 100.00) AS EQUITY_RATE,
                 nvl(BUSINESS_DATE, PRE_BUSINESS_DATE) UNI_BUSINESS_DATE,
                 floor(pre_business_date - sign_date) AS DIFF_PRE_SIGN,
                 floor(business_date - pre_business_date) AS DIFF_B_PRE,
                 floor(business_date - sign_date) AS DIFF_B_SIGN,
                 CASE
                   WHEN TERMINATION_DATE IS NULL THEN
                    'N'
                   ELSE
                    'Y'
                 END IS_FLAG,
                 TERMINATION_DATE,
                 to_char(add_months(effective_date, -1), 'YYYY-MM') effective_month_act,
                 to_char(add_months(expiration_date, -1), 'YYYY-MM') expiration_month_act,
                 to_char(add_months(effective_date, 12), 'YYYY-MM') effective_month_bgt,
                 to_char(add_months(expiration_date, 12), 'YYYY-MM') expiration_month_bgt
            FROM T_A,
                 (select t.T_A_code u_T_A_code,
                         max(t.effective_date) u_effective_date
                    from T_A t
                   group by t.T_A_code, to_char(t.effective_date, 'YYYY-MM')) t
           WHERE T_A.T_A_code = t.u_T_A_code
             and T_A.effective_date = t.u_effective_date) T_A
   where (T_B.CAL_MONTH_NAME < T_A.EXPIRATION_MONTH_ACT and
         T_B.CAL_YEAR_ID = '2013' and
         T_C.T_A_CODE = T_A.T_A_CODE and
         T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and
         T_B.CAL_MONTH_NAME >= T_A.EFFECTIVE_MONTH_ACT and
         T_B.CAL_MONTH_NAME >= '2013-05' and
         (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and
         (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'),
                                   '9999'),
                         '-'),
                  TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >=
         T_B.CAL_MONTH_NAME or T_A.TERMINATION_DATE is null))
   group by grouping sets((cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER), T_A.EFFECTIVE_MONTH_BGT, T_A.EFFECTIVE_MONTH_ACT, T_A.T_A_COOPER, T_A.T_A_CODE),(T_B.CAL_MONTH_ID, cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER), T_A.EFFECTIVE_MONTH_BGT, T_A.EFFECTIVE_MONTH_ACT, T_A.T_A_CODE))),
SAWITH1 AS
 (select sum(T_C.AVHIRE_QTY_ACT) as c7,
         sum(T_C.RMHIRE_QTY_ACT) as c8,
         T_A.EFFECTIVE_MONTH_ACT as c9,
         T_A.EFFECTIVE_MONTH_BGT as c10,
         T_A.T_A_CODE as c11,
         T_A.T_A_COOPER as c12
    from T_B T_B,
         T_C T_C,
         (SELECT CHAIN_T_A_SK,
                 HEADER_ID,
                 OFFICE_CODE,
                 T_A_CODE,
                 T_A_SHORT_NAME,
                 T_A_NAME,
                 T_A_ADDRESS,
                 T_A_PHONE,
                 T_A_PROPERITY,
                 COMPANY_PROPERTY,
                 T_A_AREA,
                 PROVINCE_SEQ,
                 T_A_PROVINCE,
                 CITY_SEQ,
                 T_A_CITY,
                 T_A_COUNTY,
                 T_A_COOPER,
                 T_A_BRAND,
                 T_A_NATURE,
                 LEASE_BUILDING_AREA,
                 CONS_BUILDING_AREA,
                 ROOM_QUANTITY,
                 SEATS_QUANTITY,
                 MEAL_QUANTITY,
                 BUSINESS_LICENSE_DATE,
                 SIGN_DATE,
                 START_DATE,
                 END_DATE,
                 case
                   when SIGN_DATE >= PRE_BUSINESS_DATE then
                    SIGN_DATE
                   else
                    PRE_BUSINESS_DATE
                 end PRE_BUSINESS_DATE,
                 BUSINESS_DATE,
                 LAST_UPDATE_DATE,
                 LAST_UPDATED_BY,
                 CREATION_DATE,
                 CREATED_BY,
                 LAST_UPDATE_LOGIN,
                 T_A_SEGMENT,
                 T_A_AREA_1,
                 T_A_TYPE,
                 ORG_CODE,
                 BUSINESS_LICENSE_END_DATE,
                 PMS_CODE,
                 ORG_ID,
                 PRO_HANDOVER_DATE,
                 FOREIGN_RENT_AREA,
                 SIGN_YEAR,
                 COMPANY_NAME,
                 REMOVE_DATE,
                 EFFECTIVE_DATE,
                 EXPIRATION_DATE,
                 CURRENT_FLAG,
                 ETL_DATE,
                 OLD_T_A_NAME,
                 BRAND_CODE,
                 EQUITY_NAME,
                 (EQUITY_RATE / 100.00) AS EQUITY_RATE,
                 nvl(BUSINESS_DATE, PRE_BUSINESS_DATE) UNI_BUSINESS_DATE,
                 floor(pre_business_date - sign_date) AS DIFF_PRE_SIGN,
                 floor(business_date - pre_business_date) AS DIFF_B_PRE,
                 floor(business_date - sign_date) AS DIFF_B_SIGN,
                 CASE
                   WHEN TERMINATION_DATE IS NULL THEN
                    'N'
                   ELSE
                    'Y'
                 END IS_FLAG,
                 TERMINATION_DATE,
                 to_char(add_months(effective_date, -1), 'YYYY-MM') effective_month_act,
                 to_char(add_months(expiration_date, -1), 'YYYY-MM') expiration_month_act,
                 to_char(add_months(effective_date, 12), 'YYYY-MM') effective_month_bgt,
                 to_char(add_months(expiration_date, 12), 'YYYY-MM') expiration_month_bgt
            FROM T_A,
                 (select t.T_A_code u_T_A_code,
                         max(t.effective_date) u_effective_date
                    from T_A t
                   group by t.T_A_code, to_char(t.effective_date, 'YYYY-MM')) t
           WHERE T_A.T_A_code = t.u_T_A_code
             and T_A.effective_date = t.u_effective_date) T_A
   where (T_C.T_A_CODE = T_A.T_A_CODE and
         T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and
         T_B.CAL_YEAR_ID = '2013' and
         T_C.PERIOD_MON < T_A.EXPIRATION_MONTH_ACT and
         (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and
         (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'),
                                   '9999'),
                         '-'),
                  TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >=
         T_C.PERIOD_MON or T_A.TERMINATION_DATE is null) and
         T_C.PERIOD_MON >= T_A.EFFECTIVE_MONTH_ACT and
         T_C.PERIOD_MON >= '2013-05' and
         T_B.CAL_MONTH_NAME >= '2013-05')
   group by T_A.T_A_CODE,
            T_A.T_A_COOPER,
            T_A.EFFECTIVE_MONTH_ACT,
            T_A.EFFECTIVE_MONTH_BGT),
SAWITH2 AS
 (select D4.c7  as c1,
         D4.c8  as c2,
         D1.c7  as c3,
         D1.c1  as c4,
         D2.c9  as c5,
         D2.c10 as c6,
         D3.c10 as c7,
         D1.c2  as c8,
         D1.c3  as c9,
         D1.c4  as c10,
         D1.c5  as c11,
         D1.c6  as c12
    from ((SAWITH0 D1 inner join SACOMMON3694452 D2 On
          SYS_OP_MAP_NONNULL(D2.c15) = SYS_OP_MAP_NONNULL(D1.c2) and
          D2.c12 = D1.c5 and D2.c11 = D1.c4 and
          SYS_OP_MAP_NONNULL(D2.c13) = SYS_OP_MAP_NONNULL(D1.c6) and
          SYS_OP_MAP_NONNULL(D2.c14) = SYS_OP_MAP_NONNULL(D1.c7)) inner join
          SACOMMON3694452 D3 On
          SYS_OP_MAP_NONNULL(D3.c15) = SYS_OP_MAP_NONNULL(D1.c2) and
          D3.c12 = D1.c5 and D3.c11 = D1.c4 and
          SYS_OP_MAP_NONNULL(D3.c16) = SYS_OP_MAP_NONNULL(D1.c8) and
          SYS_OP_MAP_NONNULL(D3.c13) = SYS_OP_MAP_NONNULL(D1.c6))
   inner join SAWITH1 D4
      On D4.c10 = D1.c5
     and D4.c9 = D1.c4
     and SYS_OP_MAP_NONNULL(D4.c11) = SYS_OP_MAP_NONNULL(D1.c6)
     and SYS_OP_MAP_NONNULL(D4.c12) = SYS_OP_MAP_NONNULL(D1.c7)
   where (D2.c17 = 1 and D3.c17 = 16))
select D1.c1  as c1,
       D1.c2  as c2,
       D1.c3  as c3,
       D1.c4  as c4,
       D1.c5  as c5,
       D1.c6  as c6,
       D1.c7  as c7,
       D1.c8  as c8,
       D1.c9  as c9,
       D1.c10 as c10
  from (select 0 as c1,
               D1.c3 as c2,
               D1.c4 as c3,
               '经营利润率' as c4,
               D1.c8 as c5,
               case
                 when D1.c9 <= '2013-10' then
                  case
                    when D1.c5 = 0 then
                     0
                    else
                     cast(D1.c6 as DOUBLE PRECISION) / nullif(D1.c5, 0) * 100.0
                  end
                 else
                  NULL
               end as c6,
               case
                 when D1.c9 <= '2013-10' then
                  case
                    when D1.c1 = 0 then
                     0
                    else
                     cast(D1.c2 as DOUBLE PRECISION) / nullif(D1.c1, 0) * 100.0
                  end
                 else
                  NULL
               end as c7,
               D1.c10 as c8,
               D1.c11 as c9,
               D1.c12 as c10,
               ROW_NUMBER() OVER(PARTITION BY D1.c3, D1.c4, D1.c8, D1.c10, D1.c11, D1.c12 ORDER BY D1.c3 ASC, D1.c4 ASC, D1.c8 ASC, D1.c10 ASC, D1.c11 ASC, D1.c12 ASC) as c11
          from SAWITH2 D1) D1
 where (D1.c11 = 1);


经观察,以上语句中t_a重复调用了多次,于是用with改写如下

WITH T_A AS 
(SELECT CHAIN_T_A_SK,
                 HEADER_ID,
                 OFFICE_CODE,
                 T_A_CODE,
                 T_A_SHORT_NAME,
                 T_A_NAME,
                 T_A_ADDRESS,
                 T_A_PHONE,
                 T_A_PROPERITY,
                 COMPANY_PROPERTY,
                 T_A_AREA,
                 PROVINCE_SEQ,
                 T_A_PROVINCE,
                 CITY_SEQ,
                 T_A_CITY,
                 T_A_COUNTY,
                 T_A_COOPER,
                 T_A_BRAND,
                 T_A_NATURE,
                 LEASE_BUILDING_AREA,
                 CONS_BUILDING_AREA,
                 ROOM_QUANTITY,
                 SEATS_QUANTITY,
                 MEAL_QUANTITY,
                 BUSINESS_LICENSE_DATE,
                 SIGN_DATE,
                 START_DATE,
                 END_DATE,
                 case
                   when SIGN_DATE >= PRE_BUSINESS_DATE then
                    SIGN_DATE
                   else
                    PRE_BUSINESS_DATE
                 end PRE_BUSINESS_DATE,
                 BUSINESS_DATE,
                 LAST_UPDATE_DATE,
                 LAST_UPDATED_BY,
                 CREATION_DATE,
                 CREATED_BY,
                 LAST_UPDATE_LOGIN,
                 T_A_SEGMENT,
                 T_A_AREA_1,
                 T_A_TYPE,
                 ORG_CODE,
                 BUSINESS_LICENSE_END_DATE,
                 PMS_CODE,
                 ORG_ID,
                 PRO_HANDOVER_DATE,
                 FOREIGN_RENT_AREA,
                 SIGN_YEAR,
                 COMPANY_NAME,
                 REMOVE_DATE,
                 EFFECTIVE_DATE,
                 EXPIRATION_DATE,
                 CURRENT_FLAG,
                 ETL_DATE,
                 OLD_T_A_NAME,
                 BRAND_CODE,
                 EQUITY_NAME,
                 (EQUITY_RATE / 100.00) AS EQUITY_RATE,
                 nvl(BUSINESS_DATE, PRE_BUSINESS_DATE) UNI_BUSINESS_DATE,
                 floor(pre_business_date - sign_date) AS DIFF_PRE_SIGN,
                 floor(business_date - pre_business_date) AS DIFF_B_PRE,
                 floor(business_date - sign_date) AS DIFF_B_SIGN,
                 CASE
                   WHEN TERMINATION_DATE IS NULL THEN
                    'N'
                   ELSE
                    'Y'
                 END IS_FLAG,
                 TERMINATION_DATE,
                 to_char(add_months(effective_date, -1), 'YYYY-MM') effective_month_act,
                 to_char(add_months(expiration_date, -1), 'YYYY-MM') expiration_month_act,
                 to_char(add_months(effective_date, 12), 'YYYY-MM') effective_month_bgt,
                 to_char(add_months(expiration_date, 12), 'YYYY-MM') expiration_month_bgt
            FROM T_A,
                 (select t.T_A_code u_T_A_code,
                         max(t.effective_date) u_effective_date
                    from T_A t
                   group by t.T_A_code, to_char(t.effective_date, 'YYYY-MM')) t
           WHERE T_A.T_A_code = t.u_T_A_code
             and T_A.effective_date = t.u_effective_date),
SAWITH0 AS
 (select distinct T_A.T_A_SHORT_NAME as c1,
                  cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER) as c2,
                  T_B.CAL_MONTH_NAME as c3,
                  T_A.EFFECTIVE_MONTH_ACT as c4,
                  T_A.EFFECTIVE_MONTH_BGT as c5,
                  T_A.T_A_CODE as c6,
                  T_A.T_A_COOPER as c7,
                  T_B.CAL_MONTH_ID as c8
    from T_B T_B /* Dim-T_B */,
         T_C T_C /* 
                  Fact-T_C_Act */,
          T_A
   where (T_C.T_A_CODE = T_A.T_A_CODE and
         T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and
         T_C.PERIOD_MON < T_A.EXPIRATION_MONTH_ACT and
         T_B.CAL_YEAR_ID = '2013' and
         T_C.PERIOD_MON >= T_A.EFFECTIVE_MONTH_ACT and
         T_C.PERIOD_MON >= '2013-05' and
         (substr(T_C.T_A_CODE, 1, 1) in ('A', 'B')) and
         (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and
         (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'),
                                   '9999'),
                         '-'),
                  TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >=
         T_C.PERIOD_MON or T_A.TERMINATION_DATE is null) and
         T_B.CAL_MONTH_NAME >= '2013-05')),
SACOMMON3694452 AS
 (select sum(T_C.AVHIRE_QTY_ACT) as c9,
         sum(T_C.RMHIRE_QTY_ACT) as c10,
         T_A.EFFECTIVE_MONTH_ACT as c11,
         T_A.EFFECTIVE_MONTH_BGT as c12,
         T_A.T_A_CODE as c13,
         T_A.T_A_COOPER as c14,
         cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER) as c15,
         T_B.CAL_MONTH_ID as c16,
         grouping_id(T_A.T_A_CODE,
                     T_A.T_A_COOPER,
                     T_A.EFFECTIVE_MONTH_ACT,
                     T_A.EFFECTIVE_MONTH_BGT,
                     cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER),
                     T_B.CAL_MONTH_ID) as c17
    from T_B T_B /* Dim-T_B */,
         T_C T_C /* 
                  Fact-T_C_Act */,
          T_A
   where (T_B.CAL_MONTH_NAME < T_A.EXPIRATION_MONTH_ACT and
         T_B.CAL_YEAR_ID = '2013' and
         T_C.T_A_CODE = T_A.T_A_CODE and
         T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and
         T_B.CAL_MONTH_NAME >= T_A.EFFECTIVE_MONTH_ACT and
         T_B.CAL_MONTH_NAME >= '2013-05' and
         (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and
         (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'),
                                   '9999'),
                         '-'),
                  TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >=
         T_B.CAL_MONTH_NAME or T_A.TERMINATION_DATE is null))
   group by grouping sets((cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER), T_A.EFFECTIVE_MONTH_BGT, T_A.EFFECTIVE_MONTH_ACT, T_A.T_A_COOPER, T_A.T_A_CODE),(T_B.CAL_MONTH_ID, cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER), T_A.EFFECTIVE_MONTH_BGT, T_A.EFFECTIVE_MONTH_ACT, T_A.T_A_CODE))),
SAWITH1 AS
 (select sum(T_C.AVHIRE_QTY_ACT) as c7,
         sum(T_C.RMHIRE_QTY_ACT) as c8,
         T_A.EFFECTIVE_MONTH_ACT as c9,
         T_A.EFFECTIVE_MONTH_BGT as c10,
         T_A.T_A_CODE as c11,
         T_A.T_A_COOPER as c12
    from T_B T_B /* Dim-T_B */,
         T_C T_C /* 
                  Fact-T_C_Act */,
          T_A
   where (T_C.T_A_CODE = T_A.T_A_CODE and
         T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and
         T_B.CAL_YEAR_ID = '2013' and
         T_C.PERIOD_MON < T_A.EXPIRATION_MONTH_ACT and
         (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and
         (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'),
                                   '9999'),
                         '-'),
                  TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >=
         T_C.PERIOD_MON or T_A.TERMINATION_DATE is null) and
         T_C.PERIOD_MON >= T_A.EFFECTIVE_MONTH_ACT and
         T_C.PERIOD_MON >= '2013-05' and
         T_B.CAL_MONTH_NAME >= '2013-05')
   group by T_A.T_A_CODE,
            T_A.T_A_COOPER,
            T_A.EFFECTIVE_MONTH_ACT,
            T_A.EFFECTIVE_MONTH_BGT),
SAWITH2 AS
 (select D4.c7  as c1,
         D4.c8  as c2,
         D1.c7  as c3,
         D1.c1  as c4,
         D2.c9  as c5,
         D2.c10 as c6,
         D3.c10 as c7,
         D1.c2  as c8,
         D1.c3  as c9,
         D1.c4  as c10,
         D1.c5  as c11,
         D1.c6  as c12
    from ((SAWITH0 D1 inner join SACOMMON3694452 D2 On
          SYS_OP_MAP_NONNULL(D2.c15) = SYS_OP_MAP_NONNULL(D1.c2) and
          D2.c12 = D1.c5 and D2.c11 = D1.c4 and
          SYS_OP_MAP_NONNULL(D2.c13) = SYS_OP_MAP_NONNULL(D1.c6) and
          SYS_OP_MAP_NONNULL(D2.c14) = SYS_OP_MAP_NONNULL(D1.c7)) inner join
          SACOMMON3694452 D3 On
          SYS_OP_MAP_NONNULL(D3.c15) = SYS_OP_MAP_NONNULL(D1.c2) and
          D3.c12 = D1.c5 and D3.c11 = D1.c4 and
          SYS_OP_MAP_NONNULL(D3.c16) = SYS_OP_MAP_NONNULL(D1.c8) and
          SYS_OP_MAP_NONNULL(D3.c13) = SYS_OP_MAP_NONNULL(D1.c6))
   inner join SAWITH1 D4
      On D4.c10 = D1.c5
     and D4.c9 = D1.c4
     and SYS_OP_MAP_NONNULL(D4.c11) = SYS_OP_MAP_NONNULL(D1.c6)
     and SYS_OP_MAP_NONNULL(D4.c12) = SYS_OP_MAP_NONNULL(D1.c7)
   where (D2.c17 = 1 and D3.c17 = 16))
select D1.c1  as c1,
       D1.c2  as c2,
       D1.c3  as c3,
       D1.c4  as c4,
       D1.c5  as c5,
       D1.c6  as c6,
       D1.c7  as c7,
       D1.c8  as c8,
       D1.c9  as c9,
       D1.c10 as c10
  from (select 0 as c1,
               D1.c3 as c2,
               D1.c4 as c3,
               '经营利润率' as c4,
               D1.c8 as c5,
               case
                 when D1.c9 <= '2013-10' then
                  case
                    when D1.c5 = 0 then
                     0
                    else
                     cast(D1.c6 as DOUBLE PRECISION) / nullif(D1.c5, 0) * 100.0
                  end
                 else
                  NULL
               end as c6,
               case
                 when D1.c9 <= '2013-10' then
                  case
                    when D1.c1 = 0 then
                     0
                    else
                     cast(D1.c2 as DOUBLE PRECISION) / nullif(D1.c1, 0) * 100.0
                  end
                 else
                  NULL
               end as c7,
               D1.c10 as c8,
               D1.c11 as c9,
               D1.c12 as c10,
               ROW_NUMBER() OVER(PARTITION BY D1.c3, D1.c4, D1.c8, D1.c10, D1.c11, D1.c12 ORDER BY D1.c3 ASC, D1.c4 ASC, D1.c8 ASC, D1.c10 ASC, D1.c11 ASC, D1.c12 ASC) as c11
          from SAWITH2 D1) D1
 where (D1.c11 = 1);
改写后网友反馈


 有教无类 18:40:09 
起作用了没
2013-11-09
     11:05:23 
起作用了
     11:05:33 
0.8秒  



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值