Using Aggregate Combinators in ClickHouse(clickhouse.com)

Using Aggregate Combinators in ClickHouse

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

3 comments

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...

gmsiperx3년 전

We are also using Clickhouse for Usermaven analytics.

zX41ZdbW3년 전

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.