Условная агрегация в SQL: CASE, FILTER и метрики в одном запросе

Коротко

CASE удобен для переносимого SQL и сложных ветвлений. FILTER делает PostgreSQL-запрос компактнее, когда у каждого агрегата своё условие. В обоих случаях сначала фиксируй базовую когорту и зерно, затем считай показатели.

• Определи зерно результата до написания оператора.
• Разделяй фильтрацию строк, групп и рассчитанных показателей.
• Проверь NULL, дубли, границы периода и стабильность сортировки.
• Сверь запрос на маленьком примере перед публикацией метрики.

Какую задачу решает этот оператор

CASE WHEN превращает условие в значение, которое можно агрегировать. FILTER синтаксически отделяет условие конкретного агрегата и хорошо читается в PostgreSQL. Для конверсии важно считать пользователей или сущности в числителе и знаменателе на одном уровне, а не делить количество событий на количество пользователей случайно.

В отчёте по signup можно одновременно посчитать новых пользователей, тех, кто дошёл до activation, и тех, кто оплатил. Главное — определить окно: activation в день регистрации, в течение семи дней или когда угодно после неё. SQL не решит эту методологическую развилку за команду.

Рабочий пример для аналитика

Сначала собери базовую когорту в CTE. Затем добавь условные счётчики и деления через NULLIF. Для пользовательских метрик используй COUNT(DISTINCT user_id), если поток событий может повторяться. После запроса проверь, что подмножества не используют разные даты.

Пример ниже намеренно оставляет явными период, ключи связи и уровень агрегации. Если заменить их на «как принято в базе», запрос может начать считать другую сущность. Сначала адаптируй названия колонок под свою схему, затем проверь результат на нескольких известных строках.

WITH cohort AS (
  SELECT user_id, DATE_TRUNC('week', registered_at) AS week
  FROM users
  WHERE registered_at >= DATE '2026-01-01'
)
SELECT week, COUNT(DISTINCT user_id) AS users,
       COUNT(DISTINCT user_id) FILTER (WHERE activated_at < registered_at + INTERVAL '7 days') AS activated,
       COUNT(DISTINCT user_id) FILTER (WHERE first_paid_at IS NOT NULL) AS paid
FROM cohort GROUP BY week ORDER BY week;

Как проверить результат

Первый тест — арифметический: совпадает ли число строк с ожидаемым зерном, а сумма подгрупп с общей суммой? Второй — поведенческий: что происходит на пустой группе, NULL, повторном событии и границе периода? Третий — сравнительный: можно ли получить тот же результат независимым способом на небольшом срезе?

Не ограничивайся тем, что запрос выполнился. Рабочий SQL должен быть объяснимым: другой аналитик понимает источник, фильтр, связь и причину выбранного оператора. Если результат меняется после добавления строки, зафиксируй, какое бизнес-правило это объясняет.

Для воспроизводимости сохрани рядом с запросом дату проверки, ожидаемый результат на контрольной выборке и версию определения метрики. Если SQL выполняется в BI, проверь, какие параметры подставляет интерфейс и не меняется ли часовая зона. Перед передачей запроса другому человеку убери неявные зависимости от сортировки, текущей даты и локальных настроек.

Зерно данных и границы расчёта

До запуска запроса выпиши, что означает одна строка в каждой таблице. В orders это может быть заказ, в order_items — позиция заказа, а в events — отдельное событие. Оператор работает поверх этого зерна: если соединить таблицы разных уровней без промежуточной агрегации, сумма и количество начнут отвечать уже на другой вопрос.

Зафиксируй период, часовую зону, статус записи и правило для повторов. Для событий важно отделить повторную отправку от двух реальных действий пользователя. Для денежных показателей нужно понять, входят ли скидки, возвраты и отмены. Эти условия должны быть видны в запросе или в названном CTE, а не жить только в голове автора.

Проверка на маленьком наборе

Сложный запрос стоит сначала проверить на пяти-десяти строках, где ответ можно посчитать вручную. Добавь один NULL, один дубль, одну строку ровно на границе периода и одну запись без связанной строки. Такой набор быстрее показывает ошибку в логике, чем просмотр тысячи строк в BI.

После ручной проверки сравни контрольные суммы и количество уникальных ключей до и после каждого важного шага. Если JOIN изменил число строк, это не обязательно ошибка, но причина должна быть понятна. Если оператор удаляет строки, запиши, какие именно условия это делают и почему они соответствуют бизнес-вопросу.

От запроса к рабочему решению

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

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

Как читать чужой SQL-запрос

Начинай не с отдельных операторов, а с внешнего результата. Найди SELECT, посмотри, какие поля группируются, затем поднимись к FROM и JOIN. У каждого источника подпиши примерное зерно и ожидаемую кардинальность связи. После этого проверь WHERE: он может убрать пользователей до расчёта, а может незаметно исключить строки, которые нужны для знаменателя.

Если в запросе есть CTE, читай их сверху вниз и фиксируй, что изменилось на каждом шаге: только названия, число строк, зерно или бизнес-смысл. Если есть окно, проверь PARTITION BY и ORDER BY. Если есть отрицательное условие, отдельно протестируй NULL. Такой порядок чтения помогает делать ревью по логике, а не спорить о стиле форматирования.

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

Что чаще всего ломается

CASE без ELSE возвращает NULL, что влияет на SUM и COUNT. COUNT(CASE WHEN ...) и SUM(CASE WHEN ... ELSE 0 END) похожи, но читаются по-разному. Не считай conversion как AVG(event_flag), если одна строка — событие, а не пользователь.

Отдельно проверь изменение зерна после JOIN или разворачивания массива. Большая часть странных цифр появляется не из-за сложного синтаксиса, а потому что одна строка стала описывать два разных факта. Полезный приём — временно вывести ключ и посчитать COUNT(*) по нему до и после спорного шага.

Когда выбрать другой подход

CASE удобен для переносимого SQL и сложных ветвлений. FILTER делает PostgreSQL-запрос компактнее, когда у каждого агрегата своё условие. В обоих случаях сначала фиксируй базовую когорту и зерно, затем считай показатели.

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

Мини-практика

Собери одну таблицу по неделям с total_users, activated_users, paid_users, mobile_users и error_users. Затем добавь guardrail: долю отмен. Проверь результат на маленьком наборе данных вручную и сравни с независимым запросом по каждой метрике.

После решения напиши короткое объяснение в формате: «одна строка результата — …; в числителе — …; знаменатель — …; период — …; потенциальное ограничение — …». Такой шаблон хорошо переносится в SQL-тренажёр, ревью и рабочий отчёт.

• Сначала реши, что является единицей результата.
• Проверь граничные значения и пустые наборы.
• Сравни результат с ручным примером.
• Сохрани запрос рядом с определением метрики.
• Назови, что запрос не умеет доказать.

Вывод

CASE удобен для переносимого SQL и сложных ветвлений. FILTER делает PostgreSQL-запрос компактнее, когда у каждого агрегата своё условие. В обоих случаях сначала фиксируй базовую когорту и зерно, затем считай показатели.

Главный навык здесь — не выучить оператор, а увидеть уровень данных, на котором он работает. Когда вопрос, зерно и период зафиксированы, SQL становится воспроизводимым способом принять решение, а не набором случайных конструкций.