Using Aggregate Combinators in ClickHouse

https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states

by taubek • 3 years ago

11 3 3 years ago

zX41ZdbW2• 3 years ago

It's worth noting that ClickHouse supports both the standard SQL and the extensions.

For example, the standard SQL way to write filtered aggregates is:

    SELECT avg(number) FILTER (WHERE number > 50) FROM numbers(100)

See https://modern-sql.com/feature/filter

But it is only supported by SQLite, PostgreSQL, and ClickHouse. While ClickHouse also provides a shorthand:

    SELECT avgIf(number, number > 50) FROM numbers(100)

The same for DISTINCT:

    SELECT avg(DISTINCT number)
    SELECT avgDistinct(number)

Why filtered aggregates are important? - They allow to implement the "segment comparison" feature in web analytics services.

    WITH Region = 'us' AS segment1, Browser = 'Chrome' AS segment2
    SELECT uniqIf(UserID, segment1), uniqIf(UserID, segment2)
    WHERE segment1 OR segment2

Most of the existing web analytics services are using ClickHouse: CloudFlare Analytics, Microsoft Clarity, Plausible Analytics, PostHog, Splitbee...

gmsiperx• 3 years ago

We are also using Clickhouse for Usermaven analytics.

zX41ZdbW2• 3 years ago

Also, ClickHouse is the only DBMS implementing aggregate function states as data types to allow them to be stored in tables, saved, loaded, and merged.

It allows incremental, continuous aggregate tables.

Other DBMSes like TimescaleDB implement a similar feature in a less generic way.

Using Aggregate Combinators in ClickHouse

Related Stories