Oracle Sql, Fill Missing Value With The Closest Non-missing

I have a dataset in which I want to fill missing values witht the closest non-missing value. I found two elegant solutions in the answers to this question, but I don't understand w

Solution 1:

Your first version should work, with a slight tweak:

select A.*, 
       coalesce(V1, lag(V1 ignore nulls)  over (orderby data)) V2
from Tab1 A;

The tweak is to remove the partition by v1 from the lag(). The coalesce() is just my preference for simpler expressions.

The same tweak should work for the second version as well.

Your version doesn't work because the lag() value must come from the same partition (or be null). When you have partition by v1, you are actually ensuring that v1 has the same value as in the current row.

Solution 2:

Hi or you can try manual create the ignore null solution by refer to my below. thanks https://stackoverflow.com/a/57016373/10562099

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.