TopolearnThe built-in functions turn multi-step SQL into one fast, readable query.
Standard SQL works, but ClickHouse adds a lot that analytics queries lean on.
- Conditional aggregates:
countIf(status = 'error'),sumIf(amount, country = 'DE'),avgIf. - Approximate and exact distinct counts:
uniq(user_id)is fast and approximate (error typically well under 1%);uniqExactis exact but uses more memory.count(DISTINCT x)maps touniqExactby default. - Time bucketing:
toStartOfHour(ts),toStartOfDay,toStartOfInterval(ts, INTERVAL 15 minute),toYYYYMM(ts),toDayOfWeek. argMax(price, ts): the price at the latest ts, without a join or window.LIMIT n BY col: top n rows per group, e.g. the 3 slowest pages per day.WITH TOTALSadds a grand-total row to aGROUP BY.count()needs no argument, and you can reuse an alias anywhere in the sameSELECT, even inWHERE.
Functions are case-sensitive in their camelCase form (toStartOfHour), although common SQL ones like COUNT and SUM also accept any case. Also note that integer division / returns a Float64; use intDiv(a, b) for integer division.
Example
SELECT
toStartOfDay(ts) AS day,
count() AS hits,
countIf(ms > 500) AS slow,
round(slow / hits * 100, 1) AS slow_pct, -- aliases are reusable
uniq(url) AS pages
FROM page_views
WHERE ts >= now() - INTERVAL 7 DAY
GROUP BY day WITH TOTALS -- extra grand-total row
ORDER BY day;
-- 2 slowest pages per day (top-N per group without a window function)
SELECT toDate(ts) AS day, url, max(ms) AS worst
FROM page_views
GROUP BY day, url
ORDER BY day, worst DESC
LIMIT 2 BY day;Remember
- countIf / sumIf replace CASE inside aggregates
- uniq is fast approximate distinct; uniqExact is exact
- toStartOf* functions bucket time; LIMIT n BY gives top-N per group
Watch out for
- Expecting 7 / 2 to be 3: ClickHouse returns 3.5; use intDiv for integer division.