May 18, 2021

ClickHouse tips #7: Forward and backfilling null values

Making use of array functions to do it.
Alejandra Rodriguez

Many times you have data with null values and you鈥檇 like to fill them with non-null values. For example, imagine this is your data:

You could replace them by a constant value like this:

But in many cases you鈥檒l want to fill them with the latest (or next) value available. This is a one-liner in libraries like Pandas with the fillna method, and on Postgres this way. On ClickHouse is also possible using array functions:

Here is a full explanation on what happens in each of the subqueries of this last query and of what each of the array functions does.

Do you like this post?

Related posts

ClickHouse tips #9: Filling gaps in time-series on ClickHouse
ClickHouse tips #10: Null behavior with LowCardinality columns
ClickHouse Tips #12: Apply Functions to Columns with a Single Call
Sep 29, 2021
ClickHouse tips #8: Generating time-series on ClickHouse
Improving the data ingestion experience: better error feedback
ClickHouse tips #3: the transform function
ClickHouse Tips #4: Materializing UNION ALL queries
Coming soon on ClickHouse: Window functions
ClickHouse tips #6: Filtering data in subqueries to avoid joins
ClickHouse tips #5: Adding and subtracting intervals

Build fast data products, faster.

Try Tinybird and bring your data sources together and enable engineers to build with data in minutes. No credit card required, free to get started.
Need more? Contact sales for Enterprise support.