Criação de Crosstabs no PostgreSQL

Você já criou uma tabela cruzada, ou crosstab, utilizando SQL? Você sabia que era possível? Possivelmente você nunca precisou, mas criar crosstabs é um recurso à disposição que pode ser bastante útil!

Tabelas cruzadas apresentam mais de uma dimensão de forma integrada aos usuários, sendo uma forma importante para a melhor visualização de dados consolidados. Geralmente o cruzamento de dados neste tipo de tabela é feito na camada de apresentação das aplicações ou em componentes geradores de relatórios, mas isso não impede que você também possa implementar crosstabs via banco de dados.

Vamos exemplificar a criação de crosstabs no postgresql utilizando as tabelas abaixo:

* Tabela Loja - Basicamente apresenta a descrição da loja

-- Tabela Loja
CREATE TABLE loja (codigo integer, nomeloja varchar(20), observacao varchar(250));
INSERT INTO loja VALUES (1, 'Loja 1', 'Matriz');
INSERT INTO loja VALUES (2, 'Loja 2', 'Filial 1');
INSERT INTO loja VALUES (3, 'Loja 3', 'Filial 2');
INSERT INTO loja VALUES (4, 'Loja 4', 'Filial 3');

* Tabela Vendas - Apresenta as vendas realizadas para cada mês em cada loja.

-- Tabela Vendas

CREATE TABLE vendas (seqvenda integer, codloja integer, mesvenda integer, valor integer);
INSERT INTO vendas VALUES (1, 1, 1, 100);
INSERT INTO vendas VALUES (2, 1, 2, 100);
INSERT INTO vendas VALUES (3, 1, 3, 100);
INSERT INTO vendas VALUES (4, 1, 1, 100);
INSERT INTO vendas VALUES (5, 2, 2, 100);
INSERT INTO vendas VALUES (6, 2, 3, 100);
INSERT INTO vendas VALUES (7, 2, 1, 100);
INSERT INTO vendas VALUES (8, 2, 2, 100);
INSERT INTO vendas VALUES (9, 4, 3, 100);
INSERT INTO vendas VALUES (10, 4, 1, 100);
INSERT INTO vendas VALUES (11, 4, 2, 100);
INSERT INTO vendas VALUES (12, 4, 3, 100);

Vamos fazer consultas para mostrar as vendas com base em duas dimensões: a loja que fez a vendas e o período (mês) das vendas.

Em primeiro lugar, vamos colocar como colunas as lojas da tabela pai (loja), e como linhas as vendas da tabela filha, agrupando os dados por mês. A cláusula CASE é importante para atribuir o valor 0 (zero) quando a venda não for da loja a ser apresentada na coluna.

-- CROSSTAB
-- COLUNAS - LOJAS
-- LINHAS - Vendas em cada Mês
SELECT
(CASE vendas.mesvenda WHEN 1 THEN 'JAN' WHEN 2 THEN 'FEV' WHEN 3 THEN 'MAR' ELSE 'ERRO' END) AS MES,
SUM (CASE vendas.codloja WHEN 1 THEN vendas.valor ELSE 0 END) AS MATRIZ,
SUM (CASE vendas.codloja WHEN 2 THEN vendas.valor ELSE 0 END) AS FILIAL_1,
SUM (CASE vendas.codloja WHEN 3 THEN vendas.valor ELSE 0 END) AS FILIAL_2,
SUM (CASE vendas.codloja WHEN 4 THEN vendas.valor ELSE 0 END) AS FILIAL_3
FROM loja
INNER JOIN vendas
ON loja.codigo = vendas.codloja
GROUP BY vendas.mesvenda
ORDER BY vendas.mesvenda;

O resultado da consulta mostra inclusive a filial 2, que esteve fechada durante o período:

 mes | matriz | filial_1 | filial_2 | filial_3
-----+--------+----------+----------+----------
 JAN |    200 |      100 |        0 |      100
 FEV |    100 |      200 |        0 |      100
 MAR |    100 |      100 |        0 |      200
(3 registros)

Agora vamos fazer uma inversão: vamos colocar como colunas as lojas da tabela filha, vendas, e como linhas as informações da tabela pai, loja, com os dados agrupados por loja. A cláusula CASE é importante para atribuir o valor 0 (zero) quando a venda não for do mês a ser apresentado na coluna.

-- CROSSTAB 2
-- COLUNAS - Vendas em cada Mês
-- LINHAS - LOJAS
SELECT
(CASE loja.codigo WHEN 1 THEN 'MATRIZ' WHEN 2 THEN 'FILIAL 1' WHEN 3 THEN 'FILIAL 2' WHEN 4 THEN 'FILIAL 3' ELSE 'ERRO' END) AS LOJA,
SUM (CASE vendas.mesvenda WHEN 1 THEN vendas.valor ELSE 0 END) AS JAN,
SUM (CASE vendas.mesvenda WHEN 2 THEN vendas.valor ELSE 0 END) AS FEV,
SUM (CASE vendas.mesvenda WHEN 3 THEN vendas.valor ELSE 0 END) AS MAR
FROM loja
INNER JOIN vendas
ON loja.codigo = vendas.codloja
GROUP BY loja.codigo
ORDER BY LOJA;

Resultado da consulta:

   loja   | jan | fev | mar
----------+-----+-----+-----
 FILIAL 1 | 100 | 200 | 100
 FILIAL 3 | 100 | 100 | 200
 MATRIZ   | 200 | 100 | 100
(3 registros)

O exemplo acima pode ser incrementado e melhorado de várias formas (por exemplo, as tabelas não têm índices). Se você tem alguma sugestão ou forma alternativa de fazer crosstabs ou de melhorar os exemplos abaixo, não deixe de postar um comentário neste post!

Obs.: Existe um módulo do postgresql já bastante estável, chamado tablefunc que apresenta funções para a criação de tabelas cruzadas. É uma forma de criar crosstabs com menos trabalho, mas também com menos diversão e portabilidade! Mais informações sobre o tablefunc aqui.