Fill null values with last non-null amount - Oracle SQL

Question

I have a table that has 4 columns: Item, Year, Month, Amount. Some of the values for Amount are null and when that happens I want to fill those values in with the previous Amount value that is not null. I can easily do this with the LAG function when there is only one null value but when there are multiple in a row I am not sure how to approach it. Below is an example of what the table might look like with an added column for what I want to add in my query:

Item | Year | Month | Amount | New_Amount
AAA  | 2013 | 01    | 100    | 100
AAA  | 2013 | 02    |        | 100
AAA  | 2013 | 03    | 150    | 150
AAA  | 2013 | 04    | 125    | 125
AAA  | 2013 | 05    |        | 125
AAA  | 2013 | 06    |        | 125
AAA  | 2013 | 07    |        | 125
AAA  | 2013 | 08    | 175    | 175

I had two ideas which I can't seem to get to work to produce what I want. First I was going to use LAG but then I noticed when there are multiple null values in a row it won't satisfy that. Next I was going to use FIRST_VALUE but that wouldn't help in this situation where there is a null followed by values followed by more nulls. Is there a way to use FIRST_VALUE or another similar function to retrieve the last non-null value?

Answer 1

last_value with IGNORE NULLS works fine in Oracle 10g:

select item, year, month, amount, 
       last_value(amount ignore nulls) 
         over(partition by item 
              order by year, month 
              rows between unbounded preceding and 1 preceding) from tab;

rows between unbounded preceding and 1 preceding sets the window for analytic function.

In this case Oracle is searching for LAST_VALUE inside the group defined in PARTITION BY (the same item) from the begining (UNBOUNDED PRECEDING) until current row - 1 (1 PRECEDING)

It's a common replacement for LEAD/LAG with IGNORE NULLS in Oracle 10g

However, if you're using Oracle 11g you can use LAG from the Gordon Linoff's answer (there is a small typo with "ignore nulls")

Answer 2

the answers are quite bad:

Item | Year | Month | Amount | New_Amount
AAA  | 2013 | 01    | 100    | null
AAA  | 2013 | 02    |        | 100
AAA  | 2013 | 03    | 150    | 100
AAA  | 2013 | 04    | 125    | 150
AAA  | 2013 | 05    |        | 125
AAA  | 2013 | 06    |        | 125
AAA  | 2013 | 07    |        | 125
AAA  | 2013 | 08    | 175    | 125

is a quite bad result :)

--

select item, year, month, amount, 
  last_value(amount ignore nulls) 
over(partition by item
  order by year, month
  rows between unbounded preceding and CURRENT ROW) from tab;

is better

Answer 3

Here is an approach. Count the number of non-null values before a given row. Then use this as a group for a window function:

select t.item, t.year, t.month, t.amount,
       max(t.amount) over (partition by t.item, grp) as new_amount
from (select t.*,
             count(Amount) over (Partition by item order by year, month) as grp
      from table t
     ) t;

In Oracle version 11+, you can use ignore nulls for lag() and lead():

select t.item, t.year, t.month, t.amount,
       lag(t.amount ignore nulls) over (partition by t.item order by year, month) as new_amount
from table t