Pivotar linhas em colunas com MAX/SUM FILTER no SQL

Um pivot transforma uma tabela "longa" (muitas linhas por entidade) em uma tabela cruzada "larga", em que cada categoria vira a sua propria coluna. No PostgreSQL a maneira mais limpa de fazer isso e um agregado com a clausula FILTER (WHERE ...), que conta apenas as linhas que satisfazem uma condicao sem sair de um unico GROUP BY.

O truque central: um agregado por coluna

Suponha que cada linha de orders tem um status e queremos os totais por usuario separados por status em colunas distintas. Em vez de varias consultas, escrevemos uma:

SELECT
  user_id,
  SUM(amount) FILTER (WHERE status = 'paid')     AS paid,
  SUM(amount) FILTER (WHERE status = 'pending')   AS pending,
  SUM(amount) FILTER (WHERE status = 'cancelled') AS cancelled,
  COUNT(*) FILTER (WHERE status = 'paid')         AS paid_count
FROM orders
GROUP BY user_id;

Cada agregado so "enxerga" as linhas do grupo que passam pelo seu FILTER; o resto e ignorado. Funciona com qualquer agregado: SUM, COUNT, MAX, MIN, AVG, array_agg.

MAX FILTER para "pegar o valor pela chave"

Quando os atributos sao guardados na vertical (chave-valor), MAX(...) FILTER (...) leva o valor certo para a coluna certa. Dadas linhas no formato (user_id, kind, amount):

SELECT
  user_id,
  MAX(amount) FILTER (WHERE kind = 'deposit')    AS deposit,
  MAX(amount) FILTER (WHERE kind = 'withdrawal')  AS withdrawal
FROM tx
GROUP BY user_id;

Aqui MAX e apenas um jeito de reduzir o grupo a um unico valor; se houver exatamente uma linha por (user_id, kind), o resultado e justamente esse valor.

FILTER versus SUM(CASE WHEN)

Antes de o FILTER existir (padrao SQL, PostgreSQL 9.4+) o mesmo resultado vinha com CASE:

SELECT
  user_id,
  SUM(CASE WHEN status = 'paid' THEN amount END)      AS paid,
  SUM(CASE WHEN status = 'pending' THEN amount END)    AS pending
FROM orders
GROUP BY user_id;

Diferencas:

• FILTER le-se melhor e separa com clareza a condicao da expressao.
• COUNT se comporta de modo diferente: COUNT(*) FILTER (WHERE c) conta linhas; COUNT(CASE WHEN c THEN 1 END) tambem funciona porque CASE sem ELSE gera NULL, que o COUNT ignora. O valor depois do THEN nao importa aqui: COUNT(CASE WHEN c THEN 0 END) conta exatamente as mesmas correspondencias, pois o COUNT so ignora a linha quando a expressao inteira e NULL. A verdadeira armadilha e acrescentar ELSE: COUNT(CASE WHEN c THEN 1 ELSE 0 END) deixa de ter linhas NULL, entao conta todas. Quando precisar de um ELSE, troque o agregado por SUM(CASE WHEN c THEN 1 ELSE 0 END).
• FILTER faz parte do padrao SQL; CASE e portavel quase em todo lugar.

Uma tabela cruzada real por pais e status:

SELECT
  u.country,
  COUNT(*) FILTER (WHERE o.status = 'paid')      AS paid,
  COUNT(*) FILTER (WHERE o.status = 'cancelled')  AS cancelled
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.country
ORDER BY u.country;

crosstab() e seus limites

O PostgreSQL traz crosstab() na extensao tablefunc:

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  'SELECT user_id, status, sum(amount) FROM orders GROUP BY 1, 2 ORDER BY 1, 2',
  'SELECT DISTINCT status FROM orders ORDER BY 1'
) AS ct(user_id int, paid numeric, pending numeric, cancelled numeric);

A dor: a lista de colunas em AS ct(...) precisa ser declarada na mao de antemao; o crosstab() nao a deduz. Para um conjunto dinamico de categorias isso e incomodo. A abordagem com FILTER tambem e estatica nas colunas, mas e mais simples, dispensa extensao e combina sem atrito com JOIN, HAVING e funcoes de janela.

Pegadinhas e portabilidade

• Atencao ao NULL: se nenhuma linha passar pelo FILTER, SUM/MAX retornam NULL. Envolva com COALESCE(SUM(...) FILTER (...), 0) se quiser zero.
• O numero de colunas fica fixo no texto da consulta. Um "pivot dinamico" com categorias desconhecidas exige gerar SQL no codigo da aplicacao ou em PL/pgSQL.
• O MySQL nao tem FILTER (ate a 8.x) — la use SUM(CASE WHEN ... THEN ... END).
• O ClickHouse tambem nao tem FILTER, mas oferece o combinador -If: sumIf(amount, status = 'paid'), countIf(status = 'paid') — um equivalente compacto do pivot.

A regra e simples: para um conjunto estatico de categorias no PostgreSQL, use FILTER; e a forma mais legivel e padrao de transformar linhas em colunas em uma unica passada.