CODEWITHSUNDEEP
sqloracle-database
user1723699
user1723699

Reputation: 1091

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

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?

Upvotes: 15

Views: 32263

Answers (4)

zheng wee
zheng wee

Reputation: 131

Just In Case you are using other database(eg. sqlite) and it didn't have a ignore null function. i post another solution here for your reference.

  1. You need use a 0/1 column to indicate the Null/Non-Null data
  2. Then create accumulate summary column to calculate the indicator number in step 1. -now you can see the data already looks like grouped by your non_Null data.
  3. As last step, pls use a Max function group by the accumulate sum (in step 2) to populate the data(here is amont) in to empty items.

attach the pic to help you understand. enter image description here

Upvotes: 0

Pavel
Pavel

Reputation: 51

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

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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

Upvotes: 11

Multisync
Multisync

Reputation: 8797

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

Upvotes: 17

Related Questions