昨天的问题应该已经解决了,见帖子 http://community.csdn.net/Expert/topic/5754/5754395.xml?temp=.6121179
但那个问题只有两列:日期和数据。真实的情况是,若要求移动平均线,表中至少有还有一个产品代码列。表及数据构造如下:create table tmp5 (code varchar2(2),adddate varchar2(20),addvalue varchar2(10),constraint pk_tmp5 primary key(code,adddate));
insert into tmp5 values ('A','2007-03-01','0');insert into tmp5 values ('A','2007-03-02','0');insert into tmp5 values ('A','2007-03-05','3.64');insert into tmp5 values ('A','2007-03-06','3.82');insert into tmp5 values ('A','2007-03-07','0');insert into tmp5 values ('A','2007-03-08','3.47');insert into tmp5 values ('A','2007-03-09','0');insert into tmp5 values ('A','2007-03-12','0');insert into tmp5 values ('A','2007-03-13','4.01');insert into tmp5 values ('A','2007-03-14','4.21');insert into tmp5 values ('A','2007-03-15','4.42');insert into tmp5 values ('A','2007-03-16','0');insert into tmp5 values ('A','2007-03-17','0');
insert into tmp5 values ('B','2007-03-01','0');insert into tmp5 values ('B','2007-03-02','2.32');insert into tmp5 values ('B','2007-03-05','3.76');insert into tmp5 values ('B','2007-03-06','3.53');insert into tmp5 values ('B','2007-03-07','0');insert into tmp5 values ('B','2007-03-08','0');insert into tmp5 values ('B','2007-03-09','0');insert into tmp5 values ('B','2007-03-12','0');insert into tmp5 values ('B','2007-03-13','2.99');insert into tmp5 values ('B','2007-03-14','3.35');insert into tmp5 values ('B','2007-03-15','4.06');insert into tmp5 values ('B','2007-03-16','0');insert into tmp5 values ('B','2007-03-17','0');
要求:对于每一个产品代码,如果数据的起始端有连续零值,应该替换为遇到的第一个(以日期顺序)非零值;如果数据的末尾端有连续零值,应该替换为最后遇到的非零值。中间部分的零值其实应该取这一段中顺序号较小的那个非零值。谢谢!
但那个问题只有两列:日期和数据。真实的情况是,若要求移动平均线,表中至少有还有一个产品代码列。表及数据构造如下:create table tmp5 (code varchar2(2),adddate varchar2(20),addvalue varchar2(10),constraint pk_tmp5 primary key(code,adddate));
insert into tmp5 values ('A','2007-03-01','0');insert into tmp5 values ('A','2007-03-02','0');insert into tmp5 values ('A','2007-03-05','3.64');insert into tmp5 values ('A','2007-03-06','3.82');insert into tmp5 values ('A','2007-03-07','0');insert into tmp5 values ('A','2007-03-08','3.47');insert into tmp5 values ('A','2007-03-09','0');insert into tmp5 values ('A','2007-03-12','0');insert into tmp5 values ('A','2007-03-13','4.01');insert into tmp5 values ('A','2007-03-14','4.21');insert into tmp5 values ('A','2007-03-15','4.42');insert into tmp5 values ('A','2007-03-16','0');insert into tmp5 values ('A','2007-03-17','0');
insert into tmp5 values ('B','2007-03-01','0');insert into tmp5 values ('B','2007-03-02','2.32');insert into tmp5 values ('B','2007-03-05','3.76');insert into tmp5 values ('B','2007-03-06','3.53');insert into tmp5 values ('B','2007-03-07','0');insert into tmp5 values ('B','2007-03-08','0');insert into tmp5 values ('B','2007-03-09','0');insert into tmp5 values ('B','2007-03-12','0');insert into tmp5 values ('B','2007-03-13','2.99');insert into tmp5 values ('B','2007-03-14','3.35');insert into tmp5 values ('B','2007-03-15','4.06');insert into tmp5 values ('B','2007-03-16','0');insert into tmp5 values ('B','2007-03-17','0');
要求:对于每一个产品代码,如果数据的起始端有连续零值,应该替换为遇到的第一个(以日期顺序)非零值;如果数据的末尾端有连续零值,应该替换为最后遇到的非零值。中间部分的零值其实应该取这一段中顺序号较小的那个非零值。谢谢!
a.adddate,
decode(a.addvalue,
0,
nvl((select c.addvalue
from (select b.code, b.adddate, b.addvalue
from tmp5 b
where b.addvalue > 0
order by b.code, b.adddate) c
where a.adddate < c.adddate
and code = a.code
and rownum = 1),
(select addvalue
from (select code, adddate, addvalue
from tmp5 d
where addvalue > 0
order by code, adddate desc) e
where code = a.code
and rownum = 1)),
a.addvalue)
from tmp5 a