SQL для анализа: агрегаты, окна, воронки

Первые главы трека были о том, откуда берутся данные и почему им нельзя верить наивно: какое решение изменится от ответа, как устроен сбор, что с данными не так к моменту, когда вы их видите. Эта глава — про инструмент, которым вы задаёте вопрос. Инструмент неприятен тем, что почти никогда не отвечает «не знаю»: синтаксически корректный запрос всегда вернёт число, и это число будет выглядеть ровно так же убедительно, как правильное.

Отсюда главная мысль. Запрос — не способ достать данные, а записанное утверждение о том, что считать одним наблюдением. Строка результата — единица вашего вывода. Пока вы не можете одним предложением сказать, что такое одна строка на выходе («один оплаченный заказ», «один пользователь-день», «одна сессия»), любые SUM и AVG поверх неё бессмысленны, даже если запрос отработал за 200 миллисекунд и цифра красиво легла на дашборд. Разбирать будем PostgreSQL как эталонный диалект — строгий и стандартный; отличия ClickHouse, BigQuery, Snowflake и DuckDB отмечены отдельно. Устройство самих СУБД — в треке про базы данных: реляционная модель, индексы и планы запросов, колоночные хранилища и OLAP; как таблицы попадают в хранилище — в моделировании данных.

Аналитический SQL — это другой SQL

Транзакционный SQL отвечает на вопрос «покажи вот эту сущность»: точечное чтение по ключу, короткая транзакция. Аналитический отвечает на вопрос «как устроено множество»: он читает миллионы строк, схлопывает их в десяток чисел и почти ничего не пишет. Отсюда три отличия, определяющие весь стиль. Ошибка не падает, а искажает. В транзакционном коде баг ломает функциональность, и его замечают. В аналитическом запросе лишний JOIN не роняет ничего — он молча умножает выручку на три. Правильность зависит от контекста, а не от синтаксиса. AVG(rating) всегда законно; верно ли оно — зависит от того, что ваша система делает с отсутствующей оценкой. Ни один линтер это не поймает. Запрос — часть аргументации. Число поедет в решение о деньгах или сроках, значит, запрос должен читаться другим человеком как доказательство: видно, какие допущения сделаны и где они могут не выполниться. Та же дисциплина, о которой системный анализ говорит про требования, — только допущение прячется не в тексте, а в двух словах LEFT JOIN.

Данные, на которых будем работать

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

Обратите внимание на две колонки в EVENTS, которые в учебных схемах обычно опускают: event_ts — когда событие произошло на устройстве, ingested_at — когда оно доехало до хранилища. Разница между ними и есть «поздняя доставка», из-за которой вчерашняя цифра завтра меняется (глава о качестве данных). В запросах ниже :period_start и :period_end — параметры, а не now(); почему это принципиально, разберём в разделе о воспроизводимости.

Гранулярность: первый вопрос к любому запросу

Соединение таблиц с отношением «один ко многим» размножает строки. Это не баг, а определение операции — но именно на нём ломается больше аналитических выводов, чем на всём остальном SQL вместе взятом.

-- НЕВЕРНО: одна строка результата — это позиция заказа,
-- а total_amount живёт на уровне заказа
SELECT u.country,
       SUM(o.total_amount) AS revenue,     -- сложится столько раз, сколько позиций в заказе
       COUNT(*)            AS orders_cnt   -- посчитает позиции, а не заказы
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN users u       ON u.user_id  = o.user_id
WHERE o.created_at >= :period_start AND o.created_at < :period_end
GROUP BY u.country;

Запрос отработает. Выручка вырастет примерно во столько раз, сколько в среднем позиций в заказе, — и если в отчёте нет эталона для сверки, ошибку заметят через квартал. Лечение — свернуть подчинённую таблицу до нужной зернистости до соединения:

WITH items_rolled AS (             -- ровно одна строка на order_id
    SELECT order_id, SUM(qty * price) AS items_amount, COUNT(*) AS items_cnt
    FROM order_items GROUP BY order_id
)
SELECT u.country,
       SUM(o.total_amount) AS revenue,
       COUNT(*)            AS orders_cnt,
       SUM(r.items_cnt)    AS items_cnt,
       SUM(o.total_amount - COALESCE(r.items_amount, 0)) AS delta_discount_shipping
