Jan 26, 2021

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

Imagine you have a table like

And you want to calculate, per day, the sum(amount) of previous and following days.

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

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

SCHEMA > Evolution
Get 10 links weekly to the Data and AI articles the Tinybird team is reading.

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.

Do you like this post?

Related posts

ClickHouse tips #5: Adding and subtracting intervals
ClickHouse tips #9: Filling gaps in time-series on ClickHouse
Tiny Updates: Drag and Drop to Rearrange Nodes in a Pipe
ClickHouse tips #7: Forward and backfilling null values
ClickHouse Tips #12: Apply Functions to Columns with a Single Call

Tinybird

Team

Sep 29, 2021
ClickHouse tips #3: the transform function
Changelog: BigQuery connector, CLI improvements, Pagination on endpoints and more

Tinybird

Team

Mar 04, 2021
A big performance boost, adding columns and more

Tinybird

Team

May 31, 2021
Changelog #18: High-frequency ingestion, handling NDJSON files and more product enhancements

Tinybird

Team

Feb 01, 2022
Roll up data with Materialized Views

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.
Tinybird is not affiliated with, associated with, or sponsored by ClickHouse, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc.