The LEAD and LAG functions are enhanced with the IGNORE NULL option in 11gR2.
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select * from T
2 ORDER BY deptno,id
3 /
ID SAL COMM DEPTNO
---------- ---------- ---------- ----------
1 50 .1 10
4 97 10
7 39 .7 10
10 2 1 10
2 28 .2 20
5 18 20
8 40 .8 20
3 80 .1 30
6 16 30
9 70 .2 30
10 rows selected.
Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> SELECT id,
2 sal ,
3 deptno ,
4 comm ,
5 CASE
6 WHEN comm IS NULL
7 THEN lead(comm) over(partition BY deptno order by id)
8 ELSE comm
9 END AS lead_comm
10 FROM t
11 /
ID SAL DEPTNO COMM LEAD_COMM
---------- ---------- ---------- ---------- ----------
1 50 10 .1 .1
4 97 10 .7
7 39 10 .7 .7
10 2 10 1 1
2 28 20 .2 .2
5 18 20 .8
8 40 20 .8 .8
3 80 30 .1 .1
6 16 30 .2
9 70 30 .2 .2
10 rows selected.
Elapsed: 00:00:00.01
rajesh@10GR2> SELECT id,
2 sal ,
3 deptno ,
4 comm ,
5 lead(lead_comm) over(partition BY deptno order by id) AS lead_comm
6 FROM
7 (
8 SELECT id,
9 sal ,
10 deptno ,
11 comm ,
12 CASE
13 WHEN comm IS NULL
14 THEN lead(comm) over(partition BY deptno order by id)
15 ELSE comm
16 END AS lead_comm
17 FROM t
18 )
19 ORDER BY deptno,id
20 /
ID SAL DEPTNO COMM LEAD_COMM
---------- ---------- ---------- ---------- ----------
1 50 10 .1 .7
4 97 10 .7
7 39 10 .7 1
10 2 10 1
2 28 20 .2 .8
5 18 20 .8
8 40 20 .8
3 80 30 .1 .2
6 16 30 .2
9 70 30 .2
10 rows selected.
Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2>
With Enhancements 11gR2 this is very simple to achieve.
rajesh@11GR2> select id,sal,deptno,comm,lead(comm ignore nulls) over(partition by deptno order by id) as lead_comm
2 from t
3 order by deptno,id
4 /
ID SAL DEPTNO COMM LEAD_COMM
---------- ---------- ---------- ---------- ----------
1 50 10 .1 .7
4 97 10 .7
7 39 10 .7 1
10 2 10 1
2 28 20 .2 .8
5 18 20 .8
8 40 20 .8
3 80 30 .1 .2
6 16 30 .2
9 70 30 .2
10 rows selected.
Elapsed: 00:00:00.03
No comments:
Post a Comment