As 7 principais consultas SQL avançadas para análise de dados

Explore as consultas SQL avançadas essenciais para a análise de dados.

A Structured Query Language (Linguagem de Consulta Estruturada), ou SQL, é uma ferramenta indispensável para os analistas de dados. A maioria das pessoas aprende a linguagem com relativa rapidez e pode começar a fazer análises de dados aprofundadas depois de apenas algumas aulas. Por esse motivo, muitos analistas de dados tendem a permanecer no nível iniciante/intermediário de uso do SQL. Esse nível de compreensão permite que você "faça o trabalho", mas pode não ser a maneira mais eficiente de escrever uma consulta.

O domínio de técnicas avançadas de SQL pode melhorar muito seus recursos de análise de dados, permitindo que você se aprofunde nos conjuntos de dados e obtenha insights valiosos. Neste artigo, exploraremos sete consultas SQL avançadas que podem ser úteis para a análise de dados. Para cada exemplo, apresentaremos a consulta e seus resultados, discutiremos a sintaxe usada e forneceremos uma breve explicação de como o banco de dados calcula o resultado.

Se quiser aprimorar suas habilidades em SQL, não deixe de conferir o nosso curso SQL Reporting. Essa é uma ótima maneira de aprender e praticar funções SQL mais avançadas.

7 SQL Avançado Consultas que os analistas de dados devem conhecer

1. Agrupamento de dados por período de tempo

Neste primeiro exemplo, vamos examinar os dados da tabela sales. Esse conjunto de dados inclui um ID para cada produto, a data da transação e o valor total da transação.

Um cenário muito comum de análise de dados é pegar dados brutos como esses e agregá-los por períodos de datas específicos, como mês ou ano. Vamos fazer exatamente isso executando a seguinte consulta:

SELECT
  EXTRACT(YEAR FROM sale_date) AS year, 
  EXTRACT(MONTH FROM sale_date) AS month,
 SUM(amount) AS total_sales
FROM sales
GROUP BY year, month
ORDER BY year, month;

Essa consulta retornará os seguintes resultados:

Nessa consulta, usamos a função EXTRACT() para extrair o ano e o mês do campo sale_date. Em seguida, agrupamos os dados pelos campos de ano e mês novos e calculamos o total de vendas de cada grupo usando a função SUM().

Observe que precisamos agrupar por ano e mês para obter resultados precisos. Se tivéssemos que extrair apenas o mês, os resultados combinariam os valores de meses específicos ao longo dos anos (portanto, todos os meses de janeiro de todos os anos seriam combinados em uma linha, todos os meses de fevereiro seriam combinados, etc.). Quando agrupamos os resultados por ano e mês, os meses de anos diferentes são colocados em linhas separadas.

Em seguida, ordenamos as linhas por ano e mês para obter resultados ordenados.

Diferentes mecanismos de banco de dados geralmente têm funções diferentes e, às vezes, melhores para obter o mesmo resultado, como DATE_TRUNC() no PostgreSQL ou TRUNC() no Oracle.

2. Criar vários níveis de agrupamento usando ROLLUP

Às vezes, você pode querer agrupar dados por vários níveis em uma única consulta. Digamos que você queira adicionar o total de vendas (em todos os anos) e o total de vendas em cada ano ao exemplo anterior. Você pode adicionar linhas adicionais para o total geral e os totais de cada ano usando a palavra-chave ROLLUP.

Usando o mesmo conjunto de dados, executaremos esta consulta:

SELECT
 EXTRACT(YEAR FROM sale_date) AS year,
 EXTRACT(MONTH FROM sale_date) AS month,
 SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(year,month)
ORDER BY year, month;

Resultado:

Em nosso exemplo, ROLLUP adiciona três linhas extras: uma para o total geral de todas as vendas e duas para o total de vendas de cada ano. Em nosso conjunto de resultados, a terceira linha é o total anual de 2021: o valor na coluna year é 2021 e o valor na coluna month é NULL. A sexta linha é o total de 2022: o valor na coluna year é 2022 e o valor na coluna month é NULL. A última linha é o total de ambos os anos: ela tem NULL nas colunas year e month. Essas linhas foram adicionadas por ROLLUP. Os valores NULL nas colunas year e month indicam as linhas agregadas.

3. Classificação de dados usando Funções de Janela (Window Functions) em SQL

A classificação de dados é um requisito comum na análise avançada de dados. Alguns casos de negócios seriam classificar seus produtos de acordo com as vendas mais altas para entender quais produtos geram mais receita ou classificar as lojas de acordo com as vendas mais baixas para entender quais lojas têm o pior desempenho.

Você pode usar funções de janela como RANK() ou DENSE_RANK() para atribuir classificações a linhas com base no valor de uma coluna específica. Primeiro, vamos dar uma olhada na função RANK():

SELECT
  product_id,
  SUM(amount) AS total_sales,
  RANK() OVER(ORDER BY SUM(amount) DESC) AS rank
FROM sales
GROUP BY id;

Resultado:

