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?

Multisync · Accepted Answer · 2014-11-17 17:06:38Z

17

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

edited Nov 17, 2014 at 17:06

answered Nov 17, 2014 at 16:45

Multisync

8,8271 gold badge20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1723699 Over a year ago

Thanks, this worked perfectly, the last part about between unbounded preceding and 1 preceding is something that I don't completely understand, would you mind explaining what that does?

Multisync Over a year ago

@user1723699 I've updated my answer. You can read Oracle documentation about WINDOWING in analytic functions.

user1723699 Over a year ago

Thanks, I am briefly familiar with windowing but still learning as I am completely self taught and don't have a strong coding background. This worked like a charm though so I appreciate it very much.

Paul Over a year ago

I had a similar requirement, but the opposite: FIRST_VALUE(amount) IGNORE NULLS OVER(PARTITION BY item ORDER BY year ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING). This solution helped me get there.

Probel Over a year ago

You are a superstar. This works almost identically in Netezza.

Gordon Linoff · Accepted Answer · 2016-04-29 01:37:19Z

11

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

edited Apr 29, 2016 at 1:37

answered Nov 17, 2014 at 16:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Maheswaran Ravisankar Over a year ago

+1 The time I took to read it.. There's an answer.. You Keep surprising me ! :)

Pavel · Accepted Answer · 2018-04-21 17:10:32Z

5

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

answered Apr 21, 2018 at 17:10

Pavel

511 silver badge1 bronze badge

Comments

zheng wee · Accepted Answer · 2019-07-13 05:15:47Z

0

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.

You need use a 0/1 column to indicate the Null/Non-Null data
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.
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.

answered Jul 13, 2019 at 5:15

zheng wee

1411 gold badge1 silver badge8 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related