Функции ClickHouse: countIf, sumIf, argMax, dateDiff, if и multiIf

В ClickHouse много функций, но в первых аналитических запросах чаще всего нужны условные агрегации, работа с датами, выбор последнего значения, условия и строки. В этом уроке разберем countIf, sumIf, argMax, dateDiff, if, multiIf, coalesce, like и concat на одной таблице событий

Исходная таблица

CREATE TABLE demo.events
(
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    event_type LowCardinality(String),
    amount Decimal(12, 2),
    url String
)
ENGINE = MergeTree
ORDER BY (event_date, event_type, user_id);

countIf: посчитать только нужные строки

countIf считает строки, для которых условие истинно. Это удобнее, чем несколько отдельных запросов

SELECT
    event_date,
    countIf(event_type = 'view') AS views,
    countIf(event_type = 'cart') AS carts,
    countIf(event_type = 'purchase') AS purchases
FROM demo.events
GROUP BY event_date
ORDER BY event_date;

Такой запрос сразу дает дневную мини-воронку

sumIf: сумма по условию

SELECT
    event_date,
    sumIf(amount, event_type = 'purchase') AS revenue,
    sumIf(amount, event_type = 'refund') AS refunds
FROM demo.events
GROUP BY event_date
ORDER BY event_date;

sumIf принимает значение и условие. Если условие выполнено, значение попадает в сумму. Если нет — строка игнорируется

argMax: значение при максимуме другого поля

argMax(arg, val) возвращает arg для строки, где val максимален. Частый сценарий — найти последнее событие пользователя

SELECT
    user_id,
    argMax(event_type, event_time) AS last_event,
    max(event_time) AS last_seen_at
FROM demo.events
GROUP BY user_id;

Если у нескольких строк одинаковый максимум event_time, результат может быть не тем, который вы ожидали. Для строгой логики добавляйте дополнительное поле версии или используйте кортеж в аргументах

dateDiff: разница между датами

SELECT
    user_id,
    dateDiff('minute', min(event_time), max(event_time)) AS session_minutes
FROM demo.events
GROUP BY user_id;

Первый аргумент задает единицу измерения: second, minute, hour, day, month и другие. Следите за часовыми поясами, если данные приходят из разных систем

if и multiIf

if подходит для простого выбора из двух вариантов:

SELECT
    user_id,
    if(amount > 0, 'paid', 'free') AS payment_flag
FROM demo.events;

Если условий несколько, используйте multiIf:

SELECT
    user_id,
    multiIf(
        amount >= 10000, 'vip',
        amount > 0, 'buyer',
        event_type = 'cart', 'warm',
        'visitor'
    ) AS segment
FROM demo.events;

У всех веток должен получиться совместимый тип. Если в одной ветке строка, а в другой число, ClickHouse будет приводить типы или вернет ошибку

coalesce и NULL

coalesce возвращает первое не-NULL значение. Он полезен, когда часть полей может быть пустой

SELECT
    user_id,
    coalesce(nullIf(url, ''), 'unknown') AS normalized_url
FROM demo.events;

Не путайте пустую строку и NULL. Если колонка не Nullable, обычная пустая строка не станет NULL сама по себе

LIKE, ILIKE и concat

SELECT *
FROM demo.events
WHERE url LIKE '%utm_source%';

SELECT
    concat(toString(user_id), ':', event_type) AS event_key
FROM demo.events;

LIKE удобен для простых проверок, но не заменяет продуманную модель данных. Если вы постоянно фильтруете по источнику, лучше распарсить источник в отдельную колонку, а не искать его внутри URL

Типичные ошибки

• Смешивать несовместимые типы в multiIf.
• Ожидать от argMax детерминированный выбор при равных максимумах.
• Использовать LIKE '%...%' как основной способ аналитики по структурированным данным.
• Забывать, что NULL и пустая строка — разные значения.
• Писать несколько запросов вместо одного с countIf/sumIf.

Как собирать функции в один отчет

Сила ClickHouse раскрывается, когда несколько метрик считаются одним проходом по данным. Вместо отдельных запросов для просмотров, корзин, покупок и выручки соберите их в один запрос с условными агрегатами

SELECT
    event_date,
    count() AS all_events,
    countIf(event_type = 'view') AS views,
    countIf(event_type = 'cart') AS carts,
    countIf(event_type = 'purchase') AS purchases,
    sumIf(amount, event_type = 'purchase') AS revenue,
    round(purchases / nullIf(views, 0), 4) AS purchase_rate
FROM demo.events
GROUP BY event_date
ORDER BY event_date;

nullIf(views, 0) защищает от деления на ноль. Такие мелочи делают отчет устойчивее: он не падает на днях без просмотров и сразу показывает понятные значения

Как проверять функцию перед использованием

• Сначала выполните функцию на 3-5 строках с ручной проверкой результата.
• Проверьте поведение на NULL, пустых строках и нулевых значениях.
• Проверьте тип результата через toTypeName().
• Если функция идет в production-отчет, добавьте комментарий с бизнес-смыслом метрики.

SELECT
    toTypeName(sumIf(amount, event_type = 'purchase')) AS revenue_type,
    toTypeName(dateDiff('day', min(event_time), max(event_time))) AS diff_type
FROM demo.events;

Что еще удобно считать в одном запросе

Когда countIf лучше нескольких отдельных запросов?

Когда отчет строится по одной таблице и различаются только условия: просмотры, корзины, покупки, ошибки, успешные ответы. Это естественное продолжение урока про INSERT INTO и первые SELECT-запросы: вместо нескольких проходов по данным вы собираете метрики сразу

Где функции начинают зависеть от схемы?

В типах и NULL-значениях. sumIf по строковой сумме, dateDiff по плохо распарсенной дате и multiIf с разными типами веток быстро превращаются в шум. Если такие ошибки повторяются, вернитесь к материалу Типы данных ClickHouse

Когда результат функции стоит вынести в витрину?

Если один и тот же расчет нужен постоянно и поверх большого потока данных. Тогда имеет смысл посмотреть Materialized View в ClickHouse: часть агрегатов можно считать при вставке, а не каждый раз в дашборде