我这里有一条oracle执行的sql，执行单时间挺长，需要你帮我对它进行优化，提高查询效率，sql语句如下
select
LAST_DAY(TO_DATE(a.PERIOD_NAME,'YYYY-MM')) as STATDATE,
case
when b.MANAGER_COM = '869701' then '860097'
else b.MANAGER_COM
end as COMCODE,
null as COMNAME,
'3' as COMLEVEL,
substr(b.MANAGER_COM,1,4) as PARENT_CODE,
'YX' as CHANNEL_CODE,
'营销' as CHANNEL_NAME,
sum(case when a.line_name in ('L_200-营销首年佣金','L_215-中心城市首年佣金') then a.actual_used_of else null END) as FIRST_FEE_A, -- 首期佣金 累计达成额
sum(case when a.line_name in ('L_200-营销基本法','L_215-中心城市基本法') then a.actual_used_of else null END) as FIRST_FEE_JBEN_A, -- 首期基本法 累计达成额
sum(case when a.line_name in ('L_215-中心城市业务费用','L_200-营销业务费用小计', 'L_200-营销培训费用小计', 'L_200-营销专项费用小计', 'L_200-营销聘才专项', 'L_200-营销将星/利剑/续航等', 'L_200-营销磐龙专项等') then a.actual_used_of else null END) as FIRST_FEE_YWTDJY_A, -- 首期业务推动经营费用 累计达成额
sum(case when a.line_name in ('L_200-营销业务推动费用','L_215-中心城市业务推动费用','L_215-中心城市协解费用专项','L_215-中心城市人力发展费用','L_200-营销人力发展费用','L_200-营销聘才专项', 'L_200-营销将星/利剑/续航等', 'L_200-营销磐龙专项等') then a.actual_used_of else null END) as FIRST_FEE_JL_A,-- 首期激励方案费用 累计达成额
sum(case when a.line_name in ('L_200-营销培训费用小计','L_215-中心城市培训费用小计') then a.actual_used_of else null END) as FIRST_FEE_YWPX_A, -- 首期业务培训费用 累计达成额
sum(case when a.line_name in ('L_200-营销专项费用小计','L_215-中心城市专项费用小计') then a.actual_used_of else null END) as FIRST_FEE_QT_A, -- 首期其他费用 累计达成额
null as FIRST_FEE_JX_A, -- 首期绩效 累计达成额
null as FIRST_FEE_SXF_A, -- 首期手续费 累计达成额
sum(case when a.line_name in ('L_200-营销首年佣金','L_215-中心城市首年佣金','L_200-营销基本法','L_215-中心城市基本法','L_215-中心城市业务费用','L_200-营销业务费用小计', 'L_200-营销培训费用小计', 'L_200-营销专项费用小计', 'L_200-营销聘才专项', 'L_200-营销将星/利剑/续航等', 'L_200-营销磐龙专项等') then a.actual_used_of else null END) as FIRST_FEE_BDSJ_A
from
MISOPER.YGBX_REPORT_DETAILS_INTERFACE_TMP a
left join MISOPER.T_SLFS_COM_MAP b on
a.company_code = b.OF_SEGMENT1_VALUE
where (b.MANAGER_COM != '' or b.MANAGER_COM is not null) and length(b.MANAGER_COM) = 6 and b.MANAGER_COM not in ('863499','860000','869701','860606','860004')
group by b.MANAGER_COM,a.PERIOD_NAME
union
select
LAST_DAY(TO_DATE(a.PERIOD_NAME,'YYYY-MM')) as STATDATE,
case
when b.MANAGER_COM = '869701' then '860097'
else b.MANAGER_COM
end as COMCODE,
null as COMNAME,
'3' as COMLEVEL,
substr(b.MANAGER_COM,1,4) as PARENT_CODE,
'KJ' as CHANNEL_CODE,
'客经' as CHANNEL_NAME,
sum(case when a.line_name = 'L_0-续期首年佣金' then a.actual_used_of else null END) as FIRST_FEE_A, -- 首期佣金 累计达成额
sum(case when a.line_name in ('L_0-续期聘才费用','L_0-续期基本法成本','L_0-续期展业福利费') then a.actual_used_of else null END) as FIRST_FEE_JBEN_A, -- 首期基本法 累计达成额
sum(case when a.line_name = 'L_0-续期业务费用-其中展业' then a.actual_used_of else null END) as FIRST_FEE_YWTDJY_A, -- 首期业务推动经营费用 累计达成额
sum(case when a.line_name in ('L_0-客经业务推动','L_0-客经业推专项','L_0-客经协解专项','L_0-客经人力发展','L_0-客经人发专项') then a.actual_used_of else null END) as FIRST_FEE_JL_A,-- 首期激励方案费用 累计达成额
sum(case when a.line_name = 'L_0-客经培训' then a.actual_used_of else null END) as FIRST_FEE_YWPX_A, -- 首期业务培训费用 累计达成额
sum(case when a.line_name = 'L_0-客经社保' then a.actual_used_of else null END) as FIRST_FEE_QT_A, -- 首期其他费用 累计达成额
null as FIRST_FEE_JX_A, -- 首期绩效 累计达成额
null as FIRST_FEE_SXF_A, -- 首期手续费 累计达成额
sum(case when a.line_name in ('L_0-续期首年佣金','L_0-续期聘才费用','L_0-续期基本法成本','L_0-续期展业福利费','L_0-续期业务费用-其中展业') then a.actual_used_of else null END) as FIRST_FEE_BDSJ_A
from
MISOPER.YGBX_REPORT_DETAILS_INTERFACE_TMP a
left join MISOPER.T_SLFS_COM_MAP b on
a.company_code = b.OF_SEGMENT1_VALUE
where (b.MANAGER_COM != '' or b.MANAGER_COM is not null) and length(b.MANAGER_COM) = 6 and b.MANAGER_COM not in ('863499','860000','869701','860606','860004')
group by b.MANAGER_COM,a.PERIOD_NAME
union
select
LAST_DAY(TO_DATE(a.PERIOD_NAME,'YYYY-MM')) as STATDATE,
case
when b.MANAGER_COM = '869701' then '860097'
else b.MANAGER_COM
end as COMCODE,
null as COMNAME,
'3' as COMLEVEL,
substr(b.MANAGER_COM,1,4) as PARENT_CODE,
'ZX' as CHANNEL_CODE,
'振兴' as CHANNEL_NAME,
sum(case when a.line_name = 'L_218-振兴首年佣金' then a.actual_used_of else null END) as FIRST_FEE_A, -- 首期佣金 累计达成额
sum(case when a.line_name = 'L_218-振兴基本法' then a.actual_used_of else null END) as FIRST_FEE_JBEN_A, -- 首期基本法 累计达成额
sum(case when a.line_name = 'L_218-振兴首期业务费用' then a.actual_used_of else null END) as FIRST_FEE_YWTDJY_A, -- 首期业务推动经营费用 累计达成额
sum(case when a.line_name in ('L_218-振兴人力发展费用','L_218-振兴专项费用小计','L_218-振兴业务推动费用') then a.actual_used_of else null END) as FIRST_FEE_JL_A, -- 首期激励方案费用 累计达成额
sum(case when a.line_name = 'L_218-振兴培训费用小计' then a.actual_used_of else null END) as FIRST_FEE_YWPX_A, -- 首期业务培训费用 累计达成额
null as FIRST_FEE_QT_A, -- 首期其他费用 累计达成额
null as FIRST_FEE_JX_A, -- 首期绩效 累计达成额
null as FIRST_FEE_SXF_A, -- 首期手续费 累计达成额
sum(case when a.line_name in ('L_218-振兴首年佣金','L_218-振兴基本法','L_218-振兴首期业务费用') then a.actual_used_of else null END) as FIRST_FEE_BDSJ_A
from
MISOPER.YGBX_REPORT_DETAILS_INTERFACE_TMP a
left join MISOPER.T_SLFS_COM_MAP b on
a.company_code = b.OF_SEGMENT1_VALUE
where (b.MANAGER_COM != '' or b.MANAGER_COM is not null) and length(b.MANAGER_COM) = 6 and b.MANAGER_COM not in ('863499','860000','869701','860606','860004')
group by b.MANAGER_COM,a.PERIOD_NAME
union
select
LAST_DAY(TO_DATE(a.PERIOD_NAME,'YYYY-MM')) as STATDATE,
case
when b.MANAGER_COM = '869701' then '860097'
else b.MANAGER_COM
end as COMCODE,
null as COMNAME,
'3' as COMLEVEL,
substr(b.MANAGER_COM,1,4) as PARENT_CODE,
'TXCJ' as CHANNEL_CODE,
'团险BBC' as CHANNEL_NAME,
sum(case when a.line_name = 'L_100-团险BBC首年佣金' then a.actual_used_of else null END) as FIRST_FEE_A, -- 首期佣金 累计达成额
sum(case when a.line_name = 'L_100-团险BBC人力成本' then a.actual_used_of else null END) as FIRST_FEE_JBEN_A, -- 首期基本法 累计达成额
sum(case when a.line_name in ('L_100-团险BBC业务招待费','L_100-团险BBC业务推动费用','L_100-团险BBC协解专项','L_100-团险BBC手续费（含预提）') then a.actual_used_of else null END) as FIRST_FEE_YWTDJY_A, -- 首期业务推动经营费用 累计达成额
null as FIRST_FEE_JL_A,-- 首期激励方案费用 累计达成额
null as FIRST_FEE_YWPX_A, -- 首期业务培训费用 累计达成额
null as FIRST_FEE_QT_A, -- 首期其他费用 累计达成额
null as FIRST_FEE_JX_A, -- 首期绩效 累计达成额
null as FIRST_FEE_SXF_A, -- 首期手续费 累计达成额
sum(case when a.line_name in ('L_100-团险BBC首年佣金','L_100-团险BBC人力成本','L_100-团险BBC业务招待费','L_100-团险BBC业务推动费用','L_100-团险BBC协解专项','L_100-团险BBC手续费（含预提）') then a.actual_used_of else null END) as FIRST_FEE_BDSJ_A
from
MISOPER.YGBX_REPORT_DETAILS_INTERFACE_TMP a
left join MISOPER.T_SLFS_COM_MAP b on
a.company_code = b.OF_SEGMENT1_VALUE
where (b.MANAGER_COM != '' or b.MANAGER_COM is not null) and length(b.MANAGER_COM) = 6 and b.MANAGER_COM not in ('863499','860000','869701','860606','860004')
group by b.MANAGER_COM,a.PERIOD_NAME
union
select
LAST_DAY(TO_DATE(a.PERIOD_NAME,'YYYY-MM')) as STATDATE,
case
when b.MANAGER_COM = '869701' then '860097'
else b.MANAGER_COM
end as COMCODE,
null as COMNAME,
'3' as COMLEVEL,
substr(b.MANAGER_COM,1,4) as PARENT_CODE,
'CSP' as CHANNEL_CODE,
'银保CSP' as CHANNEL_NAME,
sum(case when a.line_name in ('L_300-银保CSP首年佣金','L_300-银保CSP产融首年佣金') then a.actual_used_of else null END) as FIRST_FEE_A, -- 首期佣金 累计达成额
sum(case when a.line_name in ('L_300-银保CSP基本法','L_300-银保CSP产融基本法') then a.actual_used_of else null END) as FIRST_FEE_JBEN_A, -- 首期基本法 累计达成额
sum(case when a.line_name in ('L_300-银保CSP业务推动费用','L_300-银保CSP对公产融业务费用','L_300-银保CSP直营产融业务费用','L_300-银保CSP协解专项') then a.actual_used_of else null END) as FIRST_FEE_YWTDJY_A, -- 首期业务推动经营费用 累计达成额
null as FIRST_FEE_JL_A,-- 首期激励方案费用 累计达成额
null as FIRST_FEE_YWPX_A, -- 首期业务培训费用 累计达成额
null as FIRST_FEE_QT_A, -- 首期其他费用 累计达成额
null as FIRST_FEE_JX_A, -- 首期绩效 累计达成额
null as FIRST_FEE_SXF_A, -- 首期手续费 累计达成额
sum(case when a.line_name in ('L_300-银保CSP首年佣金','L_300-银保CSP产融首年佣金','L_300-银保CSP基本法','L_300-银保CSP产融基本法','L_300-银保CSP业务推动费用','L_300-银保CSP对公产融业务费用','L_300-银保CSP直营产融业务费用','L_300-银保CSP协解专项') then a.actual_used_of else null END) as FIRST_FEE_BDSJ_A
from
MISOPER.YGBX_REPORT_DETAILS_INTERFACE_TMP a
left join MISOPER.T_SLFS_COM_MAP b on
a.company_code = b.OF_SEGMENT1_VALUE
where (b.MANAGER_COM != '' or b.MANAGER_COM is not null) and length(b.MANAGER_COM) = 6 and b.MANAGER_COM not in ('863499','860000','869701','860606','860004')
group by b.MANAGER_COM,a.PERIOD_NAME
union
select
LAST_DAY(TO_DATE(a.PERIOD_NAME,'YYYY-MM')) as STATDATE,
case
when b.MANAGER_COM = '869701' then '860097'
else b.MANAGER_COM
end as COMCODE,
null as COMNAME,
'3' as COMLEVEL,
substr(b.MANAGER_COM,1,4) as PARENT_CODE,
'CT' as CHANNEL_CODE,
'银保传统' as CHANNEL_NAME,
null as FIRST_FEE_A, -- 首期佣金 累计达成额
sum(case when a.line_name = 'L_300-银保传统人力基本薪资' then a.actual_used_of else null END) as FIRST_FEE_JBEN_A, -- 首期基本法 累计达成额
sum(case when a.line_name in ('L_300-银保传统业务费用小计','L_300-银保专项费用汇总','L_300-银保传统满期协解专项') then a.actual_used_of else null END) as FIRST_FEE_YWTDJY_A, -- 首期业务推动经营费用 累计达成额
null as FIRST_FEE_JL_A,-- 首期激励方案费用-业务推动费用 累计达成额
null as FIRST_FEE_YWPX_A, -- 首期业务培训费用 累计达成额
null as FIRST_FEE_QT_A, -- 首期其他费用 累计达成额
sum(case when a.line_name = 'L_300-银保传统人力成本' then a.actual_used_of else null END) as FIRST_FEE_JX_A, -- 首期绩效 累计达成额
sum(case when a.line_name = 'L_300-银保传统手续费' then a.actual_used_of else null END) as FIRST_FEE_SXF_A, -- 首期手续费 累计达成额
sum(case when a.line_name in ('L_300-银保传统业务费用小计','L_300-银保专项费用汇总','L_300-银保传统满期协解专项','L_300-银保传统人力基本薪资','L_300-银保传统人力成本','L_300-银保传统手续费') then a.actual_used_of else null END) as FIRST_FEE_BDSJ_A --变动实际费用
from
MISOPER.YGBX_REPORT_DETAILS_INTERFACE_TMP a
left join MISOPER.T_SLFS_COM_MAP b on
a.company_code = b.OF_SEGMENT1_VALUE
where (b.MANAGER_COM != '' or b.MANAGER_COM is not null) and length(b.MANAGER_COM) = 6 and b.MANAGER_COM not in ('863499','860000','869701','860606','860004')
group by b.MANAGER_COM,a.PERIOD_NAME
union
select
LAST_DAY(TO_DATE(a.PERIOD_NAME,'YYYY-MM')) as STATDATE,
case
when b.MANAGER_COM = '869701' then '860097'
else b.MANAGER_COM
end as COMCODE,
null as COMNAME,
'3' as COMLEVEL,
substr(b.MANAGER_COM,1,4) as PARENT_CODE,
'JD' as CHANNEL_CODE,
'经代' as CHANNEL_NAME,
null as FIRST_FEE_A, -- 首期佣金 累计达成额
null as FIRST_FEE_JBEN_A, -- 首期基本法 累计达成额
sum(case when a.line_name in ('L_500-经代业务费用','L_500-经代预提') then a.actual_used_of else null END) as FIRST_FEE_YWTDJY_A, -- 首期业务推动经营费用 累计达成额
null as FIRST_FEE_JL_A,-- 首期激励方案费用-业务推动费用 累计达成额
null as FIRST_FEE_YWPX_A, -- 首期业务培训费用 累计达成额
null as FIRST_FEE_QT_A, -- 首期其他费用 累计达成额
null as FIRST_FEE_JX_A, -- 首期绩效 累计达成额
sum(case when a.line_name = 'L_500-经代手续费' then a.actual_used_of else null END) as FIRST_FEE_SXF_A, -- 首期手续费 累计达成额
sum(case when a.line_name in ('L_500-经代业务费用','L_500-经代预提','L_500-经代手续费') then a.actual_used_of else null END) as FIRST_FEE_BDSJ_A --变动实际费用
from
MISOPER.YGBX_REPORT_DETAILS_INTERFACE_TMP a
left join MISOPER.T_SLFS_COM_MAP b on
a.company_code = b.OF_SEGMENT1_VALUE
where (b.MANAGER_COM != '' or b.MANAGER_COM is not null) and length(b.MANAGER_COM) = 6 and b.MANAGER_COM not in ('863499','860000','869701','860606','860004')
group by b.MANAGER_COM,a.PERIOD_NAME
扫一扫
1623
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
