Parte 2: Comandos essenciais em SQL para análise de dados

Imagem de @maks_lab no Freepik

Link para a parte 1:

Parte 1: Comandos básicos em SQL para análise de dados

Essa é a segunda parte de uma lista de instruções em SQL que são muito úteis para análise de dados e negócios.

1. GROUP BY

Essa instrução permite agrupar linhas com base em uma ou mais colunas. O uso mais frequente é associado a funções de agregação SUM, COUNT, MIN, MAX e AVG. Trata-se de uma ferramenta útil para realizar análises agregadas e segmentadas nos dados.

SELECT type
,COUNT(order_id) as pedidos_feitos

FROM orders

GROUP BY 1
;

Nessa situação de uso, a colunatypefoi agrupada utilizando o número 1. Outras exemplos de uso do GROUP BY:

a) Desduplicação de dados

Embora não seja intuitivo, é possível utilizar a instrução da mesma forma que o DISTINCT, removendo duplicidades. As vezes esses dados nem se tratam de duplicatas e podem conter vários registros por entidade:

SELECT a.consumer_id
,a.consumer_phone

FROM consumers a
JOIN transactions b ON a.consumer_id = b.consumer_id
GROUP BY 1,2
;

b) Segmentar dados usando valores específicos

Nesse exemplo, o número de pedidos foi segmentado pelo país.

SELECT country
,COUNT(consumer_id)

FROM orders
GROUP BY 1
;

2. ORDER BY

Este comando é usado para classificar os resultados da consulta, utilizando uma ou mais colunas, seja de forma ascendente ou descendente.

Essa ordenação pode ser em uma coluna numérica ou em uma coluna de texto.

a) Ordenação crescente dos pedidos pelo total pago pelo cliente

SELECT order_id
,paid
FROM orders
ORDER BY paid ASC
;

b) Ordenação de resultados de uma função de agregação

SELECT type
,AVG(price) as media_preco
FROM products
GROUP BY type
ORDER BY media_preco DESC
;

3. HAVING

O HAVING é um comando usado para filtrar resultados de uma consulta que envolve a utilização de uma agregação usando o GROUP BY. Ela permite o filtro de resultados com base em alguma caraterística do grupo agregado.

No exemplo, são filtrados os pedidos com valor superior a 500.

SELECT order_id
,SUM(paid) AS valor_venda
FROM orders
GROUP BY 1
HAVING SUM(paid)>500
;

3. Subqueries

As subqueries são uma das maneiras de realizar cálculos que não possam ser executados em uma única consulta principal. Uma outra utilidade das subqueries é a organização de consultas longas em blocos menores.

Elas podem ser usadas em várias instruções como WHERE, FROM, SELECT e HAVING.

Alguns exemplos interessantes utilizando subqueries:

a) Distribuição de pedidos por cliente

Dado uma tabela hipótetica de pedidos orders , uma maneira de ver a distribuição de pedidos por cliente, é realizando a seguinte subquery:

SELECT orders
,COUNT(*) AS qtd_consumers
FROM 
(
   SELECT consumer_id,
          ,COUNT(order_id) as orders
   FROM orders
   GROUP BY 1
)a
GROUP BY 1
;

A subquery realiza uma etapa de agregação intermediária, onde, primeiro se conta o número de pedidos realizados por cada consumer_id e a consulta externa utiliza o número de orders como categoria e conta o número de clientes.

b) Seleção de público

A subconsulta a seguir é bastante útil em campanhas de marketing, onde você quer garantir que o público contido na tabela publico_antigo não seja novamente acionado.

SELECT *
FROM publico_novo a
WHERE NOT EXISTS (
                  SELECT *
                  FROM publico_antigo b
                  WHERE A.consumer_id = B.consumer_id
                  )

c) Subquery na cláusula HAVING

SELECT type
,AVG(price) AS media_preco
FROM products
GROUP BY 1
HAVING AVG(price) > (SELECT AVG(price) FROM products)
;

4. Tabelas temporárias

As tabelas temporárias são criadas de forma muito semelhante a qualquer outra tabela, porém só persiste durante a sessão atual. Elas são muito úteis para:

