Условная агрегация в SQL: SUM(CASE WHEN) и FILTER

Q: Что такое условная агрегация и зачем она нужна?

Представьте таблицу заказов orders с колонками id, user_id, status, amount, created_at. От вас хотят одну строку с четырьмя числами: всего заказов, из них оплачено, отменено и суммарная выручка по оплаченным. Наивный подход — четыре отдельных запроса с разными WHERE. Условная агрегация складывает всё это в один SELECT. Идея простая: агрегатная функция игнорирует NULL. Значит, если внутри CASE WHEN для «неподходящих» строк вернуть NULL (или 0 для суммы), они не попадут в подсчёт. Условие вычисляе

Q: Как посчитать метрики через SUM(CASE WHEN)?

SUM(CASE WHEN условие THEN значение ELSE 0 END) — рабочая лошадка условной агрегации. Внутри CASE вы описываете, какое значение прибавлять к сумме для каждой строки. Для строк, не прошедших условие, возвращаете 0 (или NULL — на сумму это не влияет). Посчитаем несколько денежных метрик за один проход:

Q: Как считать количество по условию через COUNT(CASE WHEN)?

Здесь есть ловушка, на которой спотыкаются даже опытные. COUNT считает любое не-NULL значение, включая 0 и пустую строку. Поэтому для подсчёта «сколько строк удовлетворяют условию» в ELSE нужно ставить NULL, а не 0:

Q: Как собрать воронку конверсий одним запросом?

Классическая задача с собеседований: есть таблица событий events с колонками user_id, event (view, add_to_cart, checkout, purchase) и ts. Нужна воронка — сколько уникальных пользователей дошло до каждого этапа. Через самосоединения это превращается в кошмар, а условная агрегация решает задачу в одну строку:

Коротко: условная агрегация — это когда внутрь агрегатной функции (SUM, COUNT, AVG) вкладывают условие, чтобы за один проход по таблице посчитать сразу несколько метрик по разным срезам. Вместо трёх подзапросов или пяти JOIN пишете SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) — и получаете выручку только по оплаченным заказам в отдельной колонке. В PostgreSQL для этого есть ещё более чистый синтаксис — предложение FILTER (WHERE ...). Обе техники читают таблицу один раз, поэтому работают быстрее нагромождения самосоединений.

Что такое условная агрегация и зачем она нужна?

Представьте таблицу заказов orders с колонками id, user_id, status, amount, created_at. От вас хотят одну строку с четырьмя числами: всего заказов, из них оплачено, отменено и суммарная выручка по оплаченным. Наивный подход — четыре отдельных запроса с разными WHERE. Условная агрегация складывает всё это в один SELECT.

Идея простая: агрегатная функция игнорирует NULL. Значит, если внутри CASE WHEN для «неподходящих» строк вернуть NULL (или 0 для суммы), они не попадут в подсчёт. Условие вычисляется для каждой строки, а агрегат сворачивает результат.

Где это встречается каждый день:

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

Если синтаксис CASE и GROUP BY пока плывёт, пройдите базу в SQL с нуля: агрегаты COUNT, SUM, AVG, GROUP BY и в разборе NULL, CAST и CASE. Дальше будет проще.

Как посчитать метрики через SUM(CASE WHEN)?

SUM(CASE WHEN условие THEN значение ELSE 0 END) — рабочая лошадка условной агрегации. Внутри CASE вы описываете, какое значение прибавлять к сумме для каждой строки. Для строк, не прошедших условие, возвращаете 0 (или NULL — на сумму это не влияет).

Посчитаем несколько денежных метрик за один проход:

SELECT
    SUM(amount)                                          AS revenue_total,
    SUM(CASE WHEN status = 'paid'     THEN amount ELSE 0 END) AS revenue_paid,
    SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS revenue_refunded,
    SUM(CASE WHEN created_at >= date_trunc('month', now())
             THEN amount ELSE 0 END)                     AS revenue_this_month
FROM orders;

Результат — одна строка:

revenue_total	revenue_paid	revenue_refunded	revenue_this_month
1 240 000	1 180 000	42 000	310 000

