4 ms·
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: SE
by zX41ZdbW 4y 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 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.