Комбинаторы агрегатных функций

ClickHouse предоставляет 9 комбинаторов — суффиксов, которые модифицируют поведение любой агрегатной функции. Вместо CASE WHEN ... END внутри SUM() — sumIf(). Вместо отдельного SELECT DISTINCT перед агрегацией — sumDistinct(). Вместо NULL-проверок на пустых наборах — avgOrNull().

Комбинаторы — это не отдельные функции. Это суффиксы, которые можно добавить к любой агрегатной функции: sum + -If = sumIf, count + -Array = countArray, avg + -OrNull = avgOrNull. Некоторые комбинаторы можно цеплять: sumIfState, countArrayIf.

Обзор 9 комбинаторов

-If: условная агрегация

Самый используемый комбинатор. Добавляет аргумент-фильтр типа UInt8 (0/1) к любой агрегатной функции.

-- Вместо:
SELECT SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_total
FROM orders;

-- Используем:
SELECT sumIf(amount, status = 'paid') AS paid_total
FROM orders;

Главное преимущество — несколько метрик за один проход:

-- Dashboard: 4 метрики за один скан таблицы
SELECT
    countIf(status = 'paid') AS paid_orders,
    countIf(status = 'refunded') AS refunded_orders,
    sumIf(amount, status = 'paid') AS revenue,
    avgIf(amount, status = 'paid' AND region = 'EU') AS avg_eu_revenue
FROM orders
WHERE event_date >= today() - 30;

Без -If потребовалось бы 4 отдельных запроса или громоздкие CASE WHEN.

-Array: агрегация массивов

Принимает столбец типа Array(T) вместо T. Агрегирует все элементы всех массивов.

-- Таблица с массивами цен
-- prices = [100, 200, 300] в строке 1
-- prices = [150, 250] в строке 2

SELECT sumArray(prices) AS total
FROM products;
-- Результат: 100 + 200 + 300 + 150 + 250 = 1000

SELECT avgArray(prices) AS avg_price
FROM products;
-- Среднее по всем элементам всех массивов

-State и -Merge: промежуточные состояния

Пара комбинаторов для инкрементальной агрегации. -State сохраняет промежуточное состояние, -Merge финализирует его.

-- -State: сохранение промежуточного состояния
SELECT
    region,
    sumState(amount) AS amount_state,  -- бинарный blob
    uniqState(user_id) AS users_state  -- HyperLogLog state
FROM orders
GROUP BY region;

-- -Merge: финализация состояния
SELECT
    region,
    sumMerge(amount_state) AS total_amount,
    uniqMerge(users_state) AS unique_users
FROM orders_aggregated
GROUP BY region;

-Distinct: дедупликация перед агрегацией

-- Количество уникальных статусов (аналог COUNT(DISTINCT status))
SELECT countDistinct(status) FROM orders;

-- Сумма только уникальных значений
SELECT sumDistinct(amount) FROM orders;

-OrNull и -OrDefault: поведение на пустых наборах

-- Обычный avg на пустом наборе:
SELECT avg(amount) FROM orders WHERE 1 = 0;
-- Результат: NaN (для Float) или 0 (для Int)

-- -OrNull: явный NULL при отсутствии данных
SELECT avgOrNull(amount) FROM orders WHERE 1 = 0;
-- Результат: NULL

-- -OrDefault: значение по умолчанию типа
SELECT avgOrDefault(amount) FROM orders WHERE 1 = 0;
-- Результат: 0.0 (default для Float64)

-OrNull полезен в визуализациях: NULL можно показать как “нет данных”, в отличие от 0 или NaN.

-Map: агрегация по ключам Map

-- Таблица с метриками в Map
-- metrics = {'clicks': 10, 'views': 100} в строке 1
-- metrics = {'clicks': 5, 'views': 50, 'carts': 3} в строке 2

SELECT sumMap(metrics) AS totals
FROM events;
-- Результат: {'clicks': 15, 'views': 150, 'carts': 3}

Каждый ключ агрегируется отдельно. Ключи, присутствующие не во всех строках, агрегируются только по имеющимся значениям.

-Resample: бакетизация

Разбивает данные на интервалы по значению ключа и вычисляет агрегат в каждом интервале. Возвращает Array результатов.

-- Количество заказов по возрастным группам [18,30), [30,42), [42,54), [54,66)
SELECT countResample(18, 66, 12)(order_id, age) AS orders_by_age
FROM orders;
-- Результат: [1520, 3200, 2800, 1100] -- 4 бакета по 12 лет

Параметры: start, end, step. Количество бакетов = (end - start) / step.

Цепочки комбинаторов

Комбинаторы можно цеплять. Порядок имеет значение:

-- -IfState: сначала фильтр (-If), затем сохранение состояния (-State)
SELECT sumIfState(amount, status = 'paid') FROM orders;
-- Тип результата: AggregateFunction(sumIf, UInt64, UInt8)

-- -StateIf: НЕ СУЩЕСТВУЕТ -- нельзя ставить -State перед -If

Допустимые цепочки:

-- -ArrayIf: агрегация элементов массива с условием
SELECT sumArrayIf(prices, active = 1) FROM products;

-- -DistinctIf: уникальные значения с фильтром
SELECT countDistinctIf(user_id, region = 'EU') FROM events;

-- -IfState: фильтр + промежуточное состояние (для MV)
SELECT avgIfState(latency, status_code = 200) FROM requests;

Практический пример: multi-metric dashboard

Один запрос — все метрики за один скан:

SELECT
    toStartOfDay(event_time) AS day,
    -- Общие метрики
    count() AS total_events,
    uniq(user_id) AS unique_users,

    -- Условные метрики через -If
    countIf(event_type = 'purchase') AS purchases,
    sumIf(amount, event_type = 'purchase') AS revenue,
    avgIf(amount, event_type = 'purchase') AS avg_order,

    -- Сегментация через -If
    countIf(platform = 'mobile') AS mobile_events,
    countIf(platform = 'desktop') AS desktop_events,

    -- Безопасные метрики через -OrNull
    avgOrNull(response_time) AS avg_response,

    -- Массивы через -Array
    sumArray(tag_scores) AS total_tag_scores
FROM events
WHERE event_time >= today() - 7
GROUP BY day
ORDER BY day;

Этот запрос вычисляет 10 метрик за один проход по таблице. Без комбинаторов потребовалось бы 10 отдельных запросов или сложная конструкция с CASE WHEN.

Ключевые выводы

1. 9 комбинаторов модифицируют поведение любой агрегатной функции через суффиксы.
2. -If — самый частый: условная агрегация без CASE WHEN, несколько метрик за один скан.
3. -State/-Merge — фундамент инкрементальной агрегации в MV + AggregatingMergeTree.
4. -OrNull/-OrDefault — контроль поведения на пустых наборах (NULL vs default значение типа).
5. -Map — агрегация по динамическим ключам Map без GROUP BY по каждому ключу.
6. Цепочки возможны, но порядок важен: -IfState (фильтр, затем состояние), не -StateIf.