Pivot SQL: Convertendo Linhas em Colunas

Experimente este notebook no Databricks

Confira o ebook Por que o Data Lakehouse é o seu Próximo Data Warehouse para descobrir o funcionamento interno da Databricks Lakehouse Platform.

ATUALIZADO EM 11/10/2018

O PIVOT foi introduzido pela primeira vez no Apache Spark 1.6 como um novo recurso do DataFrame que permite aos usuários girar uma expressão do tipo tabela, transformando os valores únicos de uma coluna em colunas individuais.

A versão Apache Spark 2.4 estende essa poderosa funcionalidade de pivotação de dados para nossos usuários SQL também. Neste artigo, usando registros de temperatura em Seattle, mostraremos como podemos usar esse recurso comum de PIVOT SQL para realizar transformações complexas de dados.

Examinando as Temperaturas de Verão com PIVOT

Neste verão em Seattle, as temperaturas subiram para níveis desconfortáveis, atingindo máximas de 30-32°C por nove dias em julho.

Suponha que queremos explorar ou examinar se houve uma tendência histórica no aumento dos níveis de mercúrio. Uma maneira intuitiva de examinar e apresentar esses números é ter os meses como colunas e, em seguida, as médias mensais máximas de cada ano em uma única linha. Dessa forma, será fácil comparar as temperaturas tanto horizontalmente, entre meses adjacentes, quanto verticalmente, entre anos diferentes.

Agora que temos suporte para a sintaxe PIVOT no Spark SQL, podemos conseguir isso com a seguinte consulta SQL.

A consulta acima produzirá um resultado como:

Bem, parece que há anos bons e anos ruins. O ano de 2016 parece ter sido um ano relativamente econômico em termos de energia.

Pivotação em SQL

Vamos dar uma olhada mais de perto nesta consulta para entender como ela funciona. Primeiro, precisamos especificar a cláusula FROM, que é a entrada para o pivô, em outras palavras, a tabela ou subconsulta na qual o pivô será realizado. No nosso caso, estamos interessados nos anos, nos meses e nas temperaturas máximas, então esses são os campos que aparecem na subconsulta.

Segundo, vamos considerar outra parte importante da consulta, a cláusula PIVOT. O primeiro argumento da cláusula PIVOT é uma função de agregação e a coluna a ser agregada. Em seguida, especificamos a coluna de pivô na sub-cláusula FOR como o segundo argumento, seguido pelo operador IN contendo os valores da coluna de pivô como o último argumento.

A coluna de pivô é o ponto em torno do qual a tabela será girada, e os valores da coluna de pivô serão transpostos para colunas na tabela de saída. A cláusula IN também permite especificar um alias para cada valor de pivô, facilitando a geração de nomes de coluna mais significativos.

Uma ideia importante sobre o pivô é que ele realiza uma agregação agrupada com base em uma lista de colunas de group-by implícitas, juntamente com a coluna de pivô. As colunas de group-by implícitas são colunas da cláusula FROM que não aparecem em nenhuma função de agregação ou como coluna de pivô.

Na consulta acima, com a coluna de pivô sendo a coluna mês e a coluna de group-by implícita sendo a coluna ano, a expressão avg(temp) será agregada em cada par de valores distintos de (year, month), onde o mês é igual a um dos valores especificados da coluna de pivô. Como resultado, cada um desses valores agregados será mapeado para sua célula correspondente da linha year e coluna month.

Vale notar que, por causa desse group-by implícito, precisamos garantir que qualquer coluna que não desejamos que faça parte da saída do pivô seja omitida da cláusula FROM, caso contrário, a consulta produziria resultados indesejados.

Especificando Múltiplas Expressões de Agregação

O exemplo acima mostra apenas uma expressão de agregação sendo usada na cláusula PIVOT, enquanto, na verdade, os usuários podem especificar múltiplas expressões de agregação, se necessário. Novamente, com os dados meteorológicos acima, podemos listar as temperaturas máximas junto com as temperaturas médias entre junho e setembro.

No caso de múltiplas expressões de agregação, as colunas serão o produto cartesiano dos valores da coluna de pivô e das expressões de agregação, com os nomes como _.

Colunas de Agrupamento vs. Colunas de Pivô

Agora, suponha que queremos incluir as temperaturas baixas em nossa exploração das tendências de temperatura a partir desta tabela de temperaturas baixas diárias:

Para combinar esta tabela com a tabela anterior de temperaturas máximas diárias, poderíamos juntar essas duas tabelas pela coluna “Data”. No entanto, como vamos usar o pivô, que realiza agrupamento nas datas, podemos simplesmente concatenar as duas tabelas usando UNION ALL. E você verá mais tarde, essa abordagem também nos oferece mais flexibilidade:

Agora vamos tentar nossa consulta de pivô com a nova tabela combinada:

Como resultado, obtemos a média das temperaturas altas e a média das temperaturas baixas para cada mês dos últimos 4 anos em uma única tabela. Observe que precisamos incluir a coluna flag na consulta PIVOT, caso contrário, a expressão avg(temp) seria baseada em uma mistura de temperaturas altas e baixas.

Você pode ter notado que agora temos duas linhas para cada ano, uma para as temperaturas altas e outra para as baixas. Isso ocorre porque incluímos mais uma coluna, flag, na entrada do PIVOT, que por sua vez se torna outra coluna de agrupamento implícita, além da coluna original year.

Alternativamente, em vez de ser uma coluna de agrupamento, a flag também pode servir como uma coluna PIVOT. Assim, agora temos duas colunas PIVOT, month e flag:

Esta consulta nos apresenta um layout diferente dos mesmos dados, com uma linha para cada ano, mas duas colunas para cada mês.

Próximos Passos

Para executar os exemplos de consulta usados neste blog, consulte os exemplos de SQL PIVOT neste notebook acompanhante.

Obrigado aos contribuidores da comunidade Apache Spark por suas contribuições!

(Esta publicação no blog foi traduzida utilizando ferramentas baseadas em inteligência artificial) Publicação original