re:re:actable
Урок 062 минуты чтения

Что такое FILTER в агрегатах

На этой странице
Кратко

FILTER (WHERE условие) ограничивает строки, которые видит конкретный агрегат, не затрагивая остальные столбцы запроса. Это позволяет одним проходом посчитать несколько срезов: всего заказов, оплаченных, отменённых, выручку за месяц — вместо нескольких запросов или громоздких sum(CASE WHEN … THEN 1 ELSE 0 END). FILTER описан в стандарте SQL:2003 и поддерживается PostgreSQL (с 9.4) и SQLite; в MySQL его нет — там используют CASE. Работает и с оконными агрегатами: count(*) FILTER (WHERE …) OVER (…).

Вопрос

Что такое FILTER в агрегатных функциях и чем он лучше CASE?

Пример: дашборд одним запросом

SELECT
  count(*)                                                        AS total,
  count(*) FILTER (WHERE status = 'paid')                         AS paid,
  count(*) FILTER (WHERE status = 'cancelled')                    AS cancelled,
  sum(total) FILTER (WHERE status = 'paid')                       AS revenue,
  sum(total) FILTER (WHERE status = 'paid'
                       AND created_at >= date_trunc('month', now())) AS revenue_this_month
FROM orders;

То же через CASE

SELECT
  count(*) AS total,
  sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)      AS paid,
  sum(CASE WHEN status = 'paid' THEN total END)         AS revenue
FROM orders;

Переносимо, но хуже читается, и легко ошибиться (count(CASE … ELSE 0 END) посчитает все строки — 0 не NULL).

FILTER с GROUP BY

SELECT
  date_trunc('day', created_at)::date            AS day,
  count(*) FILTER (WHERE status = 'paid')        AS paid,
  count(*) FILTER (WHERE status = 'refunded')    AS refunded,
  round(
    count(*) FILTER (WHERE status = 'refunded')::numeric
    / NULLIF(count(*) FILTER (WHERE status = 'paid'), 0) * 100, 2
  )                                               AS refund_rate_pct
FROM orders
GROUP BY 1
ORDER BY 1;

Pivot: строки в столбцы

SELECT
  product_id,
  sum(qty) FILTER (WHERE extract(quarter FROM sold_at) = 1) AS q1,
  sum(qty) FILTER (WHERE extract(quarter FROM sold_at) = 2) AS q2,
  sum(qty) FILTER (WHERE extract(quarter FROM sold_at) = 3) AS q3,
  sum(qty) FILTER (WHERE extract(quarter FROM sold_at) = 4) AS q4
FROM sales
WHERE sold_at >= '2026-01-01' AND sold_at < '2027-01-01'
GROUP BY product_id;

FILTER или WHERE

Если условие одно для всех агрегатов — это WHERE. FILTER нужен, когда у разных агрегатов разные условия.

Частые ошибки

  • count(CASE WHEN cond THEN 1 ELSE 0 END) — считает все строки.
  • FILTER для условия, которое подходит всему запросу (лучше WHERE — меньше строк и индексы).
  • Ожидать FILTER в MySQL.
  • sum(...) FILTER без COALESCE — NULL, если ни одна строка не подошла.

Что важно помнить

  • FILTER — условие для конкретного агрегата.
  • Несколько метрик за один проход по таблице.
  • Переносимая альтернатива — CASE внутри sum/count.

Мини-шпаргалка

  • agg(x) FILTER (WHERE cond).
  • count(*) FILTER (...).
  • Pivot — набор FILTER по значениям.
  • MySQL: sum(CASE WHEN cond THEN x END).

Документация

PostgreSQL: Aggregate Expressions.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам