ClickHouse глазами аналитика: uniq, квантили, массивы, воронки и retention

Бран · продюсер «Хроник Пограничья»

Аналитик соседней студии хвастается: у них воронка за сутки и retention считаются одной функцией в ClickHouse, а мы пишем экраны CTE. Мне нужно одно число: сколько новичков доходят до первой покупки за первые сутки? И научись читать их запросы, события скоро переедут и к нам.

В уроке про диалекты мы собрали словарик перевода: countIf против FILTER, uniqExact против count(DISTINCT), argMax против DISTINCT ON, toStartOfMonth и дробное деление. В прошлом уроке договорились, что у каждой метрики одна формула. Сегодня вторая страница словаря: функции ClickHouse, которыми аналитик пользуется каждый день. На каждую смотрим, как тот же вопрос решается в PostgreSQL и где числа могут не совпасть.

Работаем на событиях guild.events «Пограничья». В ClickHouse их хранили бы так же, только свойства из props разложили бы по колонкам: app_version, duration_sec, массив offers_shown. Как ClickHouse устроен внутри, разобрано в разделе про ClickHouse, здесь угол аналитика.

Нож и яйцерезка

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

PostgreSQL это хороший нож. Воронку, retention и квантили ты в нём уже собирал из FILTER, EXISTS, окон и CTE. В ClickHouse на самые частые вопросы аналитики есть яйцерезки: отдельная функция на воронку, на удержание, на последнее значение. Ими быстрее, но у каждой свои правила, и их надо знать, чтобы сверять числа.

Комбинаторы: -If и -Distinct

countIf из прошлого словаря это частный случай. Суффикс -If приклеивается к любому агрегату, и условие становится последним аргументом. Это комбинатор:

SELECT platform,
       uniqExact(player_id)                            AS players,
       uniqExactIf(player_id, name = 'store_open')     AS store_visitors,
       avgIf(duration_sec, name = 'tutorial_complete') AS avg_tutorial_sec
FROM events
WHERE player_id NOT IN (SELECT id FROM players WHERE email LIKE '%@studio.dev')
GROUP BY platform;

ClickHouse	PostgreSQL
uniqExactIf(player_id, c)	count(DISTINCT player_id) FILTER (WHERE c)
avgIf(x, c)	avg(x) FILTER (WHERE c)
sumDistinct(x) или sum(DISTINCT x)	sum(DISTINCT x)
count(DISTINCT x)	count(DISTINCT x)

Суффикс -Distinct делает то же, что DISTINCT внутри агрегата: повторы считаются один раз. А count(DISTINCT x) ClickHouse понимает и сам, по умолчанию выполняя его как uniqExact. На Android магазин открыли 820 игроков из 2019, обучение там в среднем идёт 908 секунд. Комбинаторы глубже, вместе с -State и -Merge, разобраны в уроке раздела ClickHouse.

uniq: почему число немного другое

Представь, что считаешь людей на площади не по паспортам, а по фотографии сверху. Быстро, но в толпе пару голов посчитаешь дважды, пару не заметишь. uniqExact проверяет паспорта: помнит каждое значение. uniq смотрит на фотографию: хранит хеши значений, а когда их становится больше 65 536, оставляет только часть и оценивает по ней.

На миллионе разных чисел uniq(number) по numbers(1000000) возвращает 1 001 943 вместо 1 000 000: ошибка 0,19%. На наших 4092 игроках uniq и uniqExact совпадают: столько хешей функция помнит целиком.

В PostgreSQL встроенного приближённого подсчёта нет, только точный count(DISTINCT). Поэтому правило сверки: сравниваешь отчёт ClickHouse с PostgreSQL, бери uniqExact. Для дашборда, где неважно, 12 345 игроков или 12 368, uniq быстрее и легче. И запиши в карточку метрики из прошлого урока, какой из двух функций она считается.

Квантили: три медианы одного ряда

Сколько длится обучение? Медиана в PostgreSQL это percentile_cont(0.5) с интерполяцией или percentile_disc(0.5), значение из самого ряда, как в уроке про описательную статистику. В ClickHouse своё семейство:

SELECT median(duration_sec)        AS p50,
       quantile(0.9)(duration_sec) AS p90,
       quantileExact(duration_sec) AS p50_exact
FROM events
WHERE name = 'tutorial_complete';

Первые скобки у quantile(0.9) это параметр, вторые колонка. median и quantile приближённые: держат случайную выборку до 8192 значений и интерполируют внутри неё, ответ на больших данных может чуть меняться от запуска к запуску. quantileExact точный, но не интерполирует: берёт из отсортированного ряда элемент с номером floor(уровень * n), считая от нуля. На ряде 0, 1, …, 9 он вернёт 5, а median 4,5.

Обучение на iOS прошли 964 игрока, число чётное, и середина ряда падает между двумя значениями:

функция	медиана, секунд
percentile_disc(0.5)	969
percentile_cont(0.5) и median в ClickHouse	970,5
quantileExact(0.5)	972

Три правильные медианы одного ряда. Разница в три секунды тут не важна, но при точной сверке важна: выбирай пару функций, которые считают одинаково.