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’d 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’ll 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 #10: Null behavior with LowCardinality columns
ClickHouse tips #9: Filling gaps in time-series on ClickHouse
ClickHouse Tips #12: Apply Functions to Columns with a Single Call
Tinybird
Team
Sep 29, 2021
Clickhouse Tips #1: Calculating Aggregations After a Given Date
ClickHouse tips #3: the transform function
A big performance boost, adding columns and more
Tinybird
Team
May 31, 2021
ClickHouse tips #6: Filtering data in subqueries to avoid joins
Changelog: BigQuery connector, CLI improvements, Pagination on endpoints and more
Tinybird
Team
Mar 04, 2021
ClickHouse tips #11: Best way to get query types
Tinybird
Team
Sep 22, 2021
Improved Support for Replacing or Appending Data

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.