Практика ClickHouse: переводим отчёты курса и не ломаем числа

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

Всё, переезжаем. С понедельника события и сессии живут в ClickHouse, PostgreSQL выключаем через месяц. Перепиши отчёты, которые ты мне строил: retention, воронку, DAU и липкость. Совет директоров сверит цифры со старыми, и если поедет хоть одна, виноват будешь ты.

В прошлом уроке ты разобрал функции ClickHouse по одной. Сегодня переводим отчёты курса целиком и после каждого сверяем числа со старыми. Все запросы ниже проверены на ClickHouse с данными «Пограничья» и «Эпохи Корон»: числа совпали с уроками, где мы их считали.

Перевод с проверкой

Переводчики договоров делают обратный перевод: текст переводят на английский, другой человек переводит его обратно на русский, и смысл сверяют с оригиналом. Если «аренда на год» вернулась как «аренда навсегда», где-то ошибка.

С запросами так же. Перевод не готов, пока числа на старой и новой базе не совпали. Разница в одну десятую это не округление, а сигнал: где-то поменялся смысл. Ниже пять отчётов курса и что в каждом пришлось поменять.

Отчёт 1: DAU, MAU и липкость

Из урока про DAU и MAU, с чисткой ботов и тестовых аккаунтов:

WITH fake AS (
  SELECT id FROM players WHERE email LIKE '%@studio.dev'
  UNION DISTINCT
  SELECT player_id FROM sessions WHERE ended_at - started_at > 20 * 3600
),
daily AS (
  SELECT toDate(started_at) AS day, uniqExact(player_id) AS dau
  FROM sessions WHERE player_id NOT IN fake
  GROUP BY day
),
monthly AS (
  SELECT toStartOfMonth(started_at) AS month, uniqExact(player_id) AS mau
  FROM sessions WHERE player_id NOT IN fake
  GROUP BY month
)
SELECT m.month, round(avg(d.dau), 1) AS avg_dau, any(m.mau) AS mau,
       round(100 * avg(d.dau) / any(m.mau), 1) AS stickiness
FROM daily d
JOIN monthly m ON toStartOfMonth(d.day) = m.month
GROUP BY m.month
ORDER BY m.month;

Что поменялось:

• разность двух DateTime в ClickHouse это число секунд, а не интервал, поэтому interval '20 hours' стал 20 * 3600;
• UNION в ClickHouse пишут явно: UNION DISTINCT или UNION ALL;
• count(DISTINCT …) стал uniqExact, а не uniq: сверяем до игрока;
• toStartOfMonth сразу даёт дату, ::date не нужен; 100.0 не нужен, деление и так дробное;
• mau не входит в GROUP BY, поэтому берём его через any(m.mau): на месяц значение одно.

Майская липкость 16,5%, MAU 1021, средний DAU 169: сошлось с уроком 49 по всем шести месяцам.

Отчёт 2: воронка до и после релиза

Из урока про воронки: кто из начавших обучение его закончил, по платформам и периодам.

SELECT platform, after_release,
       countIf(started)   AS n_started,
       countIf(completed) AS n_completed,
       round(100 * n_completed / n_started, 1) AS rate
FROM (
  SELECT p.id,
         any(p.platform) AS platform,
         any(p.registered_at) >= '2026-05-14' AS after_release,
         max(e.name = 'tutorial_start')    AS started,
         max(e.name = 'tutorial_complete') AS completed
  FROM events e
  JOIN players p ON p.id = e.player_id
  WHERE p.email NOT LIKE '%@studio.dev'
  GROUP BY p.id
)
GROUP BY platform, after_release
ORDER BY after_release, platform;

bool_or стал max от условия: условие в ClickHouse это число 0 или 1, и максимум равен 1, если хоть одно событие подошло. count(*) FILTER стал countIf. Новые имена столбцов n_started и n_completed не случайны: ClickHouse разрешает ссылаться на псевдоним в соседних выражениях, и псевдоним started поверх столбца started легко запутает и тебя, и базу. Android после релиза 34,8%, как в уроке 48.

players здесь стоит справа от JOIN, и это правильно. Почему, объясняю в чек-листе переезда ниже.

Отчёт 3: когортный треугольник

Из урока про retention, недели жизни игрока:

SELECT cohort_week,
       uniqExact(r.id) AS players,
       round(100 * uniqExactIf(r.id, week_no = 1) / uniqExact(r.id), 1) AS w1,
       round(100 * uniqExactIf(r.id, week_no = 2) / uniqExact(r.id), 1) AS w2
FROM (
  SELECT id, toMonday(registered_at) AS cohort_week
  FROM players
  WHERE email NOT LIKE '%@studio.dev'
) r
LEFT JOIN (
  SELECT s.player_id,
         intDiv(toMonday(s.started_at) - toMonday(p.registered_at), 7) AS week_no
  FROM sessions s
  JOIN players p ON p.id = s.player_id
) a ON a.player_id = r.id
GROUP BY cohort_week
ORDER BY cohort_week;

Две правки, и обе тихие. date_trunc('week', …) стал toMonday, потому что toStartOfWeek по умолчанию начинает неделю с воскресенья. А / 7 стал intDiv(…, 7). Здесь разность понедельников всегда кратна семи, и обычное деление дало бы тот же номер недели. Но стоит посчитать неделю жизни от дня регистрации, (toDate(started_at) - toDate(registered_at)) / 7, и / вернёт 0,857 вместо 0. Условие week_no = 1 тогда поймает только тех, кто зашёл ровно на седьмой день: 615 игроков вместо 1212. Неделя 1 молча превратится в D7.

У когорты 5 января 45,1% на первой неделе и 29,6% на второй, как в уроке 47.