Home » SQL & PL/SQL » SQL & PL/SQL » to find mid value (pl/sql)
| to find mid value [message #608919] |
Wed, 26 February 2014 21:37  |
simplesanju
Messages: 36
Registered: July 2008
|
Member |
|
|
Hi All,
My requirement is little tricky. can you please help me to write query?
Id col1
1 12
2 16
3 null
4 20
5 45
6 null
7 25
9 25
10 12
requirement is to fill value in place of null.logic is:
null=previous record value+next record value/2
out put :
Id col1
1 12
2 16
3 18 ---16+20/2
4 20
5 45
6 35 ---45+25/2
7 25
9 25
10 12
|
|
|
|
|
|
|
|
| Re: to find mid value [message #608956 is a reply to message #608949] |
Thu, 27 February 2014 08:26  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Interesting 2 ways to see the problem. 
SQL> select id,
2 nvl(col1,
3 ( lag(col1,1,0) over (order by id)
4 + lead(col1,1,0) over (order by id)
5 ) / 2
6 ) col1
7 from t
8 /
ID COL1
---------- ----------
1 12
2 16
3 18
4 20
5 45
6 35
7 25
9 25
10 12
9 rows selected.
SQL> select id,
2 nvl(col1,
3 avg(col1) over
4 (order by id
5 rows between 1 preceding and 1 following)
6 ) col1
7 from t
8 /
ID COL1
---------- ----------
1 12
2 16
3 18
4 20
5 45
6 35
7 25
9 25
10 12
9 rows selected.
|
|
|
|
|
|
| Re: to find mid value [message #608967 is a reply to message #608960] |
Thu, 27 February 2014 09:42 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Yes, and there is the question of what should be the result if the first(s) or last(s) value is(are) null.
In the first query I gave I took 0 for the missing value (before first, after last), so the result is the half of the other one. In the second query, I assumed nothing and so the NULL is converted to the closed value.
Open question, number of consecutive nulls is unknown and unlimited, missing values should be linear interpolation between the previous and next known values (id may not be consecutive as in OP's example), here's a test case to play:
drop table t;
create table t (id int primary key, col1 number);
insert into t values(1, 12);
insert into t values(2, 16);
insert into t values(3, null);
insert into t values(4, 20);
insert into t values(5, 45);
insert into t values(6, null);
insert into t values(7, null);
insert into t values(9, 24);
insert into t values(10, 12);
insert into t values(11, null);
insert into t values(12, null);
insert into t values(13, null);
insert into t values(14, null);
insert into t values(15, 22);
insert into t values(16, 12);
insert into t values(20, 120);
insert into t values(21, null);
insert into t values(22, null);
insert into t values(23, null);
insert into t values(24, null);
insert into t values(25, null);
commit;
|
|
|
|
| Re: to find mid value [message #609060 is a reply to message #608967] |
Fri, 28 February 2014 11:05 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Here I chose to virtually add a value 0 before the first row and after the last row (in case of first or last value is missing):
SQL> with
2 data as (
3 select id, col1,
4 row_number() over (order by id) rn,
5 nvl2(col1, row_number() over (order by id), null) flag,
6 count(*) over () cnt
7 from t
8 )
9 select id, col1 old_col1,
10 nvl(col1,
11 nvl(last_value(col1 ignore nulls) over
12 (order by id rows between unbounded preceding and 1 preceding),
13 0) +
14 ( nvl(first_value(col1 ignore nulls) over
15 (order by id rows between 1 following and unbounded following),
16 0)
17 - nvl(last_value(col1 ignore nulls) over
18 (order by id rows between unbounded preceding and 1 preceding),
19 0)
20 ) *
21 ( rn
22 - nvl(last_value(flag ignore nulls) over
23 (order by id rows between unbounded preceding and 1 preceding),
24 0)
25 ) /
26 (nvl(first_value(flag ignore nulls) over
27 (order by id rows between 1 following and unbounded following),
28 cnt+1)
29 - nvl(last_value(flag ignore nulls) over
30 (order by id rows between unbounded preceding and 1 preceding),
31 0)
32 )
33 ) new_col1
34 from data
35 order by id
36 /
ID OLD_COL1 NEW_COL1
---------- ---------- ----------
1 12 12
2 16 16
3 18
4 20 20
5 45 45
6 38
7 31
9 24 24
10 12 12
11 14
12 16
13 18
14 20
15 22 22
16 12 12
20 120 120
21 100
22 80
23 60
24 40
25 20
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 16:41:26 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
