SQL Dialect & Functions

## The idea Most SQL dialects give you a fixed catalogue of aggregate functions. ClickHouse instead gives you a set of base aggregates and a set of **combinators** — suffixes you append to the function's name to change its input or output behaviour. The combination is resolved at parse time, so `uniqArrayIf` is a real function, not a macro. This is why ClickHouse SQL looks unfamiliar: there is no separate `FILTER` ceremony or unnesting step, the behaviour is baked into the function name. ## The -If combinator `-If` appends one extra argument to the function: a condition evaluated per row. Rows where it is 0 are skipped by that aggregate only. ```sql SELECT count() AS total, countIf(status = 'error') AS errors, sumIf(bytes, status = 'ok') AS ok_bytes, uniqIf(user_id, event = 'purchase') AS buyers, avgIf(latency_ms, region = 'eu') AS eu_latency FROM requests WHERE day = today() ``` One scan, five metrics, each with its own predicate. Pushing any of those predicates into `WHERE` would apply it to all five. That is the whole point of the combinator: `WHERE` is a row filter for the query, `-If` is a row filter for a single aggregate. When every aggregate uses the same predicate, `WHERE` is strictly better — it filters earlier, prunes more, and reads less. `-If` earns its keep when the predicates differ. Note the argument order: the condition is always **last**. `countIf` is the special case where the condition is the only argument, since `count` has none. ## The -Array combinator `-Array` makes an aggregate consume an `Array` column element-wise: every element of every row's array is fed to the aggregate as if it were an ordinary input value. ```sql SELECT sumArray(item_prices) AS revenue, -- every element of every array uniqArray(tags) AS distinct_tags, maxArray(item_prices) AS priciest_item FROM baskets ``` The alternative would be `ARRAY JOIN` followed by a normal aggregate. The combinator avoids materializing the expanded rows, so it is usually both shorter and cheaper. `groupArrayArray(arr)` is the idiom for flattening arrays across rows into one array. ## Composing combinators Combinators stack, and the order is fixed by the grammar: **`-Array` first, then `-If`**. ```sql SELECT uniqArrayIf(tags, country = 'DE') AS de_tags FROM events ``` Read it right to left: `-If` selects the rows (`country = 'DE'`), `-Array` feeds the elements of those rows' arrays to `uniq`. Writing `uniqIfArray` is a parse error. Parametric aggregates keep their parameter list in front: `quantilesTimingArrayIf(0.5, 0.99)(durations, is_slow)`. ## The rest of the family The same suffix grammar carries combinators you should be able to name: - `-State` / `-Merge` — return and consume an intermediate aggregation state instead of a final value, which is what makes pre-aggregation mergeable. - `-OrNull` / `-OrDefault` — return `NULL`, or the type default, when the aggregate received no input rows at all, instead of the function's normal empty-input result. - `-Distinct` — deduplicate input values before aggregating, e.g. `sumDistinct`. - `-ForEach` — apply the aggregate element-wise across arrays, producing an array of aggregates (positional), which is different from `-Array` flattening everything into one value. - `-Map` — aggregate values grouped by map key. - `-SimpleState` — produce a state for the cheaper `SimpleAggregateFunction` type. ## Pitfalls - **`sum(if(cond, x, 0))` is not always the same as `sumIf(x, cond)`.** The `if` form contributes a zero for non-matching rows, which matters for `avg`, `min`, `count` and anything sensitive to how many values were seen. `avgIf(x, cond)` averages only matching rows; `avg(if(cond, x, 0))` drags the mean toward zero. - **Nothing is free.** `-If` still reads and evaluates every row that survives `WHERE`; it saves a second scan, not the scan. - **`countIf(x)` needs a condition, not a column.** `countIf(user_id)` counts rows where `user_id` is non-zero, which is rarely what was meant. - **`-Array` is not `-ForEach`.** `sumArray` returns one number over all elements; `sumForEach` returns an array of per-position sums. ## What interviewers probe Expect "compute paid, refunded and total revenue per customer in one query" and expect the answer to be three `sumIf` expressions in one `SELECT`, with an explanation of why that beats three subqueries or three scans. The follow-up is usually the `-Array`/`-If` ordering rule, or when a plain `WHERE` is the better tool.