----补充同期的三种算法
--总评得分同期
create or replace view bi_pm_nw_sw_dqdfqk_fyn_zpdf_tq as
select
to_char(nd) nd,
to_char(jd) jd,
sq,
ddmc,
zpdf,
first_value(zpdf) over(partition by ddmc order by to_date(nd || '-' || jd, 'yyyy-mm') range between NUMTOYMINTERVAL(1, 'year') PRECEDING and NUMTOYMINTERVAL(1, 'year') PRECEDING) zpdf_tq
from PM_NW_SW_DQDFQK where sq<>'云南'
union all
select --这里只针对所有区域单位的最大季度补充
to_char(nd+1) nd,
to_char(jd) jd,
sq,
ddmc,
null zpdf,
zpdf zpdf_tq
from PM_NW_SW_DQDFQK
where sq<>'云南' and nd = to_char(sysdate - NUMTOYMINTERVAL(1, 'year'), 'yyyy')
and jd>(select max(jd) from PM_NW_SW_DQDFQK where sq<>'云南' and nd=to_char(sysdate, 'yyyy'));
--总评分同期第二种算法 可以补充所有的同期数据 但是数据多时速度会慢 推荐用法
create or replace view bi_pm_nw_sw_dqdfqk_fyn_zpdf_tq as
select to_char(nd) nd,
to_char(jd) jd,
sq,
ddmc,
zpdf,
first_value(zpdf) over(partition by ddmc order by to_date(nd || '-' || jd, 'yyyy-mm') range between NUMTOYMINTERVAL(1, 'year') PRECEDING and NUMTOYMINTERVAL(1, 'year') PRECEDING) zpdf_tq
from PM_NW_SW_DQDFQK
where sq <> '云南'
union all
select *
from (select to_char(nd + 1) nd,
to_char(jd) jd,
sq,
ddmc,
null zpdf,
zpdf zpdf_tq
from PM_NW_SW_DQDFQK
where sq <> '云南')
where (nd, jd, sq, ddmc) not in
(select nd, jd, sq, ddmc from PM_NW_SW_DQDFQK where sq <> '云南')
and to_number(nd) <= to_number(to_char(sysdate, 'yyyy'));
--总评分同期第三种算法 可以补充所有的同期数据 但是数据多时速度会慢很多
create or replace view bi_pm_nw_sw_dqdfqk_fyn_zpdf_tq as
select nd, jd, sq, ddmc, zpdf, zpdf_tq
from (select decode(a.nd, null, b.nd, a.nd) nd,
decode(a.jd, null, b.jd, a.jd) jd,
decode(a.sq, null, b.sq, a.sq) sq,
decode(a.ddmc, null, b.ddmc, a.ddmc) ddmc,
a.zpdf,
b.zpdf_tq
from (select --当前数据
to_char(nd) nd, to_char(jd) jd, sq, ddmc, zpdf
from PM_NW_SW_DQDFQK
where sq <> '云南') a
full outer join (select --上年数据
to_char(nd + 1) nd,
to_char(jd) jd,
sq,
ddmc,
zpdf zpdf_tq
from PM_NW_SW_DQDFQK
where sq <> '云南') b
on a.nd = b.nd
and a.jd = b.jd
and a.sq = b.sq
and a.ddmc = b.ddmc
order by nd, jd)
where to_number(nd) <= to_number(to_char(sysdate, 'yyyy'));