Функции ClickHouse: countIf, COUNT(DISTINCT), sumIf и argMax

В ClickHouse это три разные задачи: посчитать строки по условию, посчитать уникальные значения без условия и посчитать уникальные значения по условию. Для первой задачи используется countIf, для второй — COUNT(DISTINCT ...), а для точного условного подсчёта — uniqExactIf

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

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);

Если такой таблицы ещё нет, сначала разберите, как создать исходную таблицу MergeTree

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;

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

Если нужно устранить повторы значений, countIf не становится эквивалентом DISTINCT: используйте следующий раздел

Как посчитать уникальные значения по условию

Способ проверен в официальном ClickHouse Playground на версии 26.7.1.459. Документация подтверждает синтаксис COUNT(DISTINCT ...) и правило комбинатора -If: условие передаётся последним аргументом агрегатной функции

В обоих запросах используется один набор из пяти строк: пользователи 1, 2 и 3; у пользователя 2 событие purchase повторяется

Контрольный запрос без условия:

SELECT
    COUNT(DISTINCT user_id) AS all_users
FROM values(
    'user_id UInt64, event_type String',
    (1, 'view'),
    (1, 'purchase'),
    (2, 'purchase'),
    (2, 'purchase'),
    (3, 'view')
);

Фактический вывод:

┌─all_users─┐
│         3 │
└───────────┘

Результат 3 включает пользователей 1, 2 и 3; повторные события не создают новых уникальных значений

Условный точный подсчёт:

SELECT
    uniqExactIf(user_id, event_type = 'purchase') AS exact_buyers
FROM values(
    'user_id UInt64, event_type String',
    (1, 'view'),
    (1, 'purchase'),
    (2, 'purchase'),
    (2, 'purchase'),
    (3, 'view')
);

Фактический вывод:

┌─exact_buyers─┐
│            2 │
└──────────────┘

Результат 2 включает только пользователей 1 и 2, у которых есть purchase; повторная строка пользователя 2 не увеличивает значение. Тот же запрос можно запустить в ClickHouse Client

uniqExactIf нужен, когда результат должен быть точным. uniqIf решает ту же условную задачу приблизительно и может потреблять меньше памяти; выбирать его стоит только когда допустима погрешность

На другой версии или в иной конфигурации повторите короткий контрольный запрос перед использованием в отчёте

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: значение при максимуме другого поля

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;

argMax(arg, val) возвращает arg для строки, где val максимален. Если у нескольких строк одинаковый максимум 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

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

• Смешивать количество строк и уникальных значений. Симптом: повторные события завышают метрику. Причина: вместо uniqExactIf используется countIf. Исправление: сверить задачу с таблицей выбора выше.
• Копирование countIf(DISTINCT ...) из обсуждения. Симптом: запрос не выполняется или его поведение нельзя подтвердить документацией. Причина: обсуждение GitHub принято за справочник. Исправление: использовать проверенный uniqExactIf(value, condition).
• Игнорирование count_distinct_implementation. Симптом: объяснение COUNT(DISTINCT ...) не совпадает с конфигурацией сервера. Причина: не учтена настройка реализации. Исправление: проверить настройку и не переносить выводы о реализации между системами.

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

Сила 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 лучше нескольких отдельных запросов?

Когда отчёт строится по одной таблице и различаются только условия: просмотры, корзины, покупки, ошибки, успешные ответы. Вместо нескольких проходов по данным вы собираете метрики сразу

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

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

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

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