FROM orders o
JOIN users u             ON u.user_id  = o.user_id
LEFT JOIN items_rolled r ON r.order_id = o.order_id
WHERE o.created_at >= :period_start AND o.created_at < :period_end
GROUP BY u.country;

Последняя колонка — не украшение, а встроенная проверка: если сумма позиций расходится с суммой заказа не на величину скидок и доставки, вы узнаете об этом из того же запроса, а не из письма бухгалтерии. И половина JOIN в аналитических запросах не нужна вовсе: таблицу присоединяют не ради колонок, а ради условия «такая запись существует». Для этого есть EXISTS, который строк не размножает.

-- Смотрели товар, но ни разу не купили: полусоединение плюс антисоединение
SELECT COUNT(*) AS viewed_but_never_bought
FROM users u
WHERE EXISTS     (SELECT 1 FROM events e WHERE e.user_id = u.user_id AND e.event_name = 'view_product')
  AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id AND o.status = 'paid');

Рядом ловушка: NOT IN с подзапросом ведёт себя иначе, чем NOT EXISTS. Если подзапрос вернёт хотя бы один NULL, всё выражение станет NULL, и результат окажется пустым — без ошибки и без предупреждения. Поэтому SELECT COUNT(*) FROM users WHERE user_id NOT IN (SELECT user_id FROM orders) даст ноль, стоит появиться одной строке с user_id IS NULL. Правило: в аналитике NOT IN с подзапросом не используют, только NOT EXISTS либо LEFT JOIN ... WHERE right.key IS NULL.

Как СУБД на самом деле исполняет запрос

Порядок, в котором вы пишете предложения, не совпадает с порядком, в котором они логически применяются. Половина вопросов вида «почему псевдоним не виден в WHERE» отпадает, если держать этот порядок в голове.

Три следствия на каждый день. Псевдоним из SELECT виден в ORDER BY и GROUP BY (в PostgreSQL), но не в WHERE — его там ещё не существует. Оконная функция вычисляется после GROUP BY и HAVING, поэтому фильтровать по ней можно только уровнем выше: подзапросом, CTE или QUALIFY в тех диалектах, где он есть (ClickHouse, BigQuery, Snowflake, DuckDB; в PostgreSQL и MySQL его нет). Условие в WHERE сокращает объём до агрегации, в HAVING — после; фильтр по «сырым» полям, случайно оказавшийся в HAVING, работает и просто стоит дороже.

Агрегаты, которые считают не то

SELECT
    COUNT(*)                  AS rows_total,      -- строк, включая полностью пустые
    COUNT(o.user_id)          AS rows_with_user,  -- строк, где user_id не NULL
    COUNT(DISTINCT o.user_id) AS distinct_users,  -- разных пользователей
    COUNT(*) FILTER (WHERE o.status = 'paid')                  AS paid_rows,
    COUNT(DISTINCT o.user_id) FILTER (WHERE o.status = 'paid') AS paying_users,
    AVG(o.total_amount)                    AS avg_naive,   -- строки с NULL выпали из знаменателя
    COUNT(o.total_amount)::numeric / COUNT(*) AS fill_rate, -- доля непустых значений
    COALESCE(SUM(o.total_amount), 0)       AS revenue      -- SUM пустого множества = NULL
FROM orders o;

COUNT(*) считает строки, COUNT(колонка) — непустые значения; разница между ними и есть доля пропусков, и её полезно выводить рядом с метрикой, а не выяснять постфактум. Отдельно про COUNT(o.order_id) после LEFT JOIN: он даст 0 для пользователей без заказов, а COUNT(*) — единицу, потому что строка есть, просто с NULL справа. Это самая частая причина отчётов вида «у нас 100 % пользователей сделали заказ». Дальше — AVG, который игнорирует NULL. Поведение разумное и одновременно подменяющее вопрос: «средняя оценка 4,6» превращается в «средняя оценка среди тех, кто оценил», а это разные величины, если оценивают в основном довольные. Показывать долю заполненности рядом — условие интерпретируемости, а не вежливость. Третья мина — целочисленное деление: COUNT(*) FILTER (...) / COUNT(*) в PostgreSQL это bigint / bigint, то есть усечение, и конверсия всегда выходит нулевой. Правильно так:

SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'paid')
             / NULLIF(COUNT(*), 0), 2) AS cr_pct
FROM orders;

FILTER (WHERE ...) — стандартный синтаксис (PostgreSQL, DuckDB, SQLite 3.30+); в диалектах без него пишут SUM(CASE WHEN ... THEN 1 ELSE 0 END). Одним проходом получается сводная таблица:

SELECT
    date_trunc('day', event_ts AT TIME ZONE 'Europe/Moscow')::date AS d,
    COUNT(DISTINCT user_id) FILTER (WHERE platform = 'ios')     AS dau_ios,
    COUNT(DISTINCT user_id) FILTER (WHERE platform = 'android') AS dau_android,
    COUNT(DISTINCT user_id) FILTER (WHERE platform = 'web')     AS dau_web,
    COUNT(DISTINCT user_id)                                     AS dau_total
FROM events
WHERE event_ts >= :period_start AND event_ts < :period_end
GROUP BY 1 ORDER BY 1;

Заметьте: dau_total не равен сумме трёх колонок, и это правильно — один человек мог зайти и с телефона, и с ноутбука. Если в отчёте суммы по разрезам обязаны сходиться с итогом, значит, метрика должна быть аддитивной, а COUNT(DISTINCT user_id) таковой не является. На аддитивности молча строится половина дашбордов, и потом «сумма по регионам не бьётся с итогом» превращается в трёхдневное расследование. Когда разрезов много, несколько уровней агрегации считают за один проход через GROUP BY GROUPING SETS ((country, platform), (country), ()) — но обязательно с колонкой GROUPING(country, platform): без неё строка-итог (где country IS NULL, потому что разрез свёрнут) неотличима от строки с реально неизвестной страной.

Ещё одна классика — среднее по средним:

-- НЕВЕРНО: каждый день получает равный вес независимо от числа заказов
SELECT AVG(daily_avg) AS aov
FROM (SELECT date_trunc('day', created_at) AS d, AVG(total_amount) AS daily_avg
      FROM orders GROUP BY 1) t;

-- Верно: средний чек за период — это выручка, делённая на число заказов
SELECT SUM(total_amount) / NULLIF(COUNT(*), 0) AS aov FROM orders;

То же относится к перцентилям: усреднять p95 по дням, шардам или сервисам нельзя никогда — среднее перцентилей не является перцентилем. Почему и что с этим делать — в описательной статистике и главе о распределениях.

Фильтр, который тихо меняет вопрос

-- Задумано: все пользователи и число их заказов за период
SELECT u.user_id, COUNT(o.order_id) AS orders_cnt   -- COUNT(*) дал бы 1 вместо 0
FROM users u
LEFT JOIN orders o
       ON o.user_id    = u.user_id
      AND o.created_at >= :period_start   -- ← перенесите эти две строки в WHERE,
      AND o.created_at <  :period_end     --    и LEFT JOIN молча станет INNER JOIN
GROUP BY u.user_id;

Условие в WHERE применяется после соединения, а у пользователей без заказов o.created_at равен NULL; сравнение с NULL даёт не «истину» и не «ложь», а NULL, и строка отбрасывается. Из выборки исчезают ровно те, ради кого писался LEFT JOIN: знаменатель конверсии уменьшается, конверсия растёт, отчёт выглядит отлично. Мнемоника: ON описывает, что считать парой; WHERE — какие строки оставить. Для внутреннего соединения это одно и то же, для внешнего — нет.

Оконные функции

Оконная функция считает агрегат, не схлопывая строки. Это её единственное отличие от GROUP BY — и оно решает целый класс задач: «сколько накопилось к этой дате», «на сколько выросло со вчера», «какая по счёту покупка у клиента», «в какую дециль он попал». У окна три независимые части, и путают обычно вторую с третьей.