Ключевой момент — ELSE 0. Для SUM можно было бы опустить его (тогда сработает NULL, который сумма пропустит), но явный 0 делает запрос читаемее и защищает от сюрпризов, если кто-то обернёт результат в COALESCE или сравнение. Про то, как NULL ведёт себя в агрегатах, подробно в шпаргалке по SQL для аналитика.

Долю тоже считают одним выражением. Например, доля выручки от вернувшихся клиентов:

SELECT
    ROUND(
        100.0 * SUM(CASE WHEN is_returning THEN amount ELSE 0 END)
              / NULLIF(SUM(amount), 0),
    2) AS returning_revenue_share_pct
FROM orders;

NULLIF(..., 0) в знаменателе спасает от деления на ноль — если заказов нет, вернётся NULL, а не ошибка.

Как считать количество по условию через COUNT(CASE WHEN)?

Здесь есть ловушка, на которой спотыкаются даже опытные. COUNT считает любое не-NULL значение, включая 0 и пустую строку. Поэтому для подсчёта «сколько строк удовлетворяют условию» в ELSE нужно ставить NULL, а не 0:

SELECT
    COUNT(*)                                              AS orders_total,
    COUNT(CASE WHEN status = 'paid' THEN 1 END)           AS orders_paid,
    COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)    AS orders_wrong
FROM orders;

Колонка orders_paid посчитает только оплаченные (для остальных CASE вернёт NULL, и COUNT их пропустит). А orders_wrong из-за ELSE 0 посчитает вообще все строки — потому что 0 это не-NULL, и COUNT его учитывает. Это одна из 10 частых ошибок в SQL у аналитиков.

Более надёжный приём для подсчёта по условию — SUM(CASE WHEN условие THEN 1 ELSE 0 END). Тут ELSE 0 наоборот правильный, потому что суммирование единиц и есть подсчёт:

SELECT
    SUM(CASE WHEN status = 'paid'     THEN 1 ELSE 0 END) AS orders_paid,
    SUM(CASE WHEN status = 'canceled' THEN 1 ELSE 0 END) AS orders_canceled,
    COUNT(DISTINCT CASE WHEN status = 'paid'
                        THEN user_id END)                AS paying_users
FROM orders;

Последняя колонка показывает, что CASE дружит и с COUNT(DISTINCT ...) — так считают уникальных платящих пользователей за один проход. Запомните правило: для COUNT(CASE ...) — ELSE NULL (или без ELSE), для SUM(CASE ...) — ELSE 0.

Чем предложение FILTER удобнее CASE WHEN в Postgres?

PostgreSQL поддерживает синтаксис из стандарта SQL — предложение FILTER (WHERE ...) после агрегатной функции. Оно делает ровно то же, что CASE WHEN, но читается заметно чище:

SELECT
    COUNT(*)                              AS orders_total,
    COUNT(*) FILTER (WHERE status = 'paid')     AS orders_paid,
    COUNT(*) FILTER (WHERE status = 'canceled') AS orders_canceled,
    SUM(amount) FILTER (WHERE status = 'paid')  AS revenue_paid,
    AVG(amount) FILTER (WHERE status = 'paid')  AS avg_check_paid
FROM orders;

Сравните с эквивалентом на CASE WHEN — FILTER короче и не требует ручного выбора ELSE 0 vs ELSE NULL, потому что просто отбрасывает строки, не прошедшие WHERE. С COUNT, AVG, MIN, MAX это особенно приятно: не нужно думать, чем заполнять «пустые» строки.

Разница между двумя подходами:

Критерий	SUM(CASE WHEN)	FILTER (WHERE)
Где работает	везде (Postgres, MySQL, SQLite, ClickHouse-аналоги)	PostgreSQL, SQLite 3.30+, часть СУБД
Читаемость	среднее, много скобок	высокая
Риск ошибки ELSE	есть (0 vs NULL)	нет
Работа с AVG/MIN/MAX	нужен ELSE NULL	из коробки корректно

Практический вывод: если пишете под PostgreSQL — используйте FILTER, код будет чище. Если запрос должен переноситься между разными СУБД (например, часть аналитиков работает на MySQL), берите CASE WHEN — он портируемее. Попробовать оба варианта на живой базе можно в SQL-тренажёре: там доступны и PostgreSQL 16, и SQLite прямо в браузере.

Как собрать воронку конверсий одним запросом?

Классическая задача с собеседований: есть таблица событий events с колонками user_id, event (view, add_to_cart, checkout, purchase) и ts. Нужна воронка — сколько уникальных пользователей дошло до каждого этапа. Через самосоединения это превращается в кошмар, а условная агрегация решает задачу в одну строку:

SELECT
    COUNT(DISTINCT user_id) FILTER (WHERE event = 'view')        AS step_view,
    COUNT(DISTINCT user_id) FILTER (WHERE event = 'add_to_cart') AS step_cart,
    COUNT(DISTINCT user_id) FILTER (WHERE event = 'checkout')    AS step_checkout,
    COUNT(DISTINCT user_id) FILTER (WHERE event = 'purchase')    AS step_purchase
FROM events
WHERE ts >= now() - interval '30 days';

Получаем этапы воронки в соседних колонках. Дальше конверсию между этапами считают простым делением. Например, конверсия из корзины в покупку = step_purchase / step_cart * 100%. Формула сквозной конверсии: step_purchase / step_view * 100%.

На SQLite (или для переносимости) тот же запрос через CASE:

SELECT
    COUNT(DISTINCT CASE WHEN event = 'view'        THEN user_id END) AS step_view,
    COUNT(DISTINCT CASE WHEN event = 'add_to_cart' THEN user_id END) AS step_cart,
    COUNT(DISTINCT CASE WHEN event = 'checkout'    THEN user_id END) AS step_checkout,
    COUNT(DISTINCT CASE WHEN event = 'purchase'    THEN user_id END) AS step_purchase
FROM events
WHERE ts >= datetime('now', '-30 days');

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

Этап	Пользователей	Конверсия от предыдущего
view	50 000	—
add_to_cart	12 500	25.0%
checkout	6 000	48.0%
purchase	4 200	70.0%

Сквозная конверсия view → purchase = 4200 / 50000 = 8.4%. Более развёрнутый разбор с процентами прямо в SQL — в статье воронка конверсии в SQL и в гайде про расчёт conversion rate. Сам показатель конверсии удобно раскладывать по когортам — тем же приёмом считается и retention D7.

Как посчитать выручку по сегментам в одной строке?

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

SELECT
    SUM(amount) FILTER (WHERE segment = 'new')        AS revenue_new,
    SUM(amount) FILTER (WHERE segment = 'returning')  AS revenue_returning,
    SUM(amount) FILTER (WHERE platform = 'mobile')    AS revenue_mobile,
    SUM(amount) FILTER (WHERE platform = 'web')       AS revenue_web,
    ROUND(AVG(amount) FILTER (WHERE segment = 'new'), 2)       AS aov_new,
    ROUND(AVG(amount) FILTER (WHERE segment = 'returning'), 2) AS aov_returning
FROM orders
WHERE status = 'paid'
  AND created_at >= date_trunc('month', now());

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

Часто к такому отчёту добавляют группировку по времени, чтобы увидеть динамику:

SELECT
    date_trunc('week', created_at)                    AS week,
    SUM(amount) FILTER (WHERE segment = 'new')        AS revenue_new,
    SUM(amount) FILTER (WHERE segment = 'returning')  AS revenue_returning,
    COUNT(*)    FILTER (WHERE amount > 5000)          AS big_orders
FROM orders
WHERE status = 'paid'
GROUP BY 1
ORDER BY 1;

Теперь каждая строка — неделя, а колонки — сегменты выручки и число крупных заказов. Средний чек тут — это метрика AOV; если сегментируете выручку на пользователя, пригодится и ARPU. Тот же приём лежит в основе RFM-сегментации и когортного анализа retention.

Если предпочитаете считать сегменты в Python поверх выгрузки, аналог SUM(CASE WHEN) — это df.groupby с agg или булевы маски; разбор в pandas с нуля: groupby, agg, pivot_table, а поэкспериментировать можно в Python-тренажёре.

Чем условная агрегация лучше лишних JOIN и подзапросов?

Наивное решение задачи «выручка по статусам» — написать три подзапроса и склеить их:

