信不信老板看到这样的前台SQL fired 掉你

 --- explain analyze 
 with T0 as (
    select
        sum(A.NUM) as NUM
    from
        (
        select
            case
                when count(1) = 1 then 2
                else 0
            end as NUM
        from
            DWPUB.TD_PRC_PARTNER_STAFF
        where
            STATE = 'A'
            and STAFF_ID = '12468'
    union all
        select
            count(1) as NUM
        from
            DWPUB.TD_BFM_STAFF
        where
            STATE = 'A'
            and STAFF_ID not in (
            select
                STAFF_ID
            from
                DWPUB.TD_PRC_PARTNER_STAFF )
            and STAFF_ID = '12468' ) A ),
    T1 as (
    select
        distinct PARTNER_ID
    from
        DWPUB.TD_PARTNER_PRIV
    where
        JOB_CODE = 'WCT_MS-SERVICE-DESK'
        and STAFF_ID = '12468'
        and 2 in (
        select
            NUM
        from
            T0 )
        and JOB_CODE in ('Partner - Management', 'Partner - Finance', 'Partner - Operation')
union all
    select
        distinct PARTNER_ID
    from
        DWPUB.TD_PARTNER_PRIV
    where
        1 in (
        select
            NUM
        from
            T0 ) )
    select
        a.SERVICE_TYPE as service_type,
        sum(coalesce(a.TIMES, 0)) as trans_num ,
        sum(coalesce(a.PARTNER_TOTAL, 0)) / 1000000 as trans_total ,
        sum(coalesce(a.DEDUCTIONS_FEES, 0)) / 1000000 as deduc_amount ,
        sum(coalesce(a.PARTNER_NETT, 0)) / 1000000 as partner_nett_amt ,
        coalesce(a.PARTNER_TAX_ACCT_ITEM_TYPE_NAME_RATE1, 'N/A') as "Applicable Tax1" ,
        sum(coalesce(a.PARTNER_TAX_AMOUNT_NETT1 / 1000000, 0)) as "Applicable Tax Amount1" ,
        coalesce(a.PARTNER_TAX_ACCT_ITEM_TYPE_NAME_RATE2, 'N/A') as "Applicable Tax2" ,
        sum(coalesce(a.PARTNER_TAX_AMOUNT_NETT2 / 1000000, 0)) as "Applicable Tax Amount2" ,
        sum(coalesce(a.PARTNER_NETT, 0)) / 1000000 + sum(coalesce(a.PARTNER_TAX_AMOUNT_NETT1 / 1000000, 0)) + sum(coalesce(a.PARTNER_TAX_AMOUNT_NETT2 / 1000000, 0)) as "Partner Nett Amt (RM) After Tax"
    from
        dwfct.TFPXL_PRM_OVERALL_REV_MON a
    where
        a.stat_date = '202304'
        and a.partner_id in (
        select
            partner_id
        from
            T1 )
        and A.PARTNER_ID in ('20330')
    group by
        a.SERVICE_TYPE,
        a.PARTNER_TAX_ACCT_ITEM_TYPE_NAME_RATE1,
        a.PARTNER_TAX_ACCT_ITEM_TYPE_NAME_RATE2,
        a.ORDER_FLAG
    order by
        a.ORDER_FLAG
 
Result  (cost=0.00..1325756.13 rows=1 width=80)
  Rows out:  1 rows with 72 ms to end.
  ->  Sort  (cost=0.00..1325756.13 rows=1 width=80)
        Sort Key: tfpxl_prm_overall_rev_mon.order_flag
        Sort Method:  quicksort  Memory: 33KB
        Rows out:  1 rows with 72 ms to end.
        Executor memory:  33K bytes.
        Work_mem used:  33K bytes. Workfile: (0 spilling)
        ->  Sequence  (cost=0.00..1325756.13 rows=1 width=88)
              Rows out:  1 rows with 71 ms to first row, 72 ms to end.
              ->  Shared Scan (share slice:id 0:0)  (cost=0.00..1293.00 rows=1 width=1)
                    Rows out:  1 rows with 6.925 ms to first row, 6.932 ms to end.
                    ->  Materialize  (cost=0.00..1293.00 rows=1 width=1)
                          Rows out:  0 rows with 6.877 ms to end.
                          ->  Aggregate  (cost=0.00..1293.00 rows=1 width=8)
                                Rows out:  1 rows with 6.595 ms to end.
                                Executor memory:  8K bytes.
                                ->  Append  (cost=0.00..1293.00 rows=1 width=8)
                                      Rows out:  2 rows with 2.848 ms to first row, 6.574 ms to end.
                                      ->  Result  (cost=0.00..431.00 rows=1 width=8)
                                            Rows out:  1 rows with 2.848 ms to first row, 2.849 ms to end.
                                            ->  Result  (cost=0.00..431.00 rows=1 width=4)
                                                  Rows out:  1 rows with 2.845 ms to end.
                                                  ->  Aggregate  (cost=0.00..431.00 rows=1 width=8)
                                                        Rows out:  1 rows with 2.842 ms to end.
                                                        ->  Gather Motion 40:1  (slice7; segments: 40)  (cost=0.00..431.00 rows=1 width=8)
                                                              Rows out:  40 rows at destination with 0.011 ms to first row, 2.822 ms to end.
                                                              ->  Aggregate  (cost=0.00..431.00 rows=1 width=8)
                                                                    Rows out:  Avg 1.0 rows x 40 workers.  Max 1 rows (seg0) with 1.659 ms to end.
                                                                    ->  Table Scan on td_prc_partner_staff  (cost=0.00..431.00 rows=1 width=1)
                                                                          Filter: st