單條SQL語句實現複雜邏輯的幾個例子(4)

一、將列值為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

用SQL實現如下效果：

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. 1 2

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單篇字數限制，繼續檢視：

單條SQL語句實現複雜邏輯的幾個例子(4)續~

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7607759/viewspace-243689/，如需轉載，請註明出處，否則將追究法律責任。

單條SQL語句實現複雜邏輯的幾個例子(4)

相關文章