-- Так делать не надо: три прохода по таблице
SELECT
    (SELECT SUM(amount) FROM orders WHERE status = 'paid')     AS paid,
    (SELECT SUM(amount) FROM orders WHERE status = 'refunded') AS refunded,
    (SELECT SUM(amount) FROM orders WHERE status = 'canceled') AS canceled;

Проблемы такого подхода:

Три отдельных сканирования одной таблицы вместо одного. На больших данных разница в разы.
Дублирование условий фильтрации — если поменяется период, править надо в трёх местах.
Плохо масштабируется: пять статусов — пять подзапросов.
Легко рассинхронизировать фильтры между подзапросами и получить несопоставимые числа.

Условная агрегация делает то же за один проход:

SELECT
    SUM(amount) FILTER (WHERE status = 'paid')     AS paid,
    SUM(amount) FILTER (WHERE status = 'refunded') AS refunded,
    SUM(amount) FILTER (WHERE status = 'canceled') AS canceled
FROM orders;

Один SELECT, одно чтение таблицы, общий WHERE для всех метрик. Если хотите убедиться, что план запроса действительно эффективнее — прогоните оба варианта через EXPLAIN ANALYZE; как читать его вывод, разобрано в гайде по EXPLAIN ANALYZE.

Есть нюанс: условная агрегация не заменяет JOIN, когда данные реально лежат в разных таблицах. Она заменяет лишние самосоединения и повторные сканирования одной таблицы. Если нужно связать orders с users, JOIN остаётся — но метрики поверх соединённого набора всё равно считайте через FILTER/CASE, а не плодите подзапросы. Разбор всех типов соединений — в SQL JOIN: все типы с примерами.

Где условная агрегация проигрывает PIVOT?

Условная агрегация — это ручной PIVOT: вы вручную перечисляете каждую колонку-срез. Пока срезов немного и они известны заранее (статусы, платформы, этапы воронки) — это идеально: явно, читаемо, под контролем.

Слабые места начинаются, когда:

Категорий много и они динамические. Если у вас 200 товарных категорий, писать 200 строк FILTER (WHERE category = '...') невозможно. Тут нужен либо генерируемый SQL, либо инструмент с настоящим PIVOT, либо разворот на стороне BI/Python.
Список значений заранее неизвестен. Условная агрегация требует захардкодить каждое значение в отдельную колонку. Появился новый статус — запрос его молча пропустит, пока вы не добавите колонку вручную.
Нужна двумерная сводная таблица (строки × столбцы). Формально это делается, но становится громоздким.

В PostgreSQL для настоящего разворота есть функция crosstab из расширения tablefunc, а в BI-инструментах — встроенный pivot. Когда категорий много и они меняются, лучше держать данные «в длину» (long format) и разворачивать на уровне отчёта. Сравнение подходов и синтаксис разворота — в статье SQL PIVOT: строки в столбцы.

Простое правило выбора:

Мало известных срезов, нужна одна строка метрик → SUM(CASE WHEN) или FILTER.
Много динамических категорий, двумерная сводка → PIVOT / crosstab / разворот в BI или pandas.

Если хотите закрепить всё это на практике — в SQL-тренажёре есть задачи на воронки, сегментацию выручки и условную агрегацию: 5 задач открыты бесплатно, дальше доступ по Pro. Можно писать запросы на настоящем PostgreSQL 16 в браузере и сразу видеть, сошёлся ли результат. Хочется системно с азов — начните с курса по SQL. А перед собеседованием прогоните себя через реальные SQL-вопросы и AI мок-собеседование, где условная агрегация всплывает почти всегда.

Что запомнить про условную агрегацию

Условная агрегация считает несколько метрик за один проход по таблице, вкладывая условие внутрь SUM, COUNT, AVG.
Для SUM(CASE WHEN ...) пишите ELSE 0; для COUNT(CASE WHEN ...) — ELSE NULL или вовсе без ELSE, иначе посчитаете все строки.
В PostgreSQL предпочитайте FILTER (WHERE ...) — чище и без ошибок с ELSE.
FILTER и CASE дружат с GROUP BY, COUNT(DISTINCT ...), AVG, MIN, MAX.
Это замена лишним самосоединениям и повторным сканированиям, но не замена JOIN между разными таблицами.
Когда категорий много и они динамические — берите PIVOT или разворот в BI/pandas.

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