Tabelas Pivot no PostgreSQL usando a função Crosstab

Alguns anos atrás, quando a versão 8.3 do PostgreSQL foi lançada, uma nova extensão chamada tablefunc foi introduzida. Esta extensão fornece um conjunto realmente interessante de funções. Uma delas é a função crosstab, que é utilizada para a criação de tabelas pivot. Isso é o que vamos cobrir neste artigo.

A maneira mais simples de explicar como esta função funciona é usando um exemplo com uma tabela pivô. Primeiro, explicaremos nosso ponto inicial de uma perspectiva prática, depois definiremos a tabela pivô que queremos.

Nosso ponto inicial: Dados brutos

Ao ler este artigo, imagine-se como um professor em uma escola primária (elementar). Assumiremos que você ensina cada disciplina (língua, música, etc.). A escola fornece um sistema para que você grave todos os resultados de avaliação ou teste. A seguinte instrução SQL mostraria os resultados da avaliação que você carregou anteriormente no sistema:

SELECT *
FROM evaluations

Nosso alvo: uma Tabela Pivot

A seguinte grade pode facilmente acompanhar o progresso de seus alunos. Na ciência da computação, chamamos este tipo de grade de uma tabela pivô. Se você analisar como a tabela pivô é construída, verá que usamos valores de dados brutos como cabeçalhos de coluna ou nomes de campo (neste caso, geografia, história, matemática, etc.)

Habilitação da função de tabela cruzada

Como mencionado anteriormente, a função crosstab é parte de uma extensão do PostgreSQL chamada tablefunc. Para chamar a função de tabulação cruzada, é necessário primeiro ativar a extensão tablefunc executando o seguinte comando SQL:

CREATE extension tablefunc;

Como funciona a função de tabulação cruzada

A função crosstab recebe um comando SQL SELECT como parâmetro, que deve estar em conformidade com as seguintes restrições:

• O SELECT deve retornar 3 colunas.
• A primeira coluna do SELECT será o identificador de cada linha da tabela pivô ou resultado final. Em nosso exemplo, este é o nome do aluno. Observe como os nomes dos estudantes (John Smith e Peter Gabriel) aparecem na primeira coluna.
• A segunda coluna do SELECT representa as categorias na tabela pivô. Em nosso exemplo, estas categorias são as matérias escolares. É importante notar que os valores desta coluna se expandirão em muitas colunas da tabela pivô. Se a segunda coluna retornar cinco valores diferentes (geografia, história, etc.), a tabela pivô terá cinco colunas.
• A terceira coluna do SELECT representa o valor a ser atribuído a cada célula da tabela pivô. Estes são os resultados da avaliação em nosso exemplo.

Se pensarmos em nossa tabela dinâmica como uma matriz bidimensional, então a primeira coluna SELECT é a primeira dimensão da matriz, a segunda coluna SELECT é a segunda dimensão, e a terceira é o valor do elemento da matriz .como grade [valor_da_primeira_coluna, valor_da_segunda_coluna] = valor_da_terceira_coluna.

Em nosso exemplo, o parâmetro SELECT será:

SELECT student, subject, evaluation_result FROM evaluations ORDER BY 1,2

A função de tabulação cruzada é invocada na cláusula da instrução SELECT FROM. Devemos definir os nomes das colunas e os tipos de dados que irão para o resultado final. Para nossos propósitos, o resultado final é definido como:

AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC)

Juntando todas estas peças, nossa consulta final será:

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations order by 1,2') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

E podemos ver o resultado aqui:

Um conjunto de dados brutos, muitas tabelas pivô

A partir de um único conjunto de dados, podemos produzir muitas tabelas pivô diferentes. Vamos continuar com o exemplo do professor e da classe, enquanto olhamos para algumas de nossas opções.

Exemplo 1: Médias de Avaliação Mensal

Como professores, podemos também precisar de um relatório para os resultados da avaliação de um estudante para o ano até o momento. Por exemplo, suponha que queremos obter as avaliações médias para John Smith de março a julho. Em uma grade como a seguinte, a tabela ficaria assim:

O SQL para esta tabela pivô é:

SELECT * 
FROM crosstab( 'select extract(month from period)::text, subject.name,
		     trunc(avg(evaluation_result),2) 
     from evaluation, subject  
     where evaluation.subject_id = subject.subject_id and student_id = 1 
     group by 1,2 order by 1,2')  AS final_result(Month TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Por favor, compare esta saída com a tabela anterior:

Exemplo 2: Como encontrar registros incompletos de alunos

Poderíamos também chamar esta seção de "Uma Limitação do Crosstab e Como Consertá-la". Antes de entrarmos nisso, vamos montar o cenário:

Suponha que você queira ver se alguns estudantes não têm uma pontuação de avaliação para certas matérias. Talvez você tente nossa consulta anterior, acrescentando uma cláusula WHERE para julho. O código ficaria assim:

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations where extract (month from evaluation_day) = 7 order by 1,2') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

A seguinte tabela pivô é o resultado desta consulta. Podemos ver rapidamente que não temos nenhuma nota para idioma, matemática e música para Peter.

Entretanto, se tentarmos uma consulta regular para obter as notas de Peter em julho ...

SELECT * from evaluations 
where extract ( month from evaluation_day)=7 and student like 'Gabriel, Peter'

... obtemos resultados diferentes. Aqui temos notas para a geografia e idioma:

Naturalmente, a segunda consulta é a correta, pois mostra dados brutos. O problema está no processo de construção da tabela pivot - faltam informações sobre algumas categorias. Para corrigir isso, podemos usar a função de tabulação cruzada com um segundo parâmetro, que representa a lista completa de categorias. Se houver valores faltando, a tabela pivô ainda será construída corretamente. (Destaquei a consulta do segundo parâmetro em vermelho).

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations 
                where extract (month from evaluation_day) = 7 order by 1,2',
                'select name from subject order by 1') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Agora temos uma tabela pivô correta com valores vazios nos lugares apropriados.

Exercício

As tabelas pivotantes nos dão uma maneira diferente de ver nossos dados. Além disso, podemos criar diferentes tabelas pivô com base nos mesmos dados brutos, usando a função de tabulação cruzada. Tente construir uma tabela pivô que mostre a temperatura máxima para cada cidade e mês com base nos dados brutos da tabela abaixo.

CREATE TABLE weather (city text, when timestamp, temperature float);

A tabela pivô deve ter uma linha para cada cidade e uma coluna para cada mês. Se desejar, você pode pensar em outras tabelas dinâmicas que poderiam ser feitas usando os mesmos dados.