|
下面是我的mapper文件，查询超级慢，请问有什么办法优化吗
with temp as
(select FUN_GET_DEPT_NAME(a.nurse_cell_code) as nurse_cell_name,
month_code,
sum(a.out_num) as out_num, /*出院人数*/
sum(a.bednum) as sjkfzcrs /*实际开放总床日数*/,
round((sum(a.bednum) / 30), 1) as pjkfcws /*平均开发床位数*/,
sum(a.end_num) as sjzyzcws /*实际占用总床日数*/ /*'期末人数'*/
from nurse_day_report a
where a.month_code between '201501' and '201511'
group by a.nurse_cell_code, month_code
order by a.nurse_cell_code),
data as (
select 'thisM' as time ,
nvl(max((select count(1) as aa
from fin_opr_register t1
WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
and t1.valid_flag = '1'
and t1.reglevl_name ]]> ' '
and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次：期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计：0 治愈1 好转 2 未愈3 死亡 4 其他'*/
nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
from temp
where month_code='201511'
union all
select 'lastM' ,
nvl(max((select count(1) as aa
from fin_opr_register t1
WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
and t1.valid_flag = '1'
and t1.reglevl_name ]]> ' '
and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次：期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计：0 治愈1 好转 2 未愈3 死亡 4 其他'*/
nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
from temp
where month_code='201511'
union all
select 'thisY' ,
nvl(max((select count(1) as aa
from fin_opr_register t1
WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
and t1.valid_flag = '1'
and t1.reglevl_name ]]> ' '
and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次：期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计：0 治愈1 好转 2 未愈3 死亡 4 其他'*/
nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
from temp
where month_code between '201601' and '201511'
union all
select 'lastY' ,
nvl(max((select count(1) as aa
from fin_opr_register t1
WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
and t1.valid_flag = '1'
and t1.reglevl_name ]]> ' '
and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次：期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计：0 治愈1 好转 2 未愈3 死亡 4 其他'*/
nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
from temp
where month_code between '201501' and '201511'
)
select
sum(case when TIME = 'thisM' then regist else 0 end) as "thisM",
sum(case when TIME = 'lastM' then regist else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then regist else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then regist else 0 end)-sum(case when TIME = 'lastM' then regist else 0 end))*100/sum(case when TIME = 'lastM' then regist else 0 end),3))as lvM,
sum(case when TIME = 'thisY' then regist else 0 end) as "thisY",
sum(case when TIME = 'lastY' then regist else 0 end) as "lastY" ,
decode(sum(case when TIME = 'lastY' then regist else 0 end) ,0,0,round((sum(case when TIME = 'thisY' then regist else 0 end) -sum(case when TIME = 'lastY' then regist else 0 end) )*100/sum(case when TIME = 'lastY' then regist else 0 end) ,1))as lvY
from data
union all
select
sum(case when TIME = 'thisM' then outer else 0 end) as "thisM",
sum(case when TIME = 'lastM' then outer else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then outer else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then outer else 0 end)-sum(case when TIME = 'lastM' then outer else 0 end))*100/sum(case when TIME = 'lastM' then outer else 0 end),1)) as LvM,
sum(case when TIME = 'thisY' then outer else 0 end) as "thisY",
sum(case when TIME = 'lastY' then outer else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then outer else 0 end),0,0,round((sum(case when TIME = 'thisY' then outer else 0 end)-sum(case when TIME = 'lastY' then outer else 0 end))*100/sum(case when TIME = 'lastY' then outer else 0 end),1) ) as LvY
from data
union all
select
sum(case when TIME = 'thisM' then sjzyzcrs else 0 end) as "thisM",
sum(case when TIME = 'lastM' then sjzyzcrs else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then sjzyzcrs else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then sjzyzcrs else 0 end)-sum(case when TIME = 'lastM' then sjzyzcrs else 0 end))*100/sum(case when TIME = 'lastM' then sjzyzcrs else 0 end),1)) as LvM,
sum(case when TIME = 'thisY' then sjzyzcrs else 0 end) as "thisY",
sum(case when TIME = 'lastY' then sjzyzcrs else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then sjzyzcrs else 0 end) ,0,0,round((sum(case when TIME = 'thisY' then sjzyzcrs else 0 end)-sum(case when TIME = 'lastY' then sjzyzcrs else 0 end) )*100/sum(case when TIME = 'lastY' then sjzyzcrs else 0 end) ,1)) as LvY
from data
union all
select
sum(case when TIME = 'thisM' then zzcs else 0 end) as "thisM",
sum(case when TIME = 'lastM' then zzcs else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then zzcs else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then zzcs else 0 end)-sum(case when TIME = 'lastM' then zzcs else 0 end))*100/sum(case when TIME = 'lastM' then zzcs else 0 end),1)) as LvM,
sum(case when TIME = 'thisY' then zzcs else 0 end) as "thisY",
sum(case when TIME = 'lastY' then zzcs else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then zzcs else 0 end),0,0,round((sum(case when TIME = 'thisY' then zzcs else 0 end)-sum(case when TIME = 'lastY' then zzcs else 0 end))*100/sum(case when TIME = 'lastY' then zzcs else 0 end) ,1)) as LvY
from data
union all
select
sum(case when TIME = 'thisM' then cwsyl else 0 end) as "thisM",
sum(case when TIME = 'lastM' then cwsyl else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then cwsyl else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then cwsyl else 0 end)-sum(case when TIME = 'lastM' then cwsyl else 0 end))*100/sum(case when TIME = 'lastM' then cwsyl else 0 end),1)) as LvM,
sum(case when TIME = 'thisY' then cwsyl else 0 end) as "thisY",
sum(case when TIME = 'lastY' then cwsyl else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then cwsyl else 0 end),0,0,round((sum(case when TIME = 'thisY' then cwsyl else 0 end)-sum(case when TIME = 'lastY' then cwsyl else 0 end))*100/sum(case when TIME = 'lastY' then cwsyl else 0 end) ,1)) as LvY
from data
下面是我的mapper文件，查询超级慢，请问有什么办法优化吗
with temp as
(select FUN_GET_DEPT_NAME(a.nurse_cell_code) as nurse_cell_name,
month_code,
sum(a.out_num) as out_num, /*出院人数*/
sum(a.bednum) as sjkfzcrs /*实际开放总床日数*/,
round((sum(a.bednum) / 30), 1) as pjkfcws /*平均开发床位数*/,
sum(a.end_num) as sjzyzcws /*实际占用总床日数*/ /*'期末人数'*/
from nurse_day_report a
where a.month_code between '201501' and '201511'
group by a.nurse_cell_code, month_code
order by a.nurse_cell_code),
data as (
select 'thisM' as time ,
nvl(max((select count(1) as aa
from fin_opr_register t1
WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
and t1.valid_flag = '1'
and t1.reglevl_name ]]> ' '
and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次：期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计：0 治愈1 好转 2 未愈3 死亡 4 其他'*/
nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
from temp
where month_code='201511'
union all
select 'lastM' ,
nvl(max((select count(1) as aa
from fin_opr_register t1
WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
and t1.valid_flag = '1'
and t1.reglevl_name ]]> ' '
and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次：期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计：0 治愈1 好转 2 未愈3 死亡 4 其他'*/
nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
from temp
where month_code='201511'
union all
select 'thisY' ,
nvl(max((select count(1) as aa
from fin_opr_register t1
WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
and t1.valid_flag = '1'
and t1.reglevl_name ]]> ' '
and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次：期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计：0 治愈1 好转 2 未愈3 死亡 4 其他'*/
nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
from temp
where month_code between '201601' and '201511'
union all
select 'lastY' ,
nvl(max((select count(1) as aa
from fin_opr_register t1
WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
and t1.valid_flag = '1'
and t1.reglevl_name ]]> ' '
and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次：期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计：0 治愈1 好转 2 未愈3 死亡 4 其他'*/
nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
from temp
where month_code between '201501' and '201511'
)
select
sum(case when TIME = 'thisM' then regist else 0 end) as "thisM",
sum(case when TIME = 'lastM' then regist else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then regist else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then regist else 0 end)-sum(case when TIME = 'lastM' then regist else 0 end))*100/sum(case when TIME = 'lastM' then regist else 0 end),3))as lvM,
sum(case when TIME = 'thisY' then regist else 0 end) as "thisY",
sum(case when TIME = 'lastY' then regist else 0 end) as "lastY" ,
decode(sum(case when TIME = 'lastY' then regist else 0 end) ,0,0,round((sum(case when TIME = 'thisY' then regist else 0 end) -sum(case when TIME = 'lastY' then regist else 0 end) )*100/sum(case when TIME = 'lastY' then regist else 0 end) ,1))as lvY
from data
union all
select
sum(case when TIME = 'thisM' then outer else 0 end) as "thisM",
sum(case when TIME = 'lastM' then outer else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then outer else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then outer else 0 end)-sum(case when TIME = 'lastM' then outer else 0 end))*100/sum(case when TIME = 'lastM' then outer else 0 end),1)) as LvM,
sum(case when TIME = 'thisY' then outer else 0 end) as "thisY",
sum(case when TIME = 'lastY' then outer else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then outer else 0 end),0,0,round((sum(case when TIME = 'thisY' then outer else 0 end)-sum(case when TIME = 'lastY' then outer else 0 end))*100/sum(case when TIME = 'lastY' then outer else 0 end),1) ) as LvY
from data
union all
select
sum(case when TIME = 'thisM' then sjzyzcrs else 0 end) as "thisM",
sum(case when TIME = 'lastM' then sjzyzcrs else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then sjzyzcrs else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then sjzyzcrs else 0 end)-sum(case when TIME = 'lastM' then sjzyzcrs else 0 end))*100/sum(case when TIME = 'lastM' then sjzyzcrs else 0 end),1)) as LvM,
sum(case when TIME = 'thisY' then sjzyzcrs else 0 end) as "thisY",
sum(case when TIME = 'lastY' then sjzyzcrs else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then sjzyzcrs else 0 end) ,0,0,round((sum(case when TIME = 'thisY' then sjzyzcrs else 0 end)-sum(case when TIME = 'lastY' then sjzyzcrs else 0 end) )*100/sum(case when TIME = 'lastY' then sjzyzcrs else 0 end) ,1)) as LvY
from data
union all
select
sum(case when TIME = 'thisM' then zzcs else 0 end) as "thisM",
sum(case when TIME = 'lastM' then zzcs else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then zzcs else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then zzcs else 0 end)-sum(case when TIME = 'lastM' then zzcs else 0 end))*100/sum(case when TIME = 'lastM' then zzcs else 0 end),1)) as LvM,
sum(case when TIME = 'thisY' then zzcs else 0 end) as "thisY",
sum(case when TIME = 'lastY' then zzcs else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then zzcs else 0 end),0,0,round((sum(case when TIME = 'thisY' then zzcs else 0 end)-sum(case when TIME = 'lastY' then zzcs else 0 end))*100/sum(case when TIME = 'lastY' then zzcs else 0 end) ,1)) as LvY
from data
union all
select
sum(case when TIME = 'thisM' then cwsyl else 0 end) as "thisM",
sum(case when TIME = 'lastM' then cwsyl else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then cwsyl else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then cwsyl else 0 end)-sum(case when TIME = 'lastM' then cwsyl else 0 end))*100/sum(case when TIME = 'lastM' then cwsyl else 0 end),1)) as LvM,
sum(case when TIME = 'thisY' then cwsyl else 0 end) as "thisY",
sum(case when TIME = 'lastY' then cwsyl else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then cwsyl else 0 end),0,0,round((sum(case when TIME = 'thisY' then cwsyl else 0 end)-sum(case when TIME = 'lastY' then cwsyl else 0 end))*100/sum(case when TIME = 'lastY' then cwsyl else 0 end) ,1)) as LvY
from data
|