Tabelas dinâmicas SQL: Um tutorial passo a passo

Você é um analista de dados e deseja aprimorar suas habilidades? Ou talvez queira descobrir quais ferramentas são necessárias para ter sucesso nessa função? Hoje, vamos dar uma olhada em uma das ferramentas mais importantes para os analistas de dados: a tabela dinâmica SQL.

Uma tabela dinâmica SQL é uma ferramenta essencial para organizar e resumir dados rapidamente. Com apenas algumas linhas de código, ela ajuda os analistas a identificar padrões e tendências nos dados de forma rápida e eficaz. Isso é feito por meio da dinamização ou rotação das linhas e colunas da tabela e da aplicação de um cálculo agregado aos dados subjacentes.

Se você não tem experiência com a criação de relatórios SQL, confira nosso Como Criar Relatórios Básicos em SQL curso. Você aprenderá a escrever consultas complexas em SQL. Entre as muitas funções ensinadas nesse curso está a sintaxe CASE WHEN, que é essencial para a criação de tabelas dinâmicas.

Antes de começarmos a usar as tabelas dinâmicas, não se esqueça de manter nossa Folha de dicas de SQL para análise de dados por perto. Você pode usá-la para relembrar algumas das funções que discutiremos.

Como as tabelas dinâmicas SQL funcionam na análise de dados

As tabelas dinâmicas SQL são uma ferramenta útil para reorganizar, resumir e analisar rapidamente grandes quantidades de informações. Você já deve ter trabalhado com ferramentas como o Microsoft Excel e o Google Sheets; elas oferecem a funcionalidade de tabela dinâmica integrada, permitindo que você realize facilmente essas transformações.

Vamos dar um exemplo. Imagine que você tenha uma tabela que exibe os dados de streaming de música de um artista em várias plataformas de música e países.

Tabela:

A forma como esses dados estão dispostos dificulta a comparação, especialmente quando o número de países e plataformas aumenta.

Agora imagine se pudéssemos estruturar os dados de modo que os fluxos de cada país fossem divididos por plataforma e totalizados coletivamente. Assim, poderíamos responder a perguntas como "Qual plataforma é mais popular em cada país?" e "Em qual plataforma e país devemos concentrar mais esforços?" Essa nova estrutura poderia ter a seguinte aparência:

Resultado:

A tabela recém-formatada facilita muito a comparação dos fluxos do artista entre plataformas e países. Agora você pode ver rapidamente qual plataforma funciona melhor em cada país - Spotify na França e na Irlanda e SoundCloud na Alemanha. O novo formato permite análises e percepções mais rápidas, exibindo o desempenho geográfico e específico de cada plataforma sem a necessidade de vasculhar manualmente as linhas de dados.

Os ingredientes de uma tabela dinâmica SQL

Vamos primeiro detalhar os componentes da tabela dinâmica, definindo-os com um exemplo. Usaremos a mesma tabela que usamos na seção anterior para ilustrar:

• Identificador de linha: Esse é o primeiro ingrediente; é uma coluna selecionada do seu conjunto de dados para representar as linhas da tabela dinâmica (ou seja, os registros individuais). No exemplo acima, usamos a coluna platform como nosso identificador de linha, de modo que cada linha conterá os detalhes de uma plataforma.
• Identificador de coluna: O segundo ingrediente é uma coluna que é selecionada para ser a parte vertical da tabela dinâmica. Em nosso exemplo, usamos a coluna country (país) como identificador de coluna; os países serão listados horizontalmente, como cabeçalhos na parte superior da tabela de resultados.
• Agregação: Agora que temos as partes vertical e horizontal de nossa tabela, a agregação é o ingrediente que determina como os dados subjacentes no identificador de linha e coluna interagem. Funções de agregação como SUM(), AVG() e MAX() são comumente usadas. Em nosso exemplo, usamos SUM() para calcular o total de fluxos de cada plataforma em cada um dos países.
• Tabela dinâmica: A tabela dinâmica é o prato final. Ela resume os dados inseridos ao dinamizar as linhas em colunas com uma função de agregação. Ao resumir efetivamente os dados, a tabela dinâmica nos ajuda a visualizar o que estamos analisando de uma forma mais estruturada. No exemplo acima, vemos que o resultado final de nossas entradas exibe os fluxos por cada plataforma nos países listados em nossa tabela original.

Tabelas dinâmicas nativas

Dependendo do banco de dados que você estiver usando, ele poderá ter uma tabela dinâmica nativa . Essencialmente, isso significa apenas que há uma função integrada que o banco de dados usa para criar tabelas dinâmicas SQL. O SQL Server é um ótimo exemplo de um banco de dados que oferece uma função PIVOT().

