3 ms·
Using Aggregate Combinators in ClickHouse
- zX41ZdbW 4y agoIt'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 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 4y agoWe are also using Clickhouse for Usermaven analytics.
- zX41ZdbW 4y agoAlso, 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.