Essa consulta primeiro calcula o total de vendas para cada product_id e depois as classifica em ordem decrescente. A função RANK() é uma função de janela usada para calcular a classificação. A cláusula OVER() é a sintaxe usada com funções de janela. A função RANK() atribui uma classificação exclusiva a cada linha em um conjunto de resultados, com base em uma ordenação específica. A cláusula ORDER BY na cláusula OVER() especifica a ordem em que a classificação será atribuída, com base em uma ou mais colunas. Em nosso exemplo, classificamos as linhas pelo valor total das vendas.

A mesma sintaxe pode ser usada para a função DENSE_RANK(). A diferença entre as duas está no cenário em que dois valores são iguais. Se dois valores empatarem no primeiro lugar, RANK() pulará para 3 na terceira linha, enquanto DENSE_RANK() atribuiria a terceira linha como 2.

Para obter mais informações sobre as funções de classificação, leia nosso artigo detalhado Visão geral das funções de classificação em SQL.

Você também pode filtrar as X linhas superiores usando uma expressão de tabela comum (CTE) e uma cláusula WHERE. Por exemplo, podemos executar o seguinte para extrair apenas a linha com a classificação mais baixa:

WITH sales_cte AS (
  SELECT
    product_id,
    SUM(amount) AS total_sales,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
  FROM sales
  GROUP BY id 
) 
SELECT * 
FROM sales_cte
WHERE rank = 1;

Nessa consulta, estamos definindo um CTE chamado sales_cteque fornece a classificação de cada product_id com base em suas vendas totais. Em seguida, consultamos esse CTE, extraindo apenas as linhas com classificação = 1. Isso nos permite retornar apenas a linha superior.

4. Cálculo da diferença (delta) entre linhas

Às vezes, é necessário comparar os valores de linhas consecutivas. Um exemplo seria obter as vendas de um mês anterior para comparar com as do mês atual. Ter as vendas do mês atual e as vendas do mês anterior permite calcular a diferença entre os dois valores.

Para isso, você pode usar funções de janela como LEAD() e LAG(). Desta vez, usaremos uma tabela diferente: sales_agg. Essa tabela tem as vendas agregadas para cada ano e mês.

Vamos calcular a diferença de vendas mês a mês usando LAG():

SELECT
  year, 
  month,
  total_sales,
  LAG(total_sales) OVER(ORDER BY year, month) AS previous_month,
  total_sales – LAG(total_sales) OVER(ORDER BY year, month) AS            sales_difference
FROM sales_agg
ORDER BY year, month;

Essa consulta usa a função LAG() para calcular a diferença entre as vendas do mês atual e as vendas do mês anterior:

A função LAG() é uma função de janela. Ela extrai o valor total_sales da linha anterior, ordenado por ano e mês. Estamos fazendo o alias dessa coluna como previous_month.

Em seguida, a coluna sales_difference é calculada pegando o valor total_sales de cada linha e subtraindo o valor previous_month. A primeira linha tem valores NULL, pois não há linha anterior.

Se você precisasse extrair as vendas do mês seguinte, usaria a função de janela LEAD() no lugar de LAG(). LAG() compara a linha atual com os valores anteriores; LEAD() compara a linha atual com os valores subsequentes.

Visão geral das funções de classificação em SQL fornece vários exemplos reais de uso dessa sintaxe para calcular a diferença entre duas linhas.

5. Cálculo de um total em execução

Um total em execução calcula a soma de uma sequência de números. Também é conhecido como total cumulativo ou soma cumulativa; ele adiciona cada novo valor ao total anterior.

Os totais em execução são úteis para calcular o efeito cumulativo dos pontos de dados ao longo do tempo. Por exemplo, talvez você queira calcular o número cumulativo de usuários que visitaram seu site até um determinado momento para entender o crescimento de usuários do site.

Vamos revisitar a função SUM() e ver como poderíamos usá-la para calcular uma soma contínua de vendas. Como no exemplo anterior, precisaremos usar SUM() como uma função de janela para obter os resultados desejados.

SELECT
  year,
  month,
  total_sales,
  SUM(total_sales) OVER(ORDER BY year, month) AS running_total
FROM sales_agg
ORDER BY year, month;

Esse total em execução é calculado usando a função SUM() com a cláusula OVER(). Isso adiciona o total_sales da linha atual a todas as linhas anteriores na ordem especificada.

Na segunda linha, o total em execução é calculado pela agregação do valor total_sales da primeira e da segunda linha. Em nosso exemplo, a segunda linha é de fevereiro de 2021. O valor running_total é a soma da primeira linha (para janeiro de 2021) e da segunda linha (para fevereiro de 2021).

Na terceira linha, o total acumulado é calculado pela agregação dos valores da primeira à terceira linhas. Esse mesmo padrão continua em cada linha.

Para obter mais informações sobre o cálculo de totais em execução no SQL, consulte nosso artigo O que é um total de execução de SQL e como calculá-lo?

6. Cálculo de uma média móvel

