Começando aqui? Esta lição é parte de um tutorial completo sobre o uso de SQL para análise de dados. Verifique o início.

Nesta lição, cobriremos:

• Dinamização de linhas em colunas
• Dinâmica de colunas em linhas
• O que vem a seguir?

Dinamizando linhas em colunas

Esta lição irá ensiná-lo a pegar dados formatados para análise e dinamizá-los para apresentação ou gráficos . Vamos pegar um conjunto de dados parecido com este:

E fazer com que tenha a seguinte aparência:

Para este exemplo, usaremos o mesmo conjunto de dados de jogadores de futebol americano universitário usado na lição CASE. Você pode visualizar os dados diretamente aqui.

Vamos começar agregando os dados para mostrar o número de jogadores de cada ano em cada conferência, semelhante ao primeiro exemplo na lição de junção interna:

Visualize isso no Modo.

Para transformar os dados, precisaremos colocar a consulta acima em uma subconsulta. Pode ser útil criar a subconsulta e selecionar todas as colunas dela antes de começar a fazer transformações. Executar novamente a consulta em etapas incrementais como esta torna mais fácil depurar se sua consulta não for executada. Observe que você pode eliminar a cláusula ORDER BY da subconsulta, pois iremos reordenar os resultados na consulta externa.

Presumindo que funcione conforme planejado (os resultados devem ser exatamente iguais aos da primeira consulta), é hora de dividir os resultados em colunas diferentes para vários anos. Cada item na instrução SELECT cria uma coluna, então você “terá que criar uma coluna separada para cada ano:

Tecnicamente, você agora cumpriu a meta de este tutorial. Mas isso ainda poderia ser melhorado. Você notará que a consulta acima produz uma lista ordenada alfabeticamente por Conferência. Pode fazer mais sentido adicionar uma coluna “total de jogadores” e ordená-la (do maior para o menor):

E pronto! Visualize isso no Modo.

Colunas dinâmicas para linhas

Muitos dados que você encontrará na Internet são formatados para consumo, não para análise. Veja, por exemplo, esta tabela que mostra o número de terremotos em todo o mundo de 2000 a 2012:

Nesse formato, é difícil responder a perguntas como “o que “é a magnitude média de um terremoto?” Seria muito mais fácil se os dados fossem exibidos em 3 colunas: “magnitude”, “ano” e “número de terremotos”. Veja como transformar os dados nesse formato:

Primeiro, verifique esses dados no Modo:

Observação: coluna nomes começam com “ano_” porque o Modo exige que os nomes das colunas comecem com letras.

A primeira coisa a fazer aqui é criar uma tabela que lista todas as colunas da tabela original como linhas em uma nova tabela . A menos que você tenha uma tonelada de colunas para transformar, a maneira mais fácil geralmente é listá-las em uma subconsulta:

Assim que tiver isso , você pode fazer a junção cruzada com a tabela worldwide_earthquakes para criar uma visualização expandida:

Observe que cada linha no worldwide_earthquakes é replicado 13 vezes. A última coisa a fazer é corrigir isso usando uma instrução CASE que extrai os dados da coluna correta na tabela worldwide_earthquakes dado o valor em a coluna year:

Veja o produto final no Modo.

Parabéns por terminar o Tutorial de SQL avançado! Agora que você já conhece o SQL, a próxima etapa é aprimorar seu processo analítico.

Construímos a seção SQL Analytics Training para esse propósito. Com conjuntos de dados falsos para imitar situações do mundo real, você pode abordar esta seção como um treinamento no trabalho. Confira!