• Armazenar resultados intermediários: há situações onde a quebra em resultados intermediários simplifica consultas muito complexas;
• Permitir que um dado seja consultado várias vezes numa mesma sessão;
• Tornar o código SQL mais legível e mais fácil de identificar possíveis erros.

Aqui temos um exemplo de utilização de uma tabela temporária onde têm-se o armazenamento de dados intermediários.

CREATE temporary TABLE temp_vendas AS
SELECT consumer_id
,order_id
,created_at
,SUM(paid) AS total_pago

FROM orders 
;

5. CTEs

As Common Table Expression ou CTEs é uma das ferramentas que eu mais gosto e uso em estudos e análises mais complexas. Trata-se de uma subconsulta escrita antes da consulta principal e sua execução também se dá antes.

Uma consulta pode conter vários CTEs, que podem fazer uso dos resultados dos CTEs anteriores para realização de operações. Esse tipo de ferramenta é muito útil nas seguintes situações:

• Quando o resultado será usado várias vezes no resto da consulta — realização de cálculos intermediários;
• Simplificação de consultas complexas;
• Substituir subqueries;
• Organizar o código SQL.

Uma consulta pode conter vários CTEs separados por vírgulas, conforme o exemplo abaixo:

WITH primeiro_cte as 
(
    SELECT ...
)
,segundo_cte as 
(
    SELECT ...
)
,terceiro_cte as 
(
    SELECT ...
)
SELECT ...
;

E um uso prático para um CTE que realiza um cálculo intermediário, onde se calcula as vendas por cliente

WITH vendas_totais AS (
    SELECT consumer_id
    ,SUM(paid) AS total
    FROM orders
    GROUP BY cliente_id
)
SELECT a.consumer_id
,a.
,b.total
FROM clientes a
LEFT JOIN vendas_totais b ON a.consumer_id = b.consumer_id
;

Bônus: Window Functions

Na sessão bônus vamos ver algumas funções de janela que não são parte do arroz-com-feijão, mas vão auxiliar em tarefas cuja realização se tornaria muito difícil sem as mesmas.

As funções de janela, são funções que permitem realizar operações num conjunto de linhas da consulta. São costumeiramente utilizadas para: classificar valores, cálculo de valores acumulados, cálculo de médias móveis e outros cálculos ou agregações mais avançados.

Algumas funções de janela:

• SUM( ): cálculo de soma;
• AVG( ): cálculo de média;
• ROW_NUMBER: atribuição de um número para cada linha da partição;
• RANK( ): atribuição de um ranking;

a)ROW_NUMBER()

Como já dito anteriormente, é uma função que permite a atribuição de um número único e sequencial a cada uma das linhas de uma consulta, ordenando de acordo com o que foi escrito no comando ORDER BY.

É bastante útil em situações em que seja necessário criar classificações. No exemplo abaixo, queremos selecionar a última transação realizada pelos clientes de um produto financeiro:

WITH ultima_transacao AS (
    SELECT
        user_id
        ,created_at AS data_transacao
        ,amount
        
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rownumber
    FROM transactions
)
SELECT
    user_id
    ,data_transacao
    ,amount
FROM ultima_transacao
WHERE rownumber = 1
;

• O ROW_NUMBER( ) cria uma identificação numérica única para cada uma das transações realizadas;
• Em seguida, o PARTITION BY divide o conjunto de linhas em grupos menores;
• Por fim, o ORDER BY realiza a ordenação pela de data created_at , trazendo os valores em ordem decrescente.

b) Gerando valores acumulados com as funções SUM( ) ou COUNT( )

Nesse exemplo, temos o acumulado de inscrições por data de um curso hipotético.

SELECT
    user_id
   ,created_at AS data_inscricao
    COUNT(*) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS resultado_acumulado
FROM inscricoes
ORDER BY created_at
;

• A função de janela COUNT(*) realiza a contagem do número de linhas na janela;
• A ordenação pela data de inscrição, permite que as linhas sejam contadas por ordem cronológica;
• Já ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW garante que a janela inclua todas as linhas, do início até a linha atual.

Isso é tudo pessoal!!!

Espero muito que esse texto possa enriquecer suas análises e, de alguma forma, te ajudar 🙌😁.

Para conectar comigo no Linkedin, esse aqui é o link. Para me seguir aqui no Medium, é nesse aqui.