2013年（350）
分类： Mysql/postgreSQL
2013-04-25 10:35:15
一、将列值为0的列替换为距离它最近列的非0值
记录集如下：
ADDDATE ADDVALUE
-------------- ---------------
2007-03-01 0
2007-03-02 0
2007-03-05 3.64
2007-03-06 3.82
2007-03-07 0
2007-03-08 3.47
2007-03-09 0
2007-03-12 0
2007-03-13 4.01
2007-03-14 4.21
2007-03-15 4.12
2007-03-16 0
2007-03-17 0
用实现如下效果：
ADDDATE ADDVALUE
-------------- ---------------
2007-03-01 3.64
2007-03-02 3.64
2007-03-05 3.64
2007-03-06 3.82
2007-03-07 3.47
2007-03-08 3.47
2007-03-09 4.01
2007-03-12 4.01
2007-03-13 4.01
2007-03-14 4.21
2007-03-15 4.12
2007-03-16 0
2007-03-17 0
建表语句如下：
create table tmp4 (adddate varchar2(20),addvalue number);
insert into tmp4 values ('2007-03-01',0);
insert into tmp4 values ('2007-03-02',0);
insert into tmp4 values ('2007-03-05',3.64);
insert into tmp4 values ('2007-03-06',3.82);
insert into tmp4 values ('2007-03-07',0);
insert into tmp4 values ('2007-03-08',3.47);
insert into tmp4 values ('2007-03-09',0);
insert into tmp4 values ('2007-03-12',0);
insert into tmp4 values ('2007-03-13',4.01);
insert into tmp4 values ('2007-03-14',4.21);
insert into tmp4 values ('2007-03-15',4.12);
insert into tmp4 values ('2007-03-16',0);
insert into tmp4 values ('2007-03-17',0);
Commit;
解题思路：
别想歪了，这道题用lead,lag之类分析函数是不行地，费事又不讨好，最简单的方式，如果不考虑执行效率的话，可以这样：
JSSWEB> select a.adddate,
2 decode(a.addvalue,0, nvl((select b.addvalue
3 from tmp4 b
4 where b.adddate > a.adddate
5 and b.addvalue != 0
6 and rownum = 1),0),a.addvalue) addvalue
7 from tmp4 a
8 ;
ADDDATE ADDVALUE
-------------------- ----------
2007-03-01 3.64
2007-03-02 3.64
2007-03-05 3.64
2007-03-06 3.82
2007-03-07 3.47
2007-03-08 3.47
2007-03-09 4.01
2007-03-12 4.01
2007-03-13 4.01
2007-03-14 4.21
2007-03-15 4.12
2007-03-16 0
2007-03-17 0
正如前文所说，这种方式效率实在堪忧，尤其是当tmp4记录量较大时，毕竟count(0)+1次tmp4表的扫描所花代价较大。
我们知道，上述形式的语句通常都是可以转换成连接查询的，因此，稍做转换：
JSSWEB> select ad1, decode(cw, 1, av1, 2, av2, 3, av1) adv
2 from (select c.*, row_number() over(partition by ad1 order by ad2) rn
3 from (select a.adddate ad1,
4 a.addvalue av1,
5 b.adddate ad2,
6 b.addvalue av2,
7 case
8 when a.addvalue != 0 then
9 1
10 when b.adddate > a.adddate and a.addvalue = 0 then
11 2
12 when b.adddate is null and a.addvalue = 0 then
13 3
14 else
15 0
16 end as cw
17 from tmp4 a, tmp4 b
18 where b.addvalue(+) != 0
19 and b.adddate(+)>a.adddate
20 order by a.adddate) c
21 where cw != 0)
22 where rn = 1
23 ;
AD1 ADV
-------------------- ----------
2007-03-01 3.64
2007-03-02 3.64
2007-03-05 3.64
2007-03-06 3.82
2007-03-07 3.47
2007-03-08 3.47
2007-03-09 4.01
2007-03-12 4.01
2007-03-13 4.01
2007-03-14 4.21
2007-03-15 4.12
2007-03-16 0
2007-03-17 0
稍加一点难度，如果希望的结果集是这样，又该怎么样实现呢：
ADDDATE ADDVALUE
-------------- ---------------
2007-03-01 3.64
2007-03-02 3.64
2007-03-05 3.64
2007-03-06 3.82
2007-03-07 3.47
2007-03-08 3.47
2007-03-09 4.01
2007-03-12 4.01
2007-03-13 4.01
2007-03-14 4.21
2007-03-15 4.12
2007-03-16 4.12
2007-03-17 4.12
并不困难，只要对我们的sql稍加改动即可........
========================
Space单篇字数限制，继续查看：