Vamos criar uma tabela dinâmica usando a função PIVOT() do SQL Server para resumir dados de fluxo contínuo. Aqui estão os dados que usaremos armazenados em uma tabela chamada streams_table:

Query (Consulta):

SELECT platform, France, Ireland, Germany
FROM
(
    SELECT platform, country, streams
    FROM streams_table
) AS SourceTable
PIVOT
(
    SUM(streams)
    FOR country IN (France, Ireland, Germany)
) AS PivotTable;

Vamos detalhar essa consulta. Começaremos com a subconsulta.

1. Definir a tabela de origem

SELECT platform, country, streams
FROM streams_table

As linhas 4-6 (imediatamente após o parêntese aberto) definem o SourceTable por meio de uma subconsulta. A tabela de origem tem três colunas declaradas na instrução SELECT (platform, country e streams) que vêm da declaração inicial streams_table.

2. Reformulando os dados usando operações dinâmicas

SUM(streams)
FOR country IN (France, Ireland, Germany)

Em seguida, dentro dos parênteses da função PIVOT(), definimos nosso PivotTable aplicando a função de agregação SUM() na coluna streams. Isso soma o número de fluxos para cada plataforma. Em seguida, especificamos em qual coluna será feito o pivô: country.

Observe que os valores dos países são fornecidos sem aspas. Observe também que, no SQL Server, os nomes de coluna que contêm espaços ou caracteres especiais ou que começam com um número devem ser colocados entre colchetes. Se tivéssemos uma coluna contendo anos, ela teria a seguinte aparência:

SUM(streams)
FOR stream_year IN ([2022], [2023])

3. Seleção das colunas finais

SELECT platform, France, Ireland, Germany

Por fim, voltamos ao início da consulta e selecionamos as colunas para nossa saída. Isso inclui a coluna platform e as colunas recém-formadas para cada país.

Resultado:

Nem todas as linguagens SQL têm funções PIVOT() incorporadas. Quando essa função não está disponível em seu banco de dados, há outra técnica simples que podemos aplicar para contornar o problema. Vamos explorá-la.

Criação de pivôs SQL sem tabelas PIVOT() nativas

Conforme mencionado anteriormente, nem todos os bancos de dados - inclusive os mais conhecidos, como MySQL e PostgreSQL - têm recursos de tabela dinâmica nativos. Você deve se lembrar que abordamos a sintaxe CASE WHEN em nossa introdução. Usando essa sintaxe, podemos obter exatamente o mesmo resultado!

Como o MySQL e o PostgreSQL são bancos de dados amplamente usados, a técnica CASE WHEN é um método amplamente usado para criar tabelas dinâmicas. Vamos dar uma olhada em alguns exemplos:

Exemplo 1: Dinamização de dados em fluxo contínuo

Usaremos o mesmo exemplo para demonstrar como a abordagem CASE WHEN pode produzir os mesmos resultados:

Consulta:

SELECT platform,
    SUM(CASE WHEN country = 'France' THEN streams END) AS france_streams,
    SUM(CASE WHEN country = 'Ireland' THEN streams END) AS ireland_streams,
    SUM(CASE WHEN country = 'Germany' THEN streams END) AS germany_streams
FROM streams_table
GROUP BY platform;

As tabelas dinâmicas SQL resultantes mostram o total de fluxos por plataforma para os países listados.

• Identificador de linha: Como selecionamos a coluna platform, cada plataforma aparece como uma coluna no resultado. Você também notará que incluímos platform em nosso GROUP BY para separar nossas plataformas em grupos.
• Identificador de coluna: Para especificar os países como colunas verticais, estamos usando a estrutura CASE WHEN country = 'France'. Se o fluxo for da França, a instrução CASE WHEN retornará os fluxos. Em seguida, ele será somado com a função SUM(). Se o fluxo não for da França, a instrução CASE WHEN retornará NULL e os valores não serão contados nessa soma, e a consulta passará para a próxima CASE WHEN. Consulte nosso artigo sobre Como usar CASE WHEN em GROUP BY para obter uma explicação mais detalhada.
• Agregação: Assim como antes, usamos SUM() como a função de agregação. A lógica da nossa instrução de caso afirma que os fluxos são acumulados se o país for igual ao país fornecido; caso contrário, nada é acumulado.

Vamos usar a mesma tabela para demonstração, mas inverter os identificadores de linha e coluna e ver o que acontece. T

Consulta:

