SQL Pivot: Convertendo linhas em colunas

Experimente este notebook no Databricks

Confira o e-book Por que o Data Lakehouse é o seu próximo Data Warehouse para descobrir o funcionamento interno da plataforma Databricks Lakehouse.

ATUALIZADO EM 10/11/2018

O Pivot foi introduzido pela primeira vez no Apache Spark 1.6 como um novo recurso de DataFrame que permite aos usuários rotacionar uma expressão com valor de tabela, transformando os valores exclusivos de uma coluna em colunas individuais.

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

Analisando as temperaturas de verão com Pivot

Neste verão, em Seattle, as temperaturas subiram para níveis desconfortáveis, atingindo picos de 80 e 90 graus Fahrenheit durante nove dias em julho.

Suponha que queiramos 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 as colunas e, em seguida, as altas médias mensais de cada ano em uma única linha. Dessa forma, será fácil comparar as temperaturas horizontalmente, entre meses adjacentes, e 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 um ano bastante favorável à energia.

Pivoting 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 do pivot, em outras palavras, a tabela ou subconsulta com base na qual o pivoting será realizado. No nosso caso, estamos preocupados com os anos, os meses e as altas temperaturas, então esses são os campos que aparecem na subconsulta.

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

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

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

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

Vale a pena 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 pivot seja deixada de fora da cláusula FROM, caso contrário, a consulta produziria resultados indesejados.

Especificando várias 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 várias expressões de agregação, se necessário. Novamente, com os dados meteorológicos acima, podemos listar as temperaturas máximas altas junto com as temperaturas médias altas entre junho e setembro.

No caso de várias expressões de agregação, as colunas serão o produto cartesiano dos valores da coluna de pivot e das expressões de agregação, com os nomes como _.

Agrupando colunas x colunas de pivot

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

Para combinar esta tabela com a tabela anterior de altas temperaturas diárias, podemos unir essas duas tabelas na coluna “Data”. No entanto, como vamos usar o pivot, que realiza o agrupamento nas datas, podemos simplesmente concatenar as duas tabelas usando UNION ALL. E você verá mais tarde, esta abordagem também nos fornece mais flexibilidade:

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

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

Você deve ter notado que agora temos duas linhas para cada ano, uma para as altas temperaturas e outra para as baixas temperaturas. 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 ano.

Alternativamente, em vez de ser uma coluna de agrupamento, o flag também pode servir como uma coluna de pivot. Então, agora temos duas colunas de pivot, mês 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.

O que vem a seguir

Para executar os exemplos de consulta usados neste blog, consulte os exemplos de SQL pivot neste notebook complementar.

Agradecemos aos colaboradores 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