A common task in data processing is to fill null values in a table with the latest existing value. For example, look at the following inventory table
Let’s assume we only get an inventory record, if the inventory changes but we want to fill the missing values (for example to create a snapshot fact table).
The final result should look like this with null values being filled with the last existing value:
In Power Query (or “Get and Transform” if you’re using Excel 2016) this is an easy task using the fill-down function, which does exactly what want:
So, how can we get the same effect in T-SQL? We could use a sub query, but in this post I’m using a window function. The idea is to generate row groups for which each row group starts with a not-null value and has a unique identifier. This allows us to use the first_value function for each group to get the existing value for that group.
In order to build the row groups I start with a change indicator for the table values. The idendicator is 1 if we have a balance, 0 if not. The resulting query is quite simple:
-
select *, case when UnitsBalance is null then 0 else 1 end ChangeIndicator from [dbo].[Inventory]
The next step is to create a running total over the change indicator. In fact, this running total already defines our row groups. I’m using the sum() over window function here. The query from above is converted into a common table expression (cte) as this is easier to read:
-
with
-
help1 as (
-
select *, case when UnitsBalance is null then 0 else 1 end ChangeIndicator
-
from [dbo].[Inventory]
-
)
-
-
select *, Sum(ChangeIndicator) over (order by DateKey) RowGroup from help1
-
order by Datekey
As you see, the number for the row group increments with each existing balance value which means that the first row always contains the number (with the exception of the very first line for which we don’t have a value at all). In order to finalize the query, we just need the first_value function to get the value of the first row per row group:
-
with
-
help1 as (
-
select *, case when UnitsBalance is null then 0 else 1 end ChangeIndicator
-
from [dbo].[Inventory]
-
)
-
, help2 as (
-
select *, Sum(ChangeIndicator) over (order by DateKey) RowGroup from help1
-
)
-
select *,
-
-
-
case when UnitsBalance is not null then UnitsBalance
-
else first_value(UnitsBalance) over (partition by RowGroup order by DateKey)
-
end UnitsBalanceFillDown
-
from help2
-
order by datekey
So this is exactly what we wanted (if you remove the columns that we just needed during the process from the final output, you get exactly the result from above).
Conclusion
Windows functions in T-SQL are a good solution to fill gaps in your data set similar to last-non-empty.
As I’m using the first_value function here, just as a reminder to avoid frustration: first_value order by date descending and last_value order by date ascending do not give the same result.
Kommentare (2)