Oracle sql优化之分析函数优化标量子查询

待优化语句如下

select a.code as code, a.m_code as m_code,a.stktype as f_stype,a.e_year as e_year,
          b.sname as sname,a.c_date as c_date,to_char(sysdate,YYYYMMDD) as createtime,
          to_char(sysdate,YYYYMMDD) as updatetime,
         (select sum(valuef2) from a t where t.code=a.code and t.c_date between to_char(to_date(a.c_date,YYYYMMDD)-180,YYYYMMDD) and a.c_date and t.e_year=a.e_year) e70115_70011,
          (select sum(valuef1) from a t where t.code=a.code and t.c_date between to_char(to_date(a.c_date,YYYYMMDD)-180,YYYYMMDD) and a.c_date and t.e_year=a.e_year) e70104_70011,
          (select sum(valuef6) from a t where t.code=a.code and t.c_date between to_char(to_date(a.c_date,YYYYMMDD)-180,YYYYMMDD) and a.c_date and t.e_year=a.e_year) e70126_70011,
         (select sum(valuef2) from a t where t.code=a.code and t.c_date between to_char(to_date(a.c_date,YYYYMMDD)-180,YYYYMMDD) and a.c_date and t.e_year=a.e_year) e70131_70011,
        - as f_unit
from a,b@link b
where a.code = b.code
and b.stype=2 and b.status=1 and c_date>to_char(sysdate-3,YYYYMMDD)

首先分析下标量子查询中的过滤条件:

t.c_date between to_char(to_date(a.c_date,YYYYMMDD)-180,YYYYMMDD) and a.c_date

该语句的目标实现c_date 180天内的数据汇总,因此可以分析函数表示为

order by to_date(c_date,‘YYYYMMDD‘) range between 180 preceding current row

标量子查询的语句可改写为

sum(valuef2) over(partition by a.code,a.year order by to_date(a.c_date,YYYYMMDD) range between 180 preceding current row)

而我们只需要三天内的数据,所以加上case判断

case 
  when a.c_date>to_char(sysdate-3,YYYYMMDD)
 then 
  sum(valuef2) over(partition by a.code,a.year order by to_date(a.c_date,YYYYMMDD) range between 180   preceding current row)
end

最终整体语句可改写为

select A.*,b.sname as sname,to_char(sysdate,YYYYMMDD) as createtime,
to_char(sysdate,YYYYMMDD) as updatetime from
(select a.code as code,a.m_code as m_code, a.stktype as f_stype,a.e_year as e_year, a.c_date as c_date,
case 
  when a.c_date>to_char(sysdate-3,YYYYMMDD)
 then 
  sum(valuef2) over(partition by a.code,a.year order by to_date(a.c_date,YYYYMMDD) range between 180   preceding current row)
end as f70115_70011,
case 
  when a.c_date>to_char(sysdate-3,YYYYMMDD)
 then 
  sum(valuef1) over(partition by a.code,a.year order by to_date(a.c_date,YYYYMMDD) range between 180   preceding current row)
end as f70104_70011,
case 
  when a.c_date>to_char(sysdate-3,YYYYMMDD)
 then 
  sum(valuef6) over(partition by a.code,a.year order by to_date(a.c_date,YYYYMMDD) range between 180   preceding current row)
end as f70126_70011,
case 
  when a.c_date>to_char(sysdate-3,YYYYMMDD)
 then 
  sum(valuef5) over(partition by a.code,a.year order by to_date(a.c_date,YYYYMMDD) range between 180   preceding current row)
end as f70131_70011,
- as f_unit
from a where a.c_date>= to_char(sysdate-3-180,YYYYMMDD) ---缩小数据区间
) A inner join b@link B on(A.code=B.code) 
where B.stype=2 and B.status=1 and A.c_date>=to_char(sysdate-3,YYYYMMDD)

随着数据量的增加该优化的效率越明显

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。