Ao observar as tendências de vendas na análise de dados, geralmente é útil usar uma média móvel em vez de cada ponto de dados individual. Uma média móvel (também conhecida como média móvel) calcula a média do valor atual e um número especificado de valores imediatamente anteriores.

Essa técnica ajuda a suavizar os dados e a identificar tendências, especialmente quando os dados têm alta volatilidade. A ideia principal é examinar como essas médias se comportam ao longo do tempo em vez de examinar o comportamento dos pontos de dados originais.

Por exemplo, talvez você precise analisar as vendas diárias de um restaurante que tem vendas altas no fim de semana, mas vendas baixas de segunda a quarta-feira. Se você traçasse cada ponto de dados individual, veria valores muito altos e muito baixos próximos uns dos outros, dificultando a visualização das tendências de longo prazo. Ao usar uma média móvel de 3 dias, você obteria a média dos últimos 3 dias, o que uniformiza os altos e baixos.

Para este exemplo, modificaremos nossa tabela sales_agg.

Agora vamos calcular uma média móvel de 3 meses das vendas:

SELECT
  year, 
  month, 
  total_sales, 
  AVG(total_sales) OVER(ORDER BY year, month ROWS BETWEEN 2 PRECEDING and CURRENT ROW) AS moving_average
FROM aales_agg
ORDER BY year, month;

Essa consulta calcula a média móvel usando a função AVG() com a cláusula OVER(). A cláusula ROWS BETWEEN especifica o intervalo de linhas a ser incluído no cálculo da média móvel - nesse caso, a linha atual e as duas linhas anteriores.

O resultado:

Como não há linhas anteriores, a média móvel da primeira linha é apenas o valor total de vendas. A média móvel da segunda linha é a média de 300 e 700. Para a terceira linha, agora temos as duas linhas anteriores, conforme definido em nossa consulta; a média móvel é calculada pela média de 300, 700 e 500. Esse padrão continua para o restante das linhas.

O que é uma média móvel e como calculá-la em SQL é um ótimo recurso para obter mais informações sobre esse tópico.

7. Contagem de elementos em categorias personalizadas usando SUM() e CASE WHEN

Você pode contar elementos em categorias personalizadas combinando SUM() com CASE WHEN. Isso é usado quando você precisa criar uma lógica comercial que não existe em seus dados. Por exemplo, talvez você queira agrupar locais específicos por regiões personalizadas e, em seguida, calcular métricas com base nessas regiões.

CASE WHEN As declarações permitem que você execute a lógica condicional nas consultas. A sintaxe é estruturada da seguinte forma:

CASE 
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
END

As condições são avaliadas de cima para baixo. A primeira condição avaliada como verdadeira determina qual resultado é retornado.

Em nosso exemplo, vamos criar um detalhamento da categoria de vendas com base nos valores de vendas:

SELECT
  SUM(CASE WHEN total_sales < 400 THEN 1 ELSE 0 END) AS low_sales,  
  SUM(CASE WHEN total_sales >= 400 AND total_sales < 700 THEN 1 ELSE 0 END) AS medium_sales,
  SUM(CASE WHEN total_sales >= 700 THEN 1 ELSE 0 END) AS high_sales
FROM sales_agg

Na consulta acima, usamos a expressão CASE WHEN para categorizar cada mês nas categorias low_sales, medium_sales ou high_sales. Quando a condição na expressão CASE WHEN é atendida, ela retorna um valor de 1; caso contrário, retorna 0.

A função SUM() é então usada para somar os 1s e 0s de cada categoria, contando efetivamente o número de meses que se enquadram em cada categoria. O resultado é uma única linha com as contagens de low_sales, medium_sales e high_sales meses.

Essa abordagem permite que você crie categorias personalizadas e conte os elementos em cada categoria com uma única consulta. A combinação de SUM() e CASE WHEN é versátil e pode ser adaptada a diferentes casos de uso.

Se quiser usar essa construção em sua consulta, o artigo Como Usar CASE WHEN com SUM() em SQL fornecerá mais detalhes.

Potencialize sua análise de dados com SQL Avançado Queries

Dominar as consultas SQL avançadas é essencial para uma análise de dados eficiente e precisa. Neste artigo, abordamos sete técnicas avançadas de SQL que podem ajudá-lo a obter insights mais profundos sobre seus dados.

Ao aprender a agrupar dados por períodos de tempo, usar ROLLUP para vários níveis de agrupamento, classificar dados com funções de janela, calcular diferenças entre linhas, calcular totais em execução e médias móveis e contar elementos em categorias personalizadas, você estará bem equipado para lidar com tarefas complexas de análise de dados.

À medida que continuar aprimorando suas habilidades em SQL, você descobrirá ainda mais maneiras de aproveitar essa poderosa ferramenta para liberar todo o potencial dos seus conjuntos de dados e promover uma melhor tomada de decisões na sua organização.

Para praticar mais e obter um nível de compreensão ainda mais profundo sobre esses tópicos, inscreva-se em nosso curso SQL Reporting. Essa é uma ótima maneira de dominar essas funções avançadas de SQL.