oracle 补充同期

----补充同期的三种算法
--总评得分同期
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'));