Для большинства повседневных задач аналитику хватает самых популярных функций ClickHouse. Более того, почти всегда можно решить ту или иную задачу уже имеющимися инструментами, написав более громоздкий код с множеством джоинов и подзапросами. Поэтому часто мы забываем использовать всю мощь кликхауса - а зря, ведь есть функции, которые бы сделали код более читаемым и производительным)

В этой статье покажу и разберу на примерах несколько недооцененных функций и приемов, которые помогут оптимизировать ваши запросы.

Представим, что у нас есть набор данных - события (клики/просмотры) пользователя на сайте.

create table events
(
    user_id     UInt64,
    event_time  DateTime,
    event_name  String,
    -- например, JSON с параметрами события
    properties  String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (user_id, event_time)

1. sequenceMatch и sequenceCount - путь пользователя без громоздких джойнов

Задача: найти пользователей, которые просмотрели карточку товара - нажали "Купить" - оформили заказ. То есть прошли по воронке в таком порядке (с любыми «шумовыми» событиями между шагами).

Классический путь - три подзапроса по разным событиям, джойны по user_id, фильтры по времени между событиями. На миллионах строк такой запрос быстро станет медленным и нечитаемым.

Как это делает sequenceMatch:

sequenceMatch(pattern)(timestamp, cond1, cond2, ...)

  • pattern — это шаблон последовательности (регулярное выражение для событий) :

    • например, '(?1)(?2)(?3)' = «сначала шаг 1, потом шаг 2, потом шаг 3». Это последовательность без ограничения по времени. Ограничение задается в формате (в секундах): (?1)(?t<=1800)(?2)(?t<=1800)(?3).

  • дальше передаём: колонку с временем, набор булевых колонок/условий по событиям.

Функция возвращает 0 или 1 для каждой группы.

ClickHouse сам отслеживает порядок событий по event_time — в том смысле, что внутри sequenceMatch не нужно вручную джойнить события между собой и считать интервалы.

Важно! события пользователя должны быть поданы в функцию уже в хронологическом порядке (обычно это обеспечивается ORDER BY (user_id, event_time) в таблице или явной сортировкой в подзапросе). sequenceMatch сам не сортирует строки, он просто идёт по ним в том порядке, в котором они пришли.

Пример: пользователи, прошедшие путь view_product -> click_buy -> purchase Установим между шагами - максимум 3 минуты:

select
    user_id,
    sequenceMatch('(?1)(?t<=180)(?2)(?t<=180)(?3)')(
        event_time,
        event_name = 'view_product',
        event_name = 'click_buy',
        event_name = 'purchase'
    ) as done_in_3m
from
(
    select
          user_id,
          event_time,
          event_name
    from events
    where event_time >= now() - interval 7 day
    order by user_id, event_time
)
group by user_id
having done_in_3m = 1

Это читается так:

Найди пользователя, у которого былоview_product, потом в течение 3-х минут click_buy, потом ещё в течение 3-х минут purchase.

Важные преимущества функции:

  • мы не джойним таблицу саму на себя

  • мы один раз читаем события

Если done_in_3m = 1, значит, пользователь прошёл путь полностью, в правильном порядке.

Функция sequenceCount имеет похожий принцип, но она возвращает количество неперекрывающихся цепочек. Это может быть удобно для повторных конверсий.

2. windowFunnel: воронка

sequenceMatch хорош, когда важно пройден был путь или нет. Но в продуктовой аналитике часто важнее другое:

  • до какого шага воронки дошёл пользователь

  • сколько пользователей застряло на каждом шаге

  • всё это - в фиксированном окне после стартового события

Для этого в ClickHouse есть windowFunnel(window)(timestamp, cond1, cond2, …). window - это длина окна в секундах (часто записывают как кол-во минут * 60).

Функция возвращает номер последнего выполненного шага. 

select
    user_id,
    -- окно 1 час 
    windowFunnel(60 * 60)( 
        event_time,
        event_name = 'view_product',
        event_name = 'click_buy',
        event_name = 'purchase',
        event_name = 'exit'
    ) as funnel_depth
from events
group by user_id

Если funnel_depth = 0 - пользователь только увидел продукт, 1 — дошёл до старта покупки click_buy, 4 — прошёл полный путь.

Важно! Параметр window — это интервал между первым и последним шагом, а не между соседними событиями. Это важно, чтобы не переоценивать конверсию: все шаги должны уложиться в заданное окно.

С помощью этой функции можно быстро построить классические воронки: сколько % пользователей остановилось на каждом шаге и сколько дошло до конца.

3. retention() - когортный retention одной функцией

retention() — малоизвестная агрегатная функция, которую часто даже не замечают в документации и блогах про ClickHouse. Она принимает до 32 булевых условий и возвращает "маску удержания" по периодам.

Идея такая:

  • ты задаёшь «флаги» по периодам (например, day_0, day_1, day_7, day_30)

  • в retention() передаёшь условия вида has_activity_day_0, has_activity_day_1, …

  • далее либо интерпретируешь результат функцией, либо агрегируешь по массиву.

Простой пример - retention после первой покупки

with
    -- первая покупка пользователя
    first_buy as
    (
        select
            user_id,
            min(event_time) AS first_purchase_at
        from events
        where event_name = 'purchase'
        group by user_id
    ),
    -- по дням после первой покупки смотрим, заходил ли пользователь в продукт
    activity as
    (
        select
            f.user_id,
            toDateDiff('day', f.first_purchase_at, e.event_time) AS day_diff
        from first_buy f
        join events e USING (user_id)
        where e.event_time >= f.first_purchase_at
          and e.event_time <  f.first_purchase_at + INTERVAL 30 DAY
    )
select
    retention(
        any(day_diff = 0), -- день покупки
        any(day_diff = 1), -- d1
        any(day_diff = 7), -- d7
        any(day_diff = 30) -- d30
    ) as r
from activity

Результат функции: массив из N элементов типа Array(UInt8).

Логика на примере одного пользователя r = [1, 1, 1]:

  • r[1] = 1, если для этого пользователя хоть раз выполнялся cond1, иначе 0

  • r[2] = 1, если cond1 И cond2 были выполнены

  • r[3] = 1, если cond1 И cond3 были выполнены

Дальше поверх этого делаем агрегацию:

select
    r1,
    r2,
    r3,
    r2 / r1 AS d1_retention,
    r3 / r1 AS d2_retention
from (
      select
          sum(r[1]) AS r1, -- размер когорты (кол-во пользователей, у кого выполнен cond1)
          sum(r[2]) AS r2, -- сколько из этой когорты вернулось на cond2 (day+1)
          sum(r[3]) AS r3 -- сколько из этой когорты вернулось на cond3 (day+2)
      from
        (
          ... тот самый select retention(...) AS r ...
        )
)

Функция помогает избежать возможных логических ошибок при работе с обычным group by (например, забыли отфильтровать d1 только по тем, кто был на d0) и избавляет от громоздких конструкций. Плюс на выходе сразу получаем удобный массив, который можно развернуть в таблицу через функции работы с массивами. Однако выигрыши в чистой производительности обычно небольшие.

4. Комбинаторы -If, -Distinct, -State, -Merge, -ForEach

ClickHouse умеет навешивать на агрегатные функции комбинаторы, которые резко сокращают количество подзапросов и делают код понятнее.

4.1. -If: условные агрегаты без CASE WHEN и без лишних JOIN

Вместо:

select
      countIf(os = 'iOS') as dau_ios,
      countIf(os = 'Android') as dau_android
from events
where event_name = 'app_open'

Можно писать:

select
    countIf(event_name = 'app_open' AND os = 'iOS') as dau_ios,
    countIf(event_name = 'app_open' AND os = 'Android') as dau_android
from events

Также и с другими функциям (например, anyIf, avgIf, uniqIf) - логика та же.

4.2. -Distinct: сегментация без двойного DISTINCT

Иногда нам нужно считать метрику по уникальным значениям, но при этом не хочется городить select distinct в подзапросе.

В clickhouse для этого есть комбинатор -Distinct, который можно навесить почти на любую агрегатную функцию.

  • sumDistinct(amount) - сумма по уникальным amount

  • avgDistinct(amount) - среднее по уникальным amount

  • avgDistinctIf(amount, cond) - среднее по уникальным amount, где cond = 1

Например, нужно посчитать средний чек по уникальным оплаченным заказам. Тут уже будем использовать таблицу-пример с заказами пользователя.

Из-за ретраев или багов один и тот же order_id иногда попадает в таблицу несколько раз. Хотим посчитать средний чек по кампании только по уникальным заказам со статусом paid.

Классический запрос без комбинаторов. Внутри уберем дубли по заказам, снаружи посчитаем среднее. Работает, но выглядит тяжеловато:

select
      campaign_id,
      avg(amount) as avg_paid_order
from
(
    select distinct campaign_id,
                    order_id,
                    amount
    from payments
    where status = 'paid'
)
group by campaign_id

Тоже самое с avgDistinctIf:

select
    campaign_id,
    avgDistinctIf(amount, status = 'paid') as avg_paid_order
from payments
group by campaign_id

4.3. -ForEach: работа с массивами как с маленькими таблицами

Когда в колонке лежит массив (например, список интересов пользователя, категории товаров в заказе),-ForEach позволяет применять агрегат к каждому элементу массива.

Комбинируя ForEach и If, можно довольно элегантно считать, например, конверсии по нескольким категориям товаров в корзине.

Например, нужно посчитать, у скольких пользователей была конверсия в покупку по категориям, если в одном заказе может быть сразу несколько категорий (categories - массив категорий товаров, например ['electronics', 'smartphone']).

Классический способ:

select
    category,
    uniqExact(user_id) as buyers
from
(
    select
        user_id,
        event_time,
        event_name,
        arrayJoin(categories) as category
    from events
    where event_name = 'purchase'
)
group by category
order by buyers desc

Мы разворачиваем массив с категориями в строки через arrayJoin и считаем покупателей по каждой категории. Работает, но приходится делать подзапрос и явный arrayJoin.

Тоже самое с ForEach

select
    uniqExactForEach(categories) as buyers_per_category
from events
where event_name = 'purchase'

uniqExactForEach(categories) пробегается по массиву для каждого пользователя и фактически считает uniqExact по каждому элементу массива отдельно. На выходе мы получаем массив с тем же размером, что и categories, только вместо строк там - количество уникальных пользователей для каждой позиции.

Важно! Агрегат считается по позициям массива, как будто каждый элемент — отдельная колонка. Это хорошо для фиксированных структур ([d0, d1, d2], [feature1, feature2, feature3]), но не подходит для задач вида "посчитать по всем уникальным значениям из массива" - там всё равно нужен arrayJoin.

Тут показан упрощенный пример на покупках. На самом деле категории покупок редко хранятся в фиксированной структуре. Лучше подойдет кейс, когда у пользователя хранится массив из 4 чисел [показы, клики, добавления в корзину, покупки] за день. sumForEach по этому массиву по сегменту (страна, кампания, продукт) вернет суммарные показы/клики/добавления/покупки - всё разом, без четырёх отдельных агрегатов.

Вместо заключения

В этой статье я рассмотрела маленький уголок экосистемы ClickHouse: sequenceMatch/sequenceCount для путей пользователя, windowFunnel для воронок, retention() для когорт и комбинаторы -If, -Distinct, -ForEach, которые позволяют писать менее шумный SQL.

На самом деле это только верхушка айсберга - функции ClickHouse можно изучать бесконечно долго. Почти всегда выбор создать ещё один CTE и три JOIN-а — это не единственный вариант, а просто самая привычная дорога.

Если вы в повседневной работе чаще всего используете sum(), count() и uniq(), попробуйте в следующем запросе осознанно заменить кусок логики одной из функций из этой статьи. Скорее всего, запрос станет короче, понятнее и будет проще поддерживаться.

В комментариях расскажите, какие "непопулярные" функции кликхаус успростили вам жизнь.