HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Analytical functions and interpolation - samples
ORACLE STUFFS
23-Feb-2013
There are various situations you want to fill missing values based on last filled or neighbor filled values. Following simple example show the approach:


Prepare testing data:

Create table ttta
as select level as SEQ, case when mod(level,6)=0 then POWER(level,2) end
ATTR1 from dual connect by level<=65;

[Download]

SELECT * FROM ttta; SEQ ATTR1 ---------------------- ---------------------- 1 2 3 4 5 6 36 7 8 9 10 11 12 144 13 14 15 16 17 18 324


Then we create query, completing missing values based on last previous value:

SELECT 
   seq    AS X
  ,attr1  as Y
  ,last_value(ATTR1 IGNORE NULLS)OVER(ORDER BY SEQ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Y_LOW
FROM ttta;

[Download]

X Y Y_LOW ---------------------- ---------------------- ---------------------- 1 2 3 4 5 6 36 36 7 36 8 36 9 36 10 36 11 36 12 144 144 13 144 14 144 15 144 16 144 17 144 18 324 324


Another case shows linear interpolation using analytical functions:

With L1 as(
Select 
   seq    as X
  ,attr1  as Y
  ,last_value(ATTR1 ignore nulls)over(order by SEQ range between unbounded preceding and current row) as Y_LOW
  ,max(case when attr1 is not null then seq end ) over(order by SEQ) X_GAP_LOW
  ,min(case when attr1 is not null then seq end ) over(order by SEQ desc) X_GAP_HIGH
  ,last_value(ATTR1 ignore nulls)over(order by SEQ desc range between unbounded preceding and current row) as Y_HIGH from ttta
)
Select 
   X
  ,Y
  ,NVL(Y,Y_LOW+((Y_HIGH-Y_LOW)*(X-X_GAP_LOW)/NULLIF(X_GAP_HIGH-X_GAP_LOW,0)))  Y_EP
from L1
order by 1
;    

[Download]

X Y Y_EP ---------------------- ---------------------- ---------------------- 1 2 3 4 5 6 36 36 7 54 8 72 9 90 10 108 11 126 12 144 144 13 174 14 204 15 234 16 264 17 294 18 324 324

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky