В практическом отчёте нужно посчитать общее число заказов и отдельно число заказов на сумму от 100, не теря...

WITH sales(region, amount) AS (
    VALUES
        ('Север', 120),
        ('Север', 80),
        ('Юг', 40),
        ('Юг', 60)
)
SELECT
    region,
    COUNT(*) AS orders_total,
    SUM(CASE WHEN amount >= 100 THEN 1 ELSE 0 END) AS large_orders
FROM sales
GROUP BY region;

Краткий ответ

Условие внутри SUM(CASE ...) выполняется для каждой строки, поэтому оно считает только подходящие заказы, но не удаляет остальные строки из входа. Результат будет: для «Севера» — 2 заказа всего и 1 заказ на сумму от 100; для «Юга» — 2 и 0 соответственно.

Если перенести условие в WHERE, строки с меньшими суммами исчезнут до агрегации. Это изменит не только условный счётчик, но и набор групп, а также значение других агрегатов.

Исторический контекст

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

Условная агрегация решает эту задачу без нескольких отдельных запросов и последующего соединения их результатов. В стандартно переносимом варианте условие выражают через CASE, хотя некоторые СУБД также поддерживают синтаксис FILTER.

Постановка проблемы

В запросе нужно одновременно сохранить все строки группы и посчитать показатель только для части из них. Например, для каждого региона требуется показать общее число заказов и число крупных заказов.

Фильтрация через WHERE выполняется до GROUP BY. Поэтому она удаляет неподходящие строки из источника целиком: они перестают участвовать в COUNT(*), SUM, других агрегатах и даже могут привести к исчезновению группы из результата.

Подробное решение

В выражении SUM(CASE WHEN amount >= 100 THEN 1 ELSE 0 END) СУБД обрабатывает каждую строку группы. Подходящая строка превращается в 1, неподходящая — в 0; сумма этих значений и есть количество крупных заказов.

COUNT(*) при этом видит все строки группы, поэтому для «Севера» результат равен 2 и 1, а для «Юга» — 2 и 0. Указание ELSE 0 важно: без него неподходящие строки дали бы NULL, а SUM игнорировал бы их, что в данном случае всё равно позволило бы посчитать подходящие строки, но поведение при отсутствии подходящих значений потребовало бы отдельной обработки NULL.

Перенос условия в WHERE выглядел бы так:

SELECT region, COUNT(*) AS large_orders
FROM sales
WHERE amount >= 100
GROUP BY region;

Такой запрос исключит «Юг» полностью, потому что после фильтрации в нём не останется строк. Условную агрегацию следует выбирать, когда разные показатели должны вычисляться по одному и тому же набору групп; WHERE подходит, когда нужно ограничить сам входной набор данных.

В СУБД с поддержкой FILTER условие можно записать яснее: COUNT(*) FILTER (WHERE amount >= 100). Однако переносимость CASE обычно выше, поэтому он часто предпочтительнее в SQL-коде, рассчитанном на разные системы.

Ситуация из практики

В отчёте по регионам требовались показатели общего числа заказов, числа заказов с оплатой картой и числа заказов дороже 100. Команда сначала сделала три агрегирующих подзапроса с разными WHERE, а затем соединила их по региону.

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

Был выбран вариант с SUM(CASE ...), поскольку отчёт выполнялся в нескольких СУБД. Результат стал проще для сопровождения, а смысл каждого показателя оказался явно виден в одном списке выражений.

Что кандидаты часто упускают

1. Что произойдёт, если заменить ELSE 0 на отсутствие ветки ELSE?

   В этом случае CASE вернёт NULL для неподходящих строк, а SUM проигнорирует эти значения. Если в группе есть хотя бы один подходящий заказ, сумма всё ещё даст правильное количество подходящих заказов. Но если подходящих строк нет, SUM может вернуть NULL, а не 0; для гарантированного нуля используют COALESCE(SUM(...), 0) или оставляют ELSE 0.

2. Чем условная агрегация отличается от COUNT(CASE WHEN ... THEN 1 END)?

   Оба варианта считают подходящие строки, потому что COUNT учитывает только ненулевые значения. SUM(CASE ... ELSE 0 END) обычно нагляднее показывает арифметическую природу счётчика и явно задаёт ноль для неподходящих строк. Важно не использовать безусловный COUNT(CASE ... ELSE 0 END): ноль не является NULL, поэтому тогда будут посчитаны все строки.

3. Почему условная агрегация не заменяет HAVING?

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