--- 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
信不信老板看到这样的前台SQL fired 掉你
于 2023-05-08 14:08:44 首次发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)