I am attempting to gap fill in two scenarios. I can do it with one group but am uncertain with multiple
the data:
Order ID Amount
1 NULL NULL
2 A 500
3 NULL NULL
4 A 700
1 B 1000
2 NULL NULL
3 NULL NULL
4 B 1500
Target Result
Order ID Amount
1 A 500
2 A 500
3 A 700
4 A 700
1 B 1000
2 B 1500
3 B 1500
4 B 1500
CodePudding user response:
Consider below approach
select * except(amount),
first_value(amount ignore nulls) over win as amount
from (select distinct `order` from your_table where not `order` is null),
(select distinct id from your_table where not id is null)
left join your_table using(`order`, id)
window win as (partition by id order by `order` rows between current row and unbounded following)
if applied to sample data in your question - output is