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

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

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")

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