A combinator is a suffix, not a function
O que você vai aprender
- break an unfamiliar name into parts: the root and suffixes
- use six suffixes:
-If,-OrNull,-Array,-Map,-ForEach,-Resample - build compound names such as
sumMapIfandquantilesExactIf— and use their suffixes to understand how the base aggregate changes - distinguish “zero” from “no data”: when no row matches, a conditional sum returns 0, while
-OrNullreturns
A name that isn’t in the reference
In the previous shift’s log, you come across the sumMapIf. You open the reading room’s function reference, but that name is not there. On nearby pages you see quantilesExactIf, argMaxIf, and uniqExactIf — none of them appear in the list either.
The reference is not outdated. These names are not supposed to exist there as separate functions.
In ClickHouse, an aggregate is assembled from a root and suffixes. Such a suffix is called a combinator: it changes the behavior of a base aggregate function when their argument types are compatible.
So the method from the previous lesson — countIf, sumIf — was just one special case. -If is one of these suffixes.
In this lesson, we’ll look at six suffixes. The next cell shows how each one changes the same aggregate — and, most importantly, how the shape of the result changes. Square brackets in the cell denote an array, or a list of values; we’ll explore arrays in detail in Chapter 3.
sum root and a sixth on count, applied to visit data. Focus first on the SHAPE of the result: a number, NULL, an array, or a pair of arrays.How to read an unfamiliar name
When you encounter an unfamiliar name, don't look it up as a whole. Start from the end: strip off suffixes from right to left until you reach a familiar root.
Each suffix you remove adds another transformation to the aggregate. This means the name tells you both how the calculation works and what shape the result takes.
| suffix | what it does | what you see in the cell |
|---|---|---|
-If | takes a condition as its last argument and aggregates only the rows that match | 5626 total for the gallery and 2753 from a tablet |
-OrNull | if there is nothing to aggregate, returns instead of 0 | a regular sumIf with no matching rows returned 0, while sumOrNullIf returned NULL |
-Array | takes an array and passes EACH of its elements to the aggregate | 13 268 for the gallery: 5626 titles plus 7642 actions combined into a single sum |
-Map | takes a pair of arrays, “keys, values,” and aggregates the values separately for each key | two parallel arrays: three devices and the sum for each one |
-ForEach | takes an array and maintains a SEPARATE aggregate for each position | [5626, 7642] — two independent totals instead of one |
-Resample | places rows into buckets based on the key and calculates the aggregate in each bucket | [694, 683, 397] — three buckets without a single GROUP BY |
-Array and -ForEach are especially easy to confuse because both work with arrays. The difference is what happens after the elements are read.
-Array sends every element from every array into a single aggregate. So the result is one number.
-ForEach maintains a separate aggregate for each array position. So the result is an array of the same length: the first total for the first position, the second for the second, and so on.
Zero and no result are not the same thing
-OrNull deserves a closer look because it changes not how the value is calculated, but what the result means.
The “empty group: zero” column and the “-OrNull” column answer the same question: what is the sum for visits where more than a hundred titles were opened? The hall groups themselves exist, but none contains a matching visit.
A regular conditional aggregate returns 0. In a report, this is easy to read as a measured fact: “there were zero titles.”
-OrNull returns NULL. That means something different: there was nothing to aggregate. In a summary read by the curator, the difference between “zero” and “no data” matters more than it may seem.
sumMapIf → -If → -Map → the root sum. Beside it is the way each suffix changes the answer’s shape: a number, NULL, a pair of arrays, or an array.-Array pours every element into one aggregate and gives 13 268, while -ForEach keeps an aggregate per position and gives [5626, 7642].Interview question
How this comes up in interviews
You are usually given a ready-made name such as avgMergeIf or maxMapState and asked to explain what it does. The interviewer is testing not your memory or your knowledge of a long list of functions, but whether you understand how to parse the name.
You need to identify the familiar root, then strip off the suffixes from right to left and explain what each one adds.
The English name for this mechanism is combinator, as in “ function combinator.”
The -State and -Merge suffixes are part of the same mechanism: the first returns an aggregate state instead of a finished value, while the second merges such states. They come up especially often in interviews because precomputed marts are built around them. This course covers them in the chapter on calculation accuracy.
maxForEach(x) in some code, where x is an array of three numbers. What will it return?Principais pontos
| question | method | price |
|---|---|---|
What does sumMapIf do? | parse the name: root sum + -Map + -If | no need to open the reference |
| Six result shapes from one table | six suffixes on functions | 1 part / 5272 rows — one pass for all six |
The main rule of this lesson: a combinator is a suffix that changes a base aggregate function.
So you do not need to memorize a list of ready-made names. Learn the transformations themselves and get used to reading names from right to left. Then you can parse an unfamiliar aggregate on the spot.
In the next lesson, we’ll examine the argMax root you have already glimpsed; the suffixes it takes remain the same.