SQL优化实录20200327

--20180719，SR-962 v0.2版需求变更调整
select 
t.分公司,
(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end) as 产品名称法人,
count(distinct t.保单号 ) as 当日新契约件数,
0 as 当日犹豫期退保件数,
0 as 当月新契约件数,
0 as 当月犹豫期退保件数,
0 as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_FR_TRANSFORM_LIST t
where t.个团='团险'/*and t.行员单='否'*/
and t.类型名称<>'续期'
and  not (t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
and t.业绩统计日期 between # prompt('p_day_end2','string')# and # prompt('p_day_end3','string')#
and t.业绩统计日期<=current_date-1 DAYS
and t.保单号 in (select 保单号 from DM_FR_TRANSFORM_LIST 
                        where 个团='团险'/*and 行员单='否'*/and 业绩统计日期 between # prompt('p_day_end2','string')# and # prompt('p_day_end3','string')#
                        group by 保单号 having sum(年化保费)>0)
and t.产品名称法人<>'团体安康' AND t.年化保费<>0
group by t.分公司,(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end)

union  all

select 
t.分公司,
(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end) as 产品名称法人,
0 as 当日新契约件数,
count(distinct t.保单号) as 当日犹豫期退保件数,
0 as 当月新契约件数,
0 as 当月犹豫期退保件数,
0 as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_FR_TRANSFORM_LIST t
where t.个团='团险'/*and t.行员单='否'*/ 
and t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人')
and t.业绩统计日期 between # prompt('p_day_end2','string')# and # prompt('p_day_end3','string')#
and t.业绩统计日期<=current_date-1 DAYS
and t.产品名称法人<>'团体安康' AND t.年化保费<>0
group by t.分公司,(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end)


union all

select 
t.分公司,
(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end) as 产品名称法人,
count(distinct case when t.类型名称<>'续期' then t.保单号 else null end ) as 当日新契约件数,
count(distinct (case when (t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人')or t.保单状态2='满期')then t.保单号 else null end)) as 当日犹豫期退保件数,
0 as 当月新契约件数,
0 as 当月犹豫期退保件数,
0 as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_FR_TRANSFORM_LIST t
where t.个团='个险'/*and t.行员单='否'*/  AND t.年化保费<>0
and t.业绩统计日期 between # prompt('p_day_end2','string')# and # prompt('p_day_end3','string')#
and t.业绩统计日期<=current_date-1 DAYS 
and t.保单号 in (select 保单号 from DM_FR_TRANSFORM_LIST 
                        where 个团='个险'/*and 行员单='否'*/and 业绩统计日期 between # prompt('p_day_end2','string')# and # prompt('p_day_end3','string')#
                        group by 保单号 having sum(年化保费)>0)
group by t.分公司,(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end)

union all

select 
t.分公司,
(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end) as 产品名称法人,
0 as 当日新契约件数,
0 as 当日犹豫期退保件数,
count(distinct t.保单号 )  as 当月新契约件数,
0 as 当月犹豫期退保件数,
0 as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_FR_TRANSFORM_LIST t
where t.个团='团险'/*and t.行员单='否'*/  AND t.年化保费<>0
and t.类型名称<>'续期'
and not (t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
and t.业绩统计日期 between left(# prompt('p_day_end3','string')#,7)||'-01' and # prompt('p_day_end3','string')#
and t.业绩统计日期<=current_date-1 DAYS
and t.保单号 in (select 保单号 from DM_FR_TRANSFORM_LIST 
                        where 个团='团险'/*and 行员单='否'*/
                           and not (类型名称='保全付'and 保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
                          and 业绩统计日期 between left(# prompt('p_day_end3','string')#,7)||'-01' and # prompt('p_day_end3','string')#
                        group by 保单号 having sum(年化保费)>0)
and t.产品名称法人<>'团体安康'
group by t.分公司,(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end)


union all

select 
t.分公司,
(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end) as 产品名称法人,
0 as 当日新契约件数,
0 as 当日犹豫期退保件数,
0 as 当月新契约件数,
count(distinct t.保单号) as 当月犹豫期退保件数,
0 as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_FR_TRANSFORM_LIST t
where t.个团='团险'/*and t.行员单='否'*/  AND t.年化保费<>0
and t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人')
and t.业绩统计日期 between left(# prompt('p_day_end3','string')#,7)||'-01' and # prompt('p_day_end3','string')#
and t.业绩统计日期<=current_date-1 DAYS
and t.产品名称法人<>'团体安康'
group by t.分公司,(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end)



union all

select 
t.分公司,
(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end) as 产品名称法人,
0 as 当日新契约件数,
0 as 当日犹豫期退保件数,
count(distinct case when t.类型名称<>'续期' then t.保单号 else null end ) as 当月新契约件数,
count(distinct (case when t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人')then t.保单号 else null end)) as 当月犹豫期退保件数,
0 as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_FR_TRANSFORM_LIST t
where t.个团='个险'/*and t.行员单='否'*/  AND t.年化保费<>0
and t.业绩统计日期 between left(# prompt('p_day_end3','string')#,7)||'-01' and # prompt('p_day_end3','string')#
and t.业绩统计日期<=current_date-1 DAYS
and t.保单号 in (select 保单号 from DM_FR_TRANSFORM_LIST 
                        where 个团='个险'/*and 行员单='否'*/and 业绩统计日期 between left(# prompt('p_day_end3','string')#,7)||'-01' and # prompt('p_day_end3','string')#
                        group by 保单号 having sum(年化保费)>0)
group by t.分公司,(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end)


union all

select 
t.分公司,
(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end) as 产品名称法人,
0 as 当日新契约件数,
0 as 当日犹豫期退保件数,
0  as 当月新契约件数,
0 as 当月犹豫期退保件数,
count(distinct t.保单号) as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_FR_TRANSFORM_LIST t
where t.个团='团险'/*and t.行员单='否'*/  AND t.年化保费<>0
and t.类型名称<>'续期'
and not (t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
and t.业绩统计日期 between left(# prompt('p_day_end3','string')#,4)||'-01-01' and # prompt('p_day_end3','string')#
and t.业绩统计日期<=current_date-1 DAYS
and t.保单号 in (select 保单号 from DM_FR_TRANSFORM_LIST 
                        where 个团='团险'/*and 行员单='否'*/
                         and not (类型名称='保全付'and 保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
                         and 业绩统计日期 between left(# prompt('p_day_end3','string')#,4)||'-01-01' and # prompt('p_day_end3','string')#
                        group by 保单号 having sum(年化保费)>0)
and t.产品名称法人<>'团体安康'
group by t.分公司,(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end)


union all

select 
t.分公司,
(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end) as 产品名称法人,
0 as 当日新契约件数,
0 as 当日犹豫期退保件数,
0  as 当月新契约件数,
0 as 当月犹豫期退保件数,
0 as 累计新契约件数,
count(distinct t.保单号) as 累计犹豫期退保件数
from DM_FR_TRANSFORM_LIST t
where t.个团='团险'/*and t.行员单='否'*/  AND t.年化保费<>0
and t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人')
and t.业绩统计日期 between left(# prompt('p_day_end3','string')#,4)||'-01-01' and # prompt('p_day_end3','string')#
and t.业绩统计日期<=current_date-1 DAYS
and t.产品名称法人<>'团体安康'
group by t.分公司,(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end)




union all

select 
t.分公司,
(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end) as 产品名称法人,
0 as 当日新契约件数,
0 as 当日犹豫期退保件数,
0 as 当月新契约件数,
0 as 当月犹豫期退保件数,
count(distinct case when  t.类型名称<>'续期' then t.保单号 else null end) as 累计新契约件数,
count(distinct (case when t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人')then t.保单号 else null end)) as 累计犹豫期退保件数
from DM_FR_TRANSFORM_LIST t
where t.个团='个险'/*and t.行员单='否'*/  AND t.年化保费<>0
and t.业绩统计日期 between left(# prompt('p_day_end3','string')#,4)||'-01-01' and # prompt('p_day_end3','string')#
and t.业绩统计日期<=current_date-1 DAYS
and t.保单号 in (select 保单号 from DM_FR_TRANSFORM_LIST 
                        where 个团='个险'/*and 行员单='否'*/and 业绩统计日期 between left(# prompt('p_day_end3','string')#,4)||'-01-01' and # prompt('p_day_end3','string')#
                        group by 保单号 having sum(年化保费)>0)
group by t.分公司,(case when t.产品名称法人='安康终身重疾'then '安康重疾'else t.产品名称法人 end)

union all
---取团体安康的件数，到分单
select 
t.分公司,
t.法人产品计划,
count(distinct t.分单号 ) as 当日新契约件数,
0 as 当日犹豫期退保件数,
0 as 当月新契约件数,
0 as 当月犹豫期退保件数,
0 as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_PAYEVENT_FD_LIST t
where t.法人产品计划='团体安康'  AND t.年化保费<>0
and t.类型名称<>'续期'
and  not (t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
and t.统计时间 between # prompt('p_day_end2','string')# and # prompt('p_day_end3','string')#
and t.统计时间<=current_date-1 DAYS
group by t.分公司,法人产品计划


union all

select 
t.分公司,
t.法人产品计划,
0 as 当日新契约件数,
count(distinct t.分单号) as 当日犹豫期退保件数,
0 as 当月新契约件数,
0 as 当月犹豫期退保件数,
0 as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_PAYEVENT_FD_LIST t
where t.法人产品计划='团体安康'  AND t.年化保费<>0
and t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人')
and t.统计时间 between # prompt('p_day_end2','string')# and # prompt('p_day_end3','string')#
and t.统计时间<=current_date-1 DAYS
group by t.分公司,t.法人产品计划

union all

select 
t.分公司,
t.法人产品计划,
0 as 当日新契约件数,
0 as 当日犹豫期退保件数,
count(distinct t.分单号)  as 当月新契约件数,
0 as 当月犹豫期退保件数,
0 as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_PAYEVENT_FD_LIST t
where t.法人产品计划='团体安康'  AND t.年化保费<>0
and t.类型名称<>'续期'
and not (t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
and t.统计时间 between left(# prompt('p_day_end3','string')#,7)||'-01' and # prompt('p_day_end3','string')#
and t.统计时间<=current_date-1 DAYS
and t.分单号 in (select 分单号 from DM_PAYEVENT_FD_LIST 
                        where 法人产品计划='团体安康'
                           and not (类型名称='保全付'and 保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
                          and 统计时间 between left(# prompt('p_day_end3','string')#,7)||'-01' and # prompt('p_day_end3','string')#
                        group by 分单号 having sum(年化保费)>0)
group by t.分公司,t.法人产品计划

union all

select 
t.分公司,
t.法人产品计划,
0 as 当日新契约件数,
0 as 当日犹豫期退保件数,
0 as 当月新契约件数,
count(distinct t.分单号) as 当月犹豫期退保件数,
0 as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_PAYEVENT_FD_LIST  t
where t.法人产品计划='团体安康'  AND t.年化保费<>0
and t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人')
and t.统计时间 between left(# prompt('p_day_end3','string')#,7)||'-01' and # prompt('p_day_end3','string')#
and t.统计时间<=current_date-1 DAYS
group by t.分公司,t.法人产品计划


union all

select 
t.分公司,
t.法人产品计划 ,
0 as 当日新契约件数,
0 as 当日犹豫期退保件数,
0  as 当月新契约件数,
0 as 当月犹豫期退保件数,
count(distinct t.分单号) as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_PAYEVENT_FD_LIST t
where t.法人产品计划='团体安康'  AND t.年化保费<>0
and t.类型名称<>'续期'
and not (t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
and t.统计时间 between left(# prompt('p_day_end3','string')#,4)||'-01-01' and # prompt('p_day_end3','string')#
and t.统计时间<=current_date-1 DAYS
and t.分单号 in (select 分单号 from DM_PAYEVENT_FD_LIST 
                        where 法人产品计划='团体安康'
                        and not (类型名称='保全付'and 保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
                         and 统计时间 between left(# prompt('p_day_end3','string')#,4)||'-01-01' and # prompt('p_day_end3','string')#
                        group by 分单号 having sum(年化保费)>0)
group by t.分公司,t.法人产品计划

union all

select 
t.分公司,
t.法人产品计划,
0 as 当日新契约件数,
0 as 当日犹豫期退保件数,
0  as 当月新契约件数,
0 as 当月犹豫期退保件数,
0 as 累计新契约件数,
count(distinct t.分单号) as 累计犹豫期退保件数
from DM_PAYEVENT_FD_LIST t
where t.法人产品计划='团体安康'  AND t.年化保费<>0
and t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人')
and t.统计时间 between left(# prompt('p_day_end3','string')#,4)||'-01-01' and # prompt('p_day_end3','string')#
and t.统计时间<=current_date-1 DAYS
group by t.分公司,t.法人产品计划


union all
---20200212 取团体守护保（安心版）的件数
select 
t.分公司,
t.法人产品计划,
count(distinct t.保单号 ) as 当日新契约件数,
0 as 当日犹豫期退保件数,
0 as 当月新契约件数,
0 as 当月犹豫期退保件数,
0 as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_PAYEVENT_FD_LIST t
where t.法人产品计划='守护保（安心版）'
and t.类型名称<>'续期'  AND t.年化保费<>0
and  not (t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
and t.统计时间 between # prompt('p_day_end2','string')# and # prompt('p_day_end3','string')#
and t.统计时间<=current_date-1 DAYS
group by t.分公司,法人产品计划


union all

select 
t.分公司,
t.法人产品计划,
0 as 当日新契约件数,
count(distinct t.保单号 ) as 当日犹豫期退保件数,
0 as 当月新契约件数,
0 as 当月犹豫期退保件数,
0 as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_PAYEVENT_FD_LIST t
where t.法人产品计划='守护保（安心版）'  AND t.年化保费<>0
and t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人')
and t.统计时间 between # prompt('p_day_end2','string')# and # prompt('p_day_end3','string')#
and t.统计时间<=current_date-1 DAYS
group by t.分公司,t.法人产品计划

union all

select 
t.分公司,
t.法人产品计划,
0 as 当日新契约件数,
0 as 当日犹豫期退保件数,
count(distinct t.保单号 )  as 当月新契约件数,
0 as 当月犹豫期退保件数,
0 as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_PAYEVENT_FD_LIST t
where t.法人产品计划='守护保（安心版）'  AND t.年化保费<>0
and t.类型名称<>'续期'
and not (t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
and t.统计时间 between left(# prompt('p_day_end3','string')#,7)||'-01' and # prompt('p_day_end3','string')#
and t.统计时间<=current_date-1 DAYS
and t.保单号 in (select 保单号 from DM_PAYEVENT_FD_LIST 
                        where 法人产品计划='守护保（安心版）'
                           and not (类型名称='保全付'and 保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
                          and 统计时间 between left(# prompt('p_day_end3','string')#,7)||'-01' and # prompt('p_day_end3','string')#
                        group by 保单号 having sum(年化保费)>0)
group by t.分公司,t.法人产品计划

union all

select 
t.分公司,
t.法人产品计划,
0 as 当日新契约件数,
0 as 当日犹豫期退保件数,
0 as 当月新契约件数,
count(distinct t.保单号 ) as 当月犹豫期退保件数,
0 as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_PAYEVENT_FD_LIST  t
where t.法人产品计划='守护保（安心版）'  AND t.年化保费<>0
and t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人')
and t.统计时间 between left(# prompt('p_day_end3','string')#,7)||'-01' and # prompt('p_day_end3','string')#
and t.统计时间<=current_date-1 DAYS
group by t.分公司,t.法人产品计划


union all

select 
t.分公司,
t.法人产品计划 ,
0 as 当日新契约件数,
0 as 当日犹豫期退保件数,
0  as 当月新契约件数,
0 as 当月犹豫期退保件数,
count(distinct t.保单号 ) as 累计新契约件数,
0 as 累计犹豫期退保件数
from DM_PAYEVENT_FD_LIST t
where t.法人产品计划='守护保（安心版）'
and t.类型名称<>'续期'  AND t.年化保费<>0
and not (t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
and t.统计时间 between left(# prompt('p_day_end3','string')#,4)||'-01-01' and # prompt('p_day_end3','string')#
and t.统计时间<=current_date-1 DAYS
and t.保单号 in (select 保单号 from DM_PAYEVENT_FD_LIST 
                        where 法人产品计划='守护保（安心版）'
                        and not (类型名称='保全付'and 保全名称 in ('犹豫期撤单','解除合同','减少被保险人'))
                         and 统计时间 between left(# prompt('p_day_end3','string')#,4)||'-01-01' and # prompt('p_day_end3','string')#
                        group by 保单号 having sum(年化保费)>0)
group by t.分公司,t.法人产品计划

union all

select 
t.分公司,
t.法人产品计划,
0 as 当日新契约件数,
0 as 当日犹豫期退保件数,
0  as 当月新契约件数,
0 as 当月犹豫期退保件数,
0 as 累计新契约件数,
count(distinct t.保单号) as 累计犹豫期退保件数
from DM_PAYEVENT_FD_LIST t
where t.法人产品计划='守护保（安心版）'  AND t.年化保费<>0
and t.类型名称='保全付'and t.保全名称 in ('犹豫期撤单','解除合同','减少被保险人')
and t.统计时间 between left(# prompt('p_day_end3','string')#,4)||'-01-01' and # prompt('p_day_end3','string')#
and t.统计时间<=current_date-1 DAYS
group by t.分公司,t.法人产品计划
with  ur

代码始终无法运行出查询结果，但是前端报表可通过缓存SQL查询的结果。排查出错点为，语法上WHERE 后的子查询中限制了全局字段的范围。子查询没有起到 开发预期的查询子集范围限制的作用。

总结：1.编写SQL语句要给被查表 起 别名;

           2.编程中，复制粘贴可复用代码后 要 注意检查 全文逻辑&语法。

           3.SQL运行超时数据量问题后，可考虑程序本身存在BUG的情况;

           4SQL代码逻辑上的问题，解决思路可以参考：由内而外，由外到内；

           5.此处可归为错误SQL，业务上划为逻辑优化，实际为底层代码编写失误，导致的程序运行内陷。（不知道可不可以归为内陷。但不属于内存溢出的问题范畴，暂时这么记录，后面有更合适的表述及时补充）。

以上。

于2020.04.30 五一劳动节前，JYKL.