FILTER (WHERE) — условная агрегация без CASE в PostgreSQL и SQLite

Конструкция FILTER (WHERE …) применяет условие к строкам, попадающим в конкретный агрегат, — это «условный» COUNT/SUM/AVG прямо в стандартном SQL. Аналитику она заменяет привычный COUNT(CASE WHEN … THEN 1 END) и делает pivot-отчёты читаемее. Поддерживается в PostgreSQL (с 9.4) и SQLite (с 3.30), но в MySQL/MariaDB её нет — там придётся возвращаться к CASE. Работает и как обычный агрегат с GROUP BY, и в связке с оконными функциями.

Синтаксис

aggregate_function(expression) FILTER (WHERE condition)

Примеры использования

Пример 1: Статусы заказов в одной строке (pivot)

SELECT
  date_trunc('day', created_at) AS day,
  COUNT(*) AS orders_total,
  COUNT(*) FILTER (WHERE status = 'paid')     AS paid,
  COUNT(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders
GROUP BY 1
ORDER BY 1;

Результат:

day        | orders_total | paid | refunded
-----------+--------------+------+---------
2026-07-10 |          320 |  298 |       12
2026-07-11 |          341 |  305 |        9

Пример 2: Условный SUM по периоду

SELECT
  user_id,
  SUM(amount) AS revenue_total,
  SUM(amount) FILTER (WHERE created_at >= DATE '2026-07-01') AS revenue_july
FROM payments
GROUP BY user_id;

Результат:

user_id | revenue_total | revenue_july
--------+---------------+-------------
     17 |         11940 |         3998
     42 |          5997 |         1999

Пример 3: COUNT(DISTINCT …) с фильтром

SELECT
  COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'purchase') AS buyers
FROM events;

Результат:

buyers
------
    46
Порешать SQL-задачи в тренажёре →

Типичная ошибка

FILTER — часть стандарта SQL, есть в PostgreSQL (9.4+) и SQLite (3.30+), но НЕ в MySQL/MariaDB — там пиши COUNT(CASE WHEN cond THEN 1 END). Если ни одна строка не прошла фильтр, COUNT вернёт 0, а SUM/AVG — NULL (а не 0), это важно при делении. В отличие от WHERE всего запроса, FILTER сужает только свой агрегат, не отбрасывая строки для остальных.

Где спрашивают на собеседовании

Спрашивают при построении когорт и pivot-отчётов (paid/refunded/pending в одной строке). Идёт в паре с GROUP BY и оконными функциями; классический follow-up — переписать то же самое через CASE для MySQL.

FAQ: частые вопросы про FILTER (WHERE)

Поддерживает ли MySQL конструкцию FILTER (WHERE)?

Нет, MySQL и MariaDB не понимают FILTER. Там условную агрегацию делают через агрегат от CASE: COUNT(CASE WHEN status = 'paid' THEN 1 END) или SUM(CASE WHEN … THEN amount ELSE 0 END). FILTER доступен в PostgreSQL (9.4+) и SQLite (3.30+).

Чем FILTER удобнее CASE внутри агрегата?

FILTER читается как обычное условие и не требует оборачивать выражение в CASE. Главное практическое преимущество — он корректно работает с COUNT(DISTINCT x) FILTER (WHERE …), что через CASE выразить неудобно. В остальном результат тот же, что у COUNT(CASE …).

Можно ли использовать FILTER с оконными функциями?

Да, в PostgreSQL допустимо agg(...) FILTER (WHERE ...) OVER (...) — фильтр применяется к агрегату, вычисляемому как оконный. Но FILTER работает только с агрегатными функциями; к ranking-функциям вроде ROW_NUMBER() или LAG() его прицепить нельзя.

Задачи в тренажёре по теме

Вопросы с собеседований по теме

Смежные темы SQL

Агрегации · Условная логика

Другие функции SQL

COALESCE · NULLIF · CASE WHEN · STRING_AGG · COUNT · SUM OVER

Открыть SQL-тренажёр (565 задач) →