COUNT(*) FILTER (WHERE ...): agregados condicionais em uma unica passada

Quando voce precisa de varias metricas sob condicoes diferentes em uma unica consulta — por exemplo, pedidos com status paid e, separadamente, refunded — a clausula FILTER (WHERE ...) calcula tudo em uma unica passada sobre os dados. E SQL padrao e bem mais legivel do que o velho truque do SUM(CASE WHEN ...).

O que e FILTER e por que importa

FILTER (WHERE ...) se acopla a qualquer funcao de agregacao e restringe as linhas que entram nela. O agregado so enxerga as linhas em que a condicao e verdadeira; o restante e simplesmente ignorado, como se nao fizesse parte do grupo.

SELECT
  count(*)                                  AS orders_total,
  count(*) FILTER (WHERE status = 'paid')   AS paid_orders,
  count(*) FILTER (WHERE status = 'refunded') AS refunded_orders
FROM orders;

Propriedades principais:

• O FILTER e aplicado no momento da agregacao, por cima do WHERE geral da consulta.
• Dentro de um mesmo SELECT, cada agregado pode ter seu proprio FILTER.
• A condicao e uma expressao booleana completa sobre as colunas da linha.

Varios segmentos em uma unica passada

A grande vantagem e montar um relatorio largo com uma duzia de metricas sem percorrer a tabela varias vezes e sem auto-junes. Combina naturalmente com GROUP BY.

SELECT
  u.country,
  count(*)                                          AS users_total,
  count(*) FILTER (WHERE o.id IS NOT NULL)          AS users_with_orders,
  sum(o.amount) FILTER (WHERE o.status = 'paid')    AS revenue_paid,
  avg(o.amount) FILTER (WHERE o.status = 'paid')    AS avg_paid_order
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.country;

Uma passada, um plano de execucao: o motor calcula todos os agregados ao mesmo tempo. Isso e mais rapido e mais claro do que tres consultas quase identicas coladas com JOIN.

FILTER versus CASE dentro do agregado

O mesmo resultado costumava ser escrito com CASE dentro de SUM/COUNT. Compare:

-- Old style: CASE inside the aggregate
SELECT
  count(CASE WHEN status = 'paid' THEN 1 END)     AS paid_orders,
  sum(CASE WHEN status = 'paid' THEN amount END)  AS revenue_paid
FROM orders;

-- Modern equivalent with FILTER
SELECT
  count(*)      FILTER (WHERE status = 'paid') AS paid_orders,
  sum(amount)   FILTER (WHERE status = 'paid') AS revenue_paid
FROM orders;

O FILTER ganha em legibilidade: a condicao fica separada da expressao, entao da para ver na hora o que cada metrica mede. Um detalhe com count: count(*) FILTER (WHERE ...) e count(CASE WHEN ... THEN 1 END) dao o mesmo numero, porque o count nunca conta NULL.

Combinacoes uteis:

• Participacao do segmento: count(*) FILTER (WHERE status = 'paid')::numeric / count(*).
• Varios limiares: FILTER separados para salary > 100000, salary > 200000 e assim por diante.
• Distintos com condicao: count(DISTINCT user_id) FILTER (WHERE amount > 0).

Gotcha: NULL, grupos vazios e portabilidade

Algumas armadilhas faceis de cair:

• Se nenhuma linha passa pelo FILTER, o count retorna 0, mas sum/avg/max retornam NULL, nao zero. Envolva em COALESCE quando precisar de 0.

-- High earners per department, 0 instead of NULL when none match
SELECT
  dept,
  coalesce(sum(salary) FILTER (WHERE salary > 150000), 0) AS top_payroll
FROM employees
GROUP BY dept;

• O FILTER nao pode ser combinado com funcoes de janela usando OVER na maioria dos motores: e so para agregados.
• Suporte por motor:
  • PostgreSQL (9.4+), SQLite (3.30+) e DuckDB suportam FILTER nativamente.
  • MySQL/MariaDB e versoes antigas de outros bancos nao entendem a sintaxe FILTER; recorra ao fallback SUM(CASE WHEN ...).
  • O ClickHouse segue o proprio caminho com combinadores como countIf(cond) e sumIf(col, cond).

-- Portable fallback for MySQL and older engines
SELECT
  sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)     AS paid_orders,
  sum(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS revenue_paid
FROM orders;

Resumindo: no PostgreSQL e em motores compativeis, use FILTER; e mais limpo e mais rapido para relatorios segmentados. Onde nao existir, tenha a mao o equivalente com SUM(CASE WHEN ...).