17,086
社区成员
发帖
与我相关
我的任务
分享
with t as(
select '201010' as smonth, 4 as val from dual
union all
select '201102' as smonth, 7 as val from dual
union all
select '201108' as smonth, 10 as val from dual
)
select mon 年月,
val 数值,
Decode(val,0,0,(val-lag_val)/val)*100 环比增长
from (
select a.mon,nvl(t.val,0) val,Lag(Nvl(t.val,0),1,Nvl(t.val,0))over(order by a.mon) lag_val from(
select to_char(add_months(m1,level-1),'yyyymm') mon from (
select min(to_date(smonth,'yyyymm')) m1,max(to_date(smonth,'yyyymm')) m2 from t
)
connect by level<=months_between(m2,m1)+1
) a
left join t on a.mon=t.smonth(+)
order by 1
)
年月 数值 环比增长
201010 4 0
201011 0 0
201012 0 0
201101 0 0
201102 7 100
201103 0 0
201104 0 0
201105 0 0
201106 0 0
201107 0 0
201108 10 100
with t as
(
select '201010' as smonth, 4 as val from dual
union all
select '201102' as smonth, 7 as val from dual
union all
select '201108' as smonth, 10 as val from dual
)
select a.smonth, 0 as val
from (select to_char(add_months(to_date(smin,'yyyymm'), rownum),'yyyymm') as smonth
from (select min(smonth) as smin,
max(smonth) as smax
from t) t1
connect by rownum < months_between(to_date(t1.smax, 'yyyymm'), to_date(t1.smin, 'yyyymm'))) a
where smonth not in(select smonth from t)
order by smonth;