I have a table :
For a given ID, with rows ordered by Timestamp, I want to fill null values with the next available non null value in following rows. The output should be in this manner.
I tried using coalesce(min( value) over (partition by id order by timestamp rows between 0 following and 10 following) , null) as new_value but that is not giving the right answer and I see why. Please help me with the logic.
Solved! Go to Solution.
You can also work in the descending order in this way :
last_value(Value ignore nulls) over (partition by id order by Timestamp desc)
With the data :
create multiset volatile table mvt_data, no log ( Id integer not null , Ts byteint not null , Val char(1) null ) primary index (Id) on commit preserve rows; insert into mvt_data values (1, 3, null); insert into mvt_data values (1, 4, null); insert into mvt_data values (1, 5, 'B' ); insert into mvt_data values (1, 6, 'A' ); insert into mvt_data values (1, 7, null); insert into mvt_data values (1, 8, 'C' ); insert into mvt_data values (1, 9, 'A' ); collect statistics column (Id) on mvt_data;
In this case, the first_value / last_value solution will perform better, but here the nPath solution - for everyone to practice :
select Id as Id , Ts as Ts , Val as Val , NewVal as NewVal from nPath( on mvt_data partition by Id order by Ts asc using mode ( overlapping ) symbols ( Val is null as V1 , Val is not null as V2 ) pattern ( 'V1*.V2?' ) result ( first(Id of any(V1,V2)) as Id , first(Ts of any(V1,V2)) as Ts , first(Val of any(V1,V2)) as Val , first(Val of V2 ) as NewVal ) ) as np order by 1, 2;
the ? in V1*.V2? pattern is to handle case where last val is a null.