SELECT country,
    SUM(CASE WHEN platform = 'Apple Music' THEN streams END) AS apple_music_streams,
    SUM(CASE WHEN platform = 'SoundCloud' THEN streams END) AS soundcloud_streams,
    SUM(CASE WHEN platform = 'Spotify' THEN streams END) AS spotify_streams
FROM streams_table
GROUP BY country;

Resultado:

Como você pode ver, o resultado da agregação permanece o mesmo. A única mudança é a forma como os dados são exibidos:

• Identificador de linha: Desta vez, usamos country como identificador de linha, de modo que cada país aparece como uma linha horizontal no resultado. Também incluímos country na cláusula GROUP BY para garantir que os resultados sejam agrupados corretamente por país, resultando em linhas separadas para o total de dados de streaming de cada país.
• Identificador de coluna: Modificamos nossa identificação de coluna para plataforma, de modo que as plataformas aparecem como colunas verticais no resultado. Isso é feito com a opção SUM(CASE WHEN platform =...)
• Agregação: Semelhante à última consulta, os resultados numéricos são os mesmos porque usamos o mesmo método de agregação (SUM()).

Para obter mais exemplos da agregação CASE WHEN com SUM(), consulte nosso artigo How to Use CASE WHEN with SUM() in SQL.

Exemplo 2: dinamização de dados de clientes

A seguir, vamos ver como as tabelas dinâmicas podem nos ajudar a descobrir segmentos de mercado-alvo.

A tabela customer_table descreve dados de vendas de clientes com quatro colunas: customer, age_category, country e purchases:

Query:

SELECT age_category,
    SUM(CASE WHEN country = 'France' THEN purchases END) AS France,
    SUM(CASE WHEN country = 'Spain' THEN purchases END) AS Spain,
    SUM(CASE WHEN country = 'Italy' THEN purchases END) AS Italy
FROM customer_table
GROUP BY age_category;

Result:

As tabelas dinâmicas SQL resultantes mostram o total de compras por categoria de idade na França, Espanha e Itália, onde:

• O identificador de linha é a coluna age_category.
• O identificador da coluna é country, especificando France, Spain e Italy.
• A função de agregação é SUM().

Exemplo 3: Dados de operações de dinamização

Agora, vamos ver um exemplo de como o uso de tabelas dinâmicas SQL pode revelar possíveis gargalos ou falhas nas operações.

A tabela abaixo, cake_baking_datadescreve os dados da padaria usando quatro colunas: order_number, stage, order_day e mins_taken:

Tabela:

Suponha que queiramos descobrir o tempo médio gasto por cada tarefa em cada dia. Esta é a consulta que usaríamos.

Consulta:

SELECT 
    order_day,
    AVG(CASE WHEN stage = 'Mixing' THEN mins_taken END) AS avg_mixing_time,
    AVG(CASE WHEN stage = 'Baking' THEN mins_taken END) AS avg_baking_time,
    AVG(CASE WHEN stage = 'Decorating' THEN mins_taken END) AS avg_decorating_time
FROM cake_baking_data
GROUP BY order_day;

Resultado:

A tabela dinâmica resultante mostra os tempos médios por dia de pedido para misturar, assar e decorar onde:

• O identificador de linha é a coluna order_day.
• O identificador da coluna é stage, especificando mixing, baking, ou decorating.
• A função de agregação é AVG().

Como você pode ver, a solução alternativa CASE WHEN permite que você permaneça nos bancos de dados populares MySQL e PostgreSQL e ainda obtenha as mesmas tabelas dinâmicas SQL.

Uma palavra final sobre tabelas dinâmicas SQL

Para resumir, está claro por que as tabelas dinâmicas SQL são tão importantes. A capacidade de dinamizar dados é uma ferramenta importante em sua caixa de ferramentas de análise de dados. A capacidade de transformar e resumir conjuntos de dados de forma rápida e eficiente permite que você descubra tendências e faça julgamentos mais inteligentes.

Neste artigo, nos concentramos principalmente na dinamização para analistas de dados. Se a carreira de análise de dados for do seu interesse, recomendo que você leia nosso artigo 25 perguntas de entrevista sobre SQL para analistas de dados. Ele o ajudará a entender o que você precisa saber para ter sucesso nesse campo.

A melhor maneira de melhorar seu SQL é praticar, praticar e praticar! Recomendamos nosso curso Como Criar Relatórios Básicos em SQL para isso; você não só poderá praticar o que aprendemos hoje, como também aprenderá a criar relatórios significativos. Portanto, dê uma olhada e continue desenvolvendo suas habilidades em tabelas dinâmicas SQL!