一个日维度的表,有些天没有值,想把它用离它最近一天的值补全,譬如:
date num
2008-1-1 10
2008-1-4 30
2008-1-6 20
填充成
date num
2008-1-1 10
2008-1-2 10
2008-1-3 10
2008-1-4 30
2008-1-5 30
2008-1-6 20
不用存储过程,只通过sql select查询怎么实现
date num
2008-1-1 10
2008-1-4 30
2008-1-6 20
填充成
date num
2008-1-1 10
2008-1-2 10
2008-1-3 10
2008-1-4 30
2008-1-5 30
2008-1-6 20
不用存储过程,只通过sql select查询怎么实现
select to_date('2008-3-1') d, 10 n from dual
union all
select to_date('2008-3-4') , 30 from dual
union all
select to_date('2008-3-9') , 20 from dual
)
select t0.d, t.n
from (select d start_d ,lead(d,1,d+1) over (order by d) end_d, n from t) t,
(select r+min_d d
from (select min(d) min_d from t),
(select rownum-1 r
from all_objects
where rownum<=(select (max(trunc(d))-min(trunc(d))+1) from t)) ) t0
where t0.d>=t.start_d and t0.d<t.end_d
order by 1;
(select r+min_d d
from (select min(d) min_d from t),
(select rownum-1 r
from all_objects
where rownum <=(select (max(trunc(d))-min(trunc(d))+1) from t)) )
的目的是得到源表最大日期与最小日期之间的所有日期
select distinct TT.dt + level -1 LDT,TT.va from
(select t1.dt DT,t1.va VA,nvl(to_number(t2.dt-t1.dt),1) Len from
(select dt,va,rownum rn from (select date dt,num va from tablename order by dt)) t1,
(select dt,va,rownum -1 rn from (select date dt,num va from tablename order by dt)) t2
where t1.rn = t2.rn(+)) TT
connect by level < len + 1
order by LDT