Задача: считать «только определённые» строки в группе

Допустим, у каждого клиента есть несколько заказов с разными статусами: delivered, paid, cancelled, refunded. И нужно построить отчёт: для каждого клиента — сколько заказов каждого статуса.

Через обычный GROUP BY customer_id ты получишь общий счётчик. Через GROUP BY customer_id, status — счётчики в строках разной длины (каждый статус — отдельная строка). А нужен один кортеж на клиента с четырьмя колонками-счётчиками.

Это типичный

FILTER (WHERE …) — стандартный SQL, реализован в Postgres

Синтаксис: после любой агрегатной функции можно добавить FILTER (WHERE условие). Агрегат посчитает значения только в тех строках, где условие истинно.

Каждый клиент — одна строка. По колонкам — счётчики разных статусов. Это и есть «горизонтальный отчёт», или

FILTER работает со всеми агрегатами — SUM, AVG, MIN, MAX, STRING_AGG, ARRAY_AGG, и так далее:

Альтернатива в чистом стандартном SQL: SUM(CASE WHEN …)

До добавления FILTER (он появился в SQL:2003, в Postgres 9.4) этот же приём писался через CASE. Конструкция длиннее, но работает везде, включая старые СУБД и MySQL:

Это работает потому, что CASE возвращает 1 для подходящих строк и 0 для остальных, а SUM всех 1 даёт счётчик. Эквивалентно COUNT(*) FILTER (...).

Для COUNT(col) FILTER (WHERE ...) через CASE надо вернуть NULL для не подходящих (потому что COUNT(col) пропускает NULL):

CASE WHEN ... THEN 1 END без ELSE возвращает NULL для не-delivered строк, а COUNT пропускает NULL — получаем то же, что COUNT(*) FILTER (WHERE status = 'delivered').

Сравнение: FILTER vs CASE

Правило: на Postgres пиши FILTER. На MySQL или для cross-database кода — CASE. Результат идентичен.

Pivot: длинная форма → широкая форма

Главное практическое применение FILTER — это

Расширим пример: pivot по месяцу, считаем выручку:

Pivot хорош, когда значения колонок известны заранее (5 месяцев, 4 статуса). Если категорий много или они динамические — это уже задача для приложения, а не для SQL.

FILTER с DISTINCT

FILTER сочетается с DISTINCT так же, как обычный агрегат:

Сразу три «уникальных пользователя по событию» одним запросом. Это очень полезно для funnel-аналитики.

Подводный камень: NULL внутри FILTER

FILTER (WHERE x = 'delivered') отсеивает строки, где условие не TRUE. То есть NULL и FALSE отбрасываются одинаково. Это по умолчанию правильно — но если ты ожидаешь, что NULL должен учитываться, добавь явное IS NULL в условие:

Заметь, что FILTER (WHERE birth_year = NULL) дал бы 0 — потому что любое сравнение с NULL даёт NULL, и FILTER отбросит такие строки. Только IS NULL сработает.

FILTER vs WHERE — не путать

Это распространённая ошибка новичков: пытаются заменить WHERE на FILTER или наоборот.

• WHERE отсекает строки до агрегации. После него ничего не помнит про отсечённое.
• FILTER отсекает строки внутри каждого агрегата, при этом другие агрегаты в том же запросе продолжают видеть все строки.

То есть SELECT COUNT(*), COUNT(*) FILTER (WHERE status='delivered') FROM orders даёт два разных числа в одной строке — общее число заказов и число delivered. Сделать то же через WHERE без подзапросов невозможно.

Чек-лист

• agg(...) FILTER (WHERE условие) — стандартный SQL:2003 синтаксис для conditional aggregation. В Postgres работает с 9.4.
• Полностью эквивалентно SUM(CASE WHEN ... THEN 1 ELSE 0 END) для COUNT и COUNT(CASE WHEN ... THEN col END) для COUNT(col).
• Главное применение — pivot: длинная форма → широкая форма, по одной строке на ключ группировки, по колонке на каждое значение.
• Можно сочетать с DISTINCT (COUNT(DISTINCT x) FILTER (WHERE ...)) — частый паттерн для funnel-аналитики.
• NULL внутри FILTER отсекается так же, как FALSE — используй IS NULL явно, если нужно учесть NULL.
• WHERE и FILTER — не одно и то же: WHERE режет до агрегации (на всём запросе), FILTER — внутри отдельного агрегата (остальные агрегаты в том же SELECT его не видят).