SQL Academy · Aula

Padrões de tabela cruzada (crosstab() do PostgreSQL)

Gere verdadeiras tabelas dinâmicas com a função crosstab() da extensão tablefunc.

Padrões de tabela cruzada (crosstab() do PostgreSQL) é uma aula grátis de SQL Academy no CoddyKit. Esta é a aula 4 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de SQL Academy, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de SQL Academy inclui 4 aulas no total.

Por que usar crosstab de verdade?

Os PIVOTs com CASE exigem que você liste cada coluna de destino. Para PIVOTs realmente largos (por exemplo, uma coluna por produto), a extensão tablefunc e a função crosstab() são a ferramenta adequada.

Ativando a extensão

tablefunc acompanha o PostgreSQL contrib:

CREATE EXTENSION IF NOT EXISTS tablefunc;

Assinatura básica de crosstab

crosstab recebe uma cadeia de caracteres SQL com três colunas (row_key, category, value) e retorna row_key + uma coluna por categoria:

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders
    GROUP BY user_id, status
    ORDER BY user_id, status
  $$
) AS ct (
  user_id BIGINT,
  paid    INT,
  pending INT,
  cancelled INT
);

Por que você declara as colunas de saída

SQL tem tipagem estática — o planejador precisa conhecer as colunas de saída no momento da análise. Por isso, você especifica o esquema na cláusula AS, incluindo os tipos de dados.

crosstab com dois argumentos (com conjunto de categorias)

Para dados esparsos, forneça a lista de categorias separadamente para que os valores ausentes se tornem NULL em vez de causarem desalinhamento:

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders GROUP BY user_id, status
    ORDER BY user_id
  $$,
  $$ VALUES ('paid'), ('pending'), ('cancelled') $$
) AS ct (
  user_id BIGINT, paid INT, pending INT, cancelled INT
);

Quando CASE é melhor que crosstab

Para um conjunto pequeno e conhecido de categorias, CASE/FILTER é mais simples — sem extensão e sem armadilhas do crosstab com dois argumentos. Use crosstab quando:

Você tiver muitas categorias
As categorias forem carregadas dinamicamente
Você estiver gerando dados para um consumidor externo de PIVOT

PIVOTs dinâmicos

Para categorias desconhecidas em tempo de execução, gere o SQL na sua aplicação ou use PL/pgSQL com format() + EXECUTE.

-- Build the SQL dynamically:
SELECT string_agg(format('SUM(CASE WHEN status = %L THEN 1 END) AS %I',
                          status, status), ', ')
FROM (SELECT DISTINCT status FROM orders) s;

Transformando dados para planilhas

Relatórios para analistas geralmente precisam do formato largo. Gere-o no SQL ou simplesmente encaminhe os dados no formato longo e deixe a ferramenta de BI fazer o PIVOT.

Unpivot: o inverso

Para passar do formato largo → longo, use UNION ALL ou jsonb_each_text() do PostgreSQL:

SELECT id, key AS month, (value)::NUMERIC AS revenue
FROM monthly_wide,
     jsonb_each_text(to_jsonb(monthly_wide) - 'id');

Desempenho

crosstab() executa o SQL interno uma vez e faz o PIVOT em memória. O gargalo é o mesmo de uma consulta GROUP BY normal.

Limitações do crosstab

O PostgreSQL não tem uma palavra-chave PIVOT nativa, ao contrário do Oracle e do SQL Server. crosstab() é a solução alternativa.

Recapitulação

Para a maioria dos PIVOTs, CASE/FILTER é a solução mais clara. crosstab() é a sua ferramenta quando há muitas categorias ou quando elas são desconhecidas antecipadamente.

Verificação rápida

Qual extensão fornece a função crosstab() do PostgreSQL?

Perguntas Frequentes

A aula “Padrões de tabela cruzada (crosstab() do PostgreSQL)” é grátis?

Sim — o texto completo de “Padrões de tabela cruzada (crosstab() do PostgreSQL)” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de SQL Academy, atualize para CoddyKit PRO. O curso de SQL Academy inclui 4 aulas no total.

O que vou aprender em “Padrões de tabela cruzada (crosstab() do PostgreSQL)”?

Gere verdadeiras tabelas dinâmicas com a função crosstab() da extensão tablefunc. Você pratica SQL Academy com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar SQL Academy?

Nenhuma experiência prévia é necessária. SQL Academy no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 4 de 4.

Quanto tempo leva a aula “Padrões de tabela cruzada (crosstab() do PostgreSQL)”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de SQL Academy?

Sim. Cada aula de SQL Academy inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

UNION, INTERSECT, EXCEPT
UNION ALL em comparação com UNION (custo da remoção de duplicatas)
Expressões CASE e consultas de tabela dinâmica
Padrões de tabela cruzada (crosstab() do PostgreSQL)

← Voltar para SQL Academy