Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Clickhouse Tips #1: Calculating Aggregations After a Given Date

Tips and recipes to learn how to make the most of ClickHouse, curated weekly by the Tinybird team.
Javier Santana
Co-founder
Jan 26, 2021
 ・ 
  min read

Imagine you have a table like

And you want to calculate, per day, the {% code-line %}sum(amount){% code-line-end %} of previous and following days.

For example, for the day {% code-line %}2020-01-05{% code-line-end %} you have to calculate {% code-line %}sumIf(amount, ts < '2020-01-05'){% code-line-end %} and {% code-line %}sumIf(amount, ts >= '2020-01-05'){% code-line-end %}

I think there are many ways to do it but this works:

Check out this snapshot for a step-by-step explanation of what’s going on here.

I think a way to exploit that values for each day don’t need to be calculated every time for each day using a nice function, arrayCumSum, plus some other array magic.

Check this out for a step-by-step explanation.

I feel there should be an easier way but that’s just a feeling.

Become a better data developer

Subscribe to the tinytales newsletter for monthly tips on building better data products.