PARTITION BY режет строки на независимые секции — между секциями функция не смотрит никогда. ORDER BY задаёт порядок внутри секции: сам по себе он ничего не считает, он определяет, что значит «до» и «после». Рамка (ROWS / RANGE / GROUPS) выбирает подмножество строк секции, по которому берётся агрегат для текущей строки.

Рамка по умолчанию — самая дорогая деталь всего SQL. Если ORDER BY в окне написан, а рамка нет, стандарт подставляет RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE работает не по строкам, а по значениям сортировки: все строки с одинаковым значением (peers) попадают в рамку целиком.

SELECT event_date, revenue,
       SUM(revenue) OVER (ORDER BY event_date          -- честный итог по строкам
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows,
       -- рамка по умолчанию: строки с одинаковой датой получат одно и то же число
       SUM(revenue) OVER (ORDER BY event_date)                              AS running_range
FROM daily_revenue;

Для данных с уникальным ключом сортировки обе формы совпадают. Как только появляются дубли по дате — а они появляются, стоит добавить разрез, — числа расходятся, и расходятся молча.

Скользящее среднее — место, где рамка обязана быть осознанной:

-- НЕВЕРНО, если в daily_revenue бывают дни без строк:
-- рамка отсчитывает 7 СТРОК, а не 7 дней, и тихо уезжает в прошлое
AVG(revenue) OVER (ORDER BY event_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- Верно: рамка по значению даты (PostgreSQL 11+)
AVG(revenue) OVER (ORDER BY event_date
                   RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)

В диалектах без интервальных рамок сначала достраивают календарь через generate_series и LEFT JOIN, чтобы пропущенных дней не осталось физически. И там прячется содержательное решение, а не техническое: пропущенный день — это «выручки не было» или «данные не доехали»? Первый случай — COALESCE(revenue, 0), второй — NULL и исключение дня из среднего. Выбор меняет число, и в коде он должен быть виден.

Ещё две ловушки рамки и один рабочий приём:

-- Вернёт значение ТЕКУЩЕЙ строки: рамка по умолчанию заканчивается на CURRENT ROW
LAST_VALUE(status) OVER (PARTITION BY order_id ORDER BY changed_at)
-- Верно: явно раскрыть рамку до конца секции...
LAST_VALUE(status) OVER (PARTITION BY order_id ORDER BY changed_at
                         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
-- ...либо проще и дешевле — FIRST_VALUE с обратной сортировкой
FIRST_VALUE(status) OVER (PARTITION BY order_id ORDER BY changed_at DESC)

-- Дедупликация до «последнего состояния»: по одной актуальной строке на сущность
WITH ranked AS (
    SELECT p.*,
           ROW_NUMBER() OVER (PARTITION BY p.user_id
                              ORDER BY p.changed_at DESC, p.change_id DESC) AS rn
    FROM profile_changes p
)
SELECT user_id, plan, country, changed_at FROM ranked WHERE rn = 1;

Второй ключ сортировки обязателен. Без него при одинаковых changed_at выбор строки не определён — запрос будет возвращать разные ответы на разных запусках, версиях планировщика и числе параллельных воркеров. Такая нестабильность не воспроизводится по требованию и съедает дни отладки. В ClickHouse, BigQuery, Snowflake и DuckDB тот же запрос пишется короче через QUALIFY ROW_NUMBER() OVER (...) = 1.

Перцентили в PostgreSQL — не окно, а упорядоченный агрегат:

SELECT percentile_cont(0.5)  WITHIN GROUP (ORDER BY duration_ms) AS p50,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY duration_ms) AS p95,  -- интерполяция
       percentile_disc(0.95) WITHIN GROUP (ORDER BY duration_ms) AS p95_real_row,
       AVG(duration_ms) AS mean, COUNT(*) AS n
FROM page_loads
WHERE loaded_at >= :period_start AND loaded_at < :period_end;

percentile_cont интерполирует между соседними наблюдениями, percentile_disc возвращает реально существовавшее значение. Для времени отклика берут первое, для «типичного чека» — второе, потому что чека 1247,5 рубля не существовало. Колонка n рядом с p95 нужна всегда: перцентиль по 40 наблюдениям — почти шум, и насколько именно, разбирает глава о неопределённости.

Сессионизация: от событий к поведению

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

WITH gaps AS (
    SELECT user_id, event_id, event_ts,
           LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts, event_id) AS prev_ts
    FROM events
    WHERE event_ts >= :period_start AND event_ts < :period_end
), flags AS (
    SELECT *, CASE WHEN prev_ts IS NULL OR event_ts - prev_ts > INTERVAL '30 minutes'
                   THEN 1 ELSE 0 END AS is_session_start
    FROM gaps
), numbered AS (
    SELECT *, SUM(is_session_start) OVER (
                  PARTITION BY user_id ORDER BY event_ts, event_id
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- ROWS, не RANGE!
              ) AS session_no
    FROM flags
)
SELECT user_id, session_no, MIN(event_ts) AS started_at,
       MAX(event_ts) - MIN(event_ts) AS duration, COUNT(*) AS events_cnt
FROM numbered GROUP BY user_id, session_no;

Приём — нарастающая сумма флагов как идентификатор группы — стоит запомнить: им решается любая задача «разбей упорядоченную последовательность на серии». Явный ROWS здесь не педантизм: события регулярно совпадают по времени с точностью до секунды, и RANGE склеил бы их в одну рамку, сбив нумерацию. А вот тайм-аут в 30 минут — конвенция, а не физика. Он взят из веб-аналитики девяностых и к мобильному приложению с фоновыми пингами отношения не имеет. Меняя его на 10 или 60 минут, вы меняете число сессий, среднюю длительность и «сессий на пользователя» — метрику, которую кто-то потом поставит в цель. Ровно тот механизм, о котором предупреждают глава про Гудхарта и системное мышление. Вторая честная оговорка: сессии, начавшиеся до :period_start или не закрывшиеся к :period_end, обрезаны границей выборки — средняя длительность занижена, и запрос об этом не сообщит.

Воронки: три способа посчитать и три разных ответа

Воронка — самая востребованная и самая часто неверная конструкция продуктовой аналитики. Причина в том, что «конверсия из просмотра в покупку» кажется однозначной величиной, а на деле это семейство разных величин.

Способ 1 — независимые счётчики. Ни последовательности, ни привязки шагов друг к другу:

SELECT event_name, COUNT(DISTINCT user_id) AS users
FROM events
WHERE event_ts >= :period_start AND event_ts < :period_end
  AND event_name IN ('view_product', 'add_to_cart', 'checkout', 'purchase')
GROUP BY event_name;

В «покупку» здесь попадёт пользователь, который вернулся по пушу к сохранённой корзине и карточку товара в этом месяце не смотрел. Единственное, что запрос честно измеряет, — размер каждой аудитории по отдельности. Как только вы говорите «из просмотревших дошли до покупки 12,75 %», вы утверждаете больше, чем посчитали.

Способ 2 — по первому касанию. Один проход, порядок шагов соблюдён, монотонность гарантирована конструкцией:

WITH per_user AS (
    SELECT user_id,
           MIN(event_ts) FILTER (WHERE event_name = 'view_product') AS t_view,
           MIN(event_ts) FILTER (WHERE event_name = 'add_to_cart')  AS t_cart,
           MIN(event_ts) FILTER (WHERE event_name = 'checkout')     AS t_checkout,
           MIN(event_ts) FILTER (WHERE event_name = 'purchase')     AS t_purchase
    FROM events
    WHERE event_ts >= :period_start AND event_ts < :period_end
    GROUP BY user_id
)
SELECT COUNT(*) FILTER (WHERE t_view IS NOT NULL)                      AS s1_view,
       COUNT(*) FILTER (WHERE t_cart > t_view)                         AS s2_cart,
       COUNT(*) FILTER (WHERE t_cart > t_view AND t_checkout > t_cart) AS s3_checkout,
       COUNT(*) FILTER (WHERE t_cart > t_view AND t_checkout > t_cart
                          AND t_purchase > t_checkout)                 AS s4_purchase
FROM per_user;

Сравнения с NULL дают NULL, поэтому пользователь без нужного события просто не попадает в счётчик — это работает верно. Но условия сравнивают только первые вхождения каждого события. Пользователь, который в начале месяца случайно положил товар в корзину, а через две недели прошёл нормальный путь «карточка → корзина → чекаут → оплата», в шаг 2 не попадёт: его MIN(add_to_cart) меньше, чем MIN(view_product). Способ недосчитывает систематически, и тем сильнее, чем длиннее цикл покупки.

Способ 3 — строгая последовательность с окном. Каждый CTE агрегирует по user_id, поэтому размножения строк не происходит ни на одном соединении:

WITH s1 AS (
    SELECT user_id, MIN(event_ts) AS t1
    FROM events
    WHERE event_name = 'view_product'
      AND event_ts >= :period_start AND event_ts < :period_end
    GROUP BY user_id
), s2 AS (
    SELECT s1.user_id, MIN(e.event_ts) AS t2
    FROM s1 JOIN events e ON e.user_id = s1.user_id AND e.event_name = 'add_to_cart'
         AND e.event_ts > s1.t1 AND e.event_ts <= s1.t1 + INTERVAL '24 hours'
    GROUP BY s1.user_id
), s3 AS (
    SELECT s2.user_id, MIN(e.event_ts) AS t3
    FROM s2 JOIN events e ON e.user_id = s2.user_id AND e.event_name = 'checkout'
         AND e.event_ts > s2.t2 AND e.event_ts <= s2.t2 + INTERVAL '24 hours'
    GROUP BY s2.user_id
), s4 AS (
    SELECT s3.user_id, MIN(e.event_ts) AS t4
    FROM s3 JOIN events e ON e.user_id = s3.user_id AND e.event_name = 'purchase'
         AND e.event_ts > s3.t3 AND e.event_ts <= s3.t3 + INTERVAL '24 hours'
    GROUP BY s3.user_id
)
SELECT (SELECT COUNT(*) FROM s1) AS step1_view,  (SELECT COUNT(*) FROM s2) AS step2_cart,
       (SELECT COUNT(*) FROM s3) AS step3_checkout, (SELECT COUNT(*) FROM s4) AS step4_purchase;

На одних и тех же данных за июнь получаются разные числа:

Сквозная конверсия — 12,75 % против 12,25 %. Разница в 200 покупок не погрешность, а другой вопрос: способ 1 включил людей, купивших без просмотра карточки в этом окне.

Ни один из трёх ответов не «настоящий». Настоящий зависит от того, зачем вы считаете. Оцениваете эффект переделки карточки товара — нужна строгая последовательность с окном порядка длительности сессии. Планируете нагрузку на платёжный шлюз — нужен способ 1, потому что шлюзу всё равно, откуда пришёл пользователь. Вопрос «какое решение изменится» из первой главы трека здесь не абстракция, а выбор между двумя ветками кода. Полезно приложить к отчёту и определение шага: «add_to_cart» — это нажатие кнопки, успешный ответ сервера или появление товара в корзине? Три разных числа, и продуктовые метрики ломаются ровно на этом. В ClickHouse этот класс задач закрывает встроенная windowFunnel(86400)(event_ts, cond1, ..., cond4): она ищет самую длинную цепочку условий в скользящем окне и возвращает достигнутый уровень на пользователя, а накопительные счётчики получаются как countIf(level >= k). Семантика окна там другая — 24 часа отсчитываются от первого шага на всю цепочку, а в способе 3 заново от каждого шага. Это опять разные метрики; документация описывает поведение точно.

Когорты: техническая основа

Полный разбор кривых удержания — в главе о когортах; здесь скелет запроса, на котором видна ещё одна ошибка.

WITH cohort AS (
    SELECT user_id,
           date_trunc('week', MIN(event_ts) AT TIME ZONE 'Europe/Moscow')::date AS cohort_week
    FROM events GROUP BY user_id
), activity AS (
    SELECT DISTINCT c.cohort_week, e.user_id,
           (date_trunc('week', e.event_ts AT TIME ZONE 'Europe/Moscow')::date
            - c.cohort_week) / 7 AS week_no
    FROM events e JOIN cohort c ON c.user_id = e.user_id
), per_week AS (
    -- DISTINCT выше свернул события в людей, поэтому COUNT(*) считает людей
    SELECT cohort_week, week_no, COUNT(*) AS active_users
    FROM activity GROUP BY cohort_week, week_no
)
SELECT p.cohort_week, p.week_no, p.active_users,
       ROUND(100.0 * p.active_users / b.active_users, 1) AS retention_pct
FROM per_week p
JOIN per_week b ON b.cohort_week = p.cohort_week AND b.week_no = 0
ORDER BY p.cohort_week, p.week_no;

Ошибка, которую запрос допускает по умолчанию: свежие когорты выглядят лучше старых, потому что для них ещё не наступила восьмая, двенадцатая, двадцатая неделя. Строки просто отсутствуют, кривая обрывается, а глаз достраивает её оптимистично. Лечится ограничением: показывать week_no только до горизонта, который прожили все когорты выборки. По природе это тот же эффект выжившего, что и в главе об источниках данных, только созданный не сбором, а календарём.

Время, часовые пояса и воспроизводимость

Сутки — не свойство данных, а решение аналитика. timestamptz хранится в UTC; «дневная» метрика существует только относительно выбранного пояса, и date_trunc('day', event_ts AT TIME ZONE 'Europe/Moscow') даёт другой DAU, чем то же выражение для UTC. Разница видна на границах суток — как раз там, где обычно ищут аномалии.

now() в запросе уничтожает воспроизводимость. Отчёт, вчера показавший 42, сегодня показывает 39, и невозможно понять, изменились данные или сместилось окно. Поэтому во всех примерах главы стоят параметры :period_start и :period_end, а не now() - INTERVAL '30 days'.

У поздних событий нужна отсечка по времени приёма, иначе «выручка за 1 июня» будет расти ещё неделю и два отчёта с одной датой в заголовке не сойдутся: к границам по event_ts добавляется AND ingested_at < :snapshot_ts — «данные по состоянию на этот момент». И наконец, полуоткрытые интервалы [начало, конец) во всех примерах — не стилистика. BETWEEN включает верхнюю границу, поэтому BETWEEN '2026-06-01' AND '2026-06-30' для timestamptz теряет почти все события 30 июня, а BETWEEN '2026-06-01' AND '2026-07-01' дважды считает полночь при соседних периодах.

Производительность: запрос, который считает сутки

Аналитический запрос по определению читает много. Наибольший эффект дают три вещи. Не оборачивайте колонку функцией в фильтре — это убивает и индекс, и отсечение партиций: WHERE date(event_ts) = DATE '2026-06-15' заставляет читать всё, а WHERE event_ts >= ... AND event_ts < ... — нет. Фильтруйте до соединения. В PostgreSQL 12+ CTE по умолчанию встраиваются в основной запрос, но WITH ... AS MATERIALIZED вернёт барьер оптимизации; иногда это нужно, чаще вредно. Читайте план: EXPLAIN (ANALYZE, BUFFERS) показывает, где реально ушло время, а где вы это предположили (индексы и планы). Считайте предагрегаты один раз: витрина «пользователь-день» строится ночью и переиспользуется десятком отчётов — это дешевле десяти полных сканов и, что важнее, гарантирует всем отчётам одно определение метрики (оркестрация).

COUNT(DISTINCT ...) по миллиардам строк дорог, поэтому колоночные СУБД предлагают приближённые версии на основе HyperLogLog: uniqCombined в ClickHouse, APPROX_COUNT_DISTINCT в BigQuery, расширение postgresql-hll. Относительная стандартная ошибка HLL зависит только от числа регистров:

$$\sigma_{rel} \approx \frac{1.04}{\sqrt{m}}$$

При $m = 16384$ это примерно 0,8 %. Практический вывод важнее формулы: на приближённых счётчиках нельзя сравнивать варианты A/B-теста, где ожидаемый эффект — доли процента. Разница между 100 000 и 100 400 уникальных пользователей полностью тонет в погрешности метода. Для дашборда с DAU в миллионах приближение отлично, для решения об эффекте — нет. Что значит «эффект тонет в погрешности», разбирает глава об A/B-тестах; аппарат — в теории вероятностей и статистике. Исходная работа: Flajolet et al., HyperLogLog: the analysis of a near-optimal cardinality estimation algorithm, 2007.

Запрос как код: ревью и тесты

Аналитический SQL живёт в репозитории и проверяется автоматически — иначе деградирует так же, как любой код без тестов. Тест здесь — запрос, который в норме возвращает пустой результат.

-- 1. Нет дублей по ключу витрины
SELECT order_id, COUNT(*) FROM mart_orders GROUP BY order_id HAVING COUNT(*) > 1;

-- 2. Витрина не разошлась с источником по строкам и по сумме
SELECT * FROM (
    SELECT (SELECT COUNT(*) FROM stg_orders) AS src_rows, (SELECT COUNT(*) FROM mart_orders) AS dst_rows,
           (SELECT SUM(total_amount) FROM stg_orders) AS src_sum, (SELECT SUM(total_amount) FROM mart_orders) AS dst_sum
) t WHERE src_rows <> dst_rows OR src_sum IS DISTINCT FROM dst_sum;

-- 3. Монотонность воронки: шаг не может быть больше предыдущего
SELECT * FROM mart_funnel_daily
WHERE step2_cart > step1_view OR step3_checkout > step2_cart
   OR step4_purchase > step3_checkout;

Третий тест ловит ровно тот класс ошибок, которому посвящена половина главы: если запрос начнёт возвращать «покупок больше, чем просмотров», значит, в нём разъехалась гранулярность. Такие инварианты дешевле любого код-ревью и работают ночью. Формализуют это тесты dbt и Great Expectations; общий контур владения данными — в главе о data governance.

Чек-лист ревью аналитического запроса: (1) что такое одна строка результата — ответ должен помещаться в предложение; (2) не размножает ли соединение строки, какая сторона «многие»; (3) не превратилось ли внешнее соединение во внутреннее фильтром в WHERE; (4) границы периода полуоткрытые, пояс явный, now() отсутствует; (5) у каждого окна с ORDER BY рамка написана явно; (6) знаменатель защищён от нуля и приведён к numeric; (7) метрика аддитивна по разрезам — а если нет, сказано ли это в отчёте.

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

Мини-итог

SQL не защищает вас от неверного вывода — он его выполняет. Всё, что можно сделать, — сделать допущения видимыми.

• Гранулярность первична. Одна строка результата — это что? Без ответа любой агрегат бессмыслен. Соединение меняет зернистость: нужны только колонки-условия — берите EXISTS; нужны числа — сворачивайте подчинённую таблицу заранее.
• NULL тихо меняет вопрос. AVG считает по ответившим, WHERE после LEFT JOIN выбрасывает тех, ради кого он писался, NOT IN возвращает пустоту.
• У окна три части, и рамка — самая опасная. Пишите ROWS явно всегда, когда есть ORDER BY.
• Воронка — не одно число, а семейство. Выбор способа считать — это выбор вопроса, а не оптимизация.
• Время требует явности (пояс, полуоткрытый интервал, отсечка по ingested_at, никаких now()), а запрос — это код: инварианты в CI ловят разъехавшуюся гранулярность лучше, чем внимательность.

Для дальнейшего чтения: Cathy Tanimura, SQL for Data Analysis (O’Reilly, 2021) — лучшая книга именно про аналитический SQL; Bill Karwin, SQL Antipatterns (Pragmatic Bookshelf) — про то, как схема портит запросы; modern-sql.com Маркуса Винанда — разбор стандарта и различий диалектов, включая оконные функции; документация PostgreSQL по оконным функциям — короткая и точная. Дальше числа из этих запросов придётся интерпретировать, и там начинается отдельный слой ошибок: «среднее время ответа выросло на 12 %» звучит однозначно ровно до момента, когда вы смотрите на распределение.

Что дальше

Описательная статистика: среднее врёт чаще, чем кажется — почему одно число почти никогда не описывает выборку, чем медиана отличается от среднего по смыслу, а не по формуле, и какой минимальный набор статистик показывать рядом, чтобы читатель не сделал ложный вывод.