Agregação e Agrupamento de Dados MySQL

Introdução

Neste laboratório, exploraremos a agregação e o agrupamento de dados no MySQL, habilidades essenciais para analisar e resumir dados em bancos de dados. Você aprenderá a usar funções agregadas para realizar cálculos em linhas, agrupar dados com base em valores de colunas e filtrar resultados agrupados. Essas técnicas são fundamentais para gerar relatórios, analisar tendências e extrair insights significativos de seus dados. Através de exercícios práticos, você obterá experiência prática com essas operações cruciais de banco de dados e entenderá como analisar dados de forma eficaz no MySQL.

Funções Agregadas Básicas

Nesta etapa, exploraremos as funções agregadas fundamentais no MySQL. Essas funções realizam cálculos em várias linhas e retornam um único valor, tornando-as essenciais para a análise de dados.

Primeiro, vamos conectar ao MySQL e selecionar nosso banco de dados:

sudo mysql -u root

Uma vez conectado:

USE sales_db;

Função COUNT

Vamos começar com a função COUNT, que conta o número de linhas em um conjunto de resultados:

-- Contar o número total de vendas
SELECT COUNT(*) as total_sales
FROM sales;

Você deve ver uma saída como:

+--------------+
| total_sales  |
+--------------+
| 12           |
+--------------+

COUNT pode ser usado de diferentes maneiras:

-- Contar produtos únicos vendidos
SELECT COUNT(DISTINCT product_name) as unique_products
FROM sales;

-- Contar vendas por categoria
SELECT category, COUNT(*) as sales_count
FROM sales
GROUP BY category;

Explicação:

• COUNT(*) conta todas as linhas na tabela, incluindo valores NULL
• COUNT(DISTINCT column) conta apenas valores únicos na coluna especificada
• Quando usado com GROUP BY, COUNT calcula totais para cada grupo separadamente
• A palavra-chave as cria aliases para colunas de resultados, tornando a saída mais legível

Função SUM

A função SUM calcula o total de colunas numéricas:

-- Calcular a quantidade total vendida
SELECT SUM(quantity) as total_items_sold
FROM sales;

-- Calcular a receita total
SELECT
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales;

Explicação:

• SUM só pode ser usado com colunas numéricas
• Cálculos como quantity * unit_price são realizados antes da agregação
• ROUND(x, 2) arredonda números para 2 casas decimais
• Para cálculos de receita, a multiplicação acontece antes da soma para manter a precisão

Função AVG

A função AVG calcula o valor médio:

-- Calcular o preço unitário médio
SELECT
    ROUND(AVG(unit_price), 2) as avg_price
FROM sales;

-- Calcular a quantidade média por venda
SELECT
    ROUND(AVG(quantity), 1) as avg_quantity
FROM sales;

Explicação:

• AVG ignora automaticamente valores NULL em seus cálculos
• O segundo parâmetro em ROUND especifica o número de casas decimais
• Os resultados são automaticamente convertidos para o tipo DECIMAL para precisão
• AVG é comumente usado para encontrar valores típicos em seus dados

Funções MAX e MIN

Essas funções encontram os valores mais altos e mais baixos:

-- Encontrar a faixa de preço dos produtos
SELECT
    MIN(unit_price) as lowest_price,
    MAX(unit_price) as highest_price
FROM sales;

-- Encontrar as datas da primeira e última venda
SELECT
    MIN(sale_date) as first_sale,
    MAX(sale_date) as last_sale
FROM sales;

Explicação:

• MIN/MAX funcionam com números, strings e datas
• Para datas, MIN encontra a data mais antiga, enquanto MAX encontra a mais recente
• Múltiplas funções agregadas podem ser combinadas em uma única instrução SELECT
• Como outras funções agregadas, elas ignoram automaticamente valores NULL
• Essas funções são úteis para encontrar faixas de valores e limites em seus dados

Agrupando Dados com GROUP BY

Nesta etapa, aprenderemos como agrupar dados usando a cláusula GROUP BY. O agrupamento nos permite realizar cálculos agregados em subconjuntos de nossos dados com base em valores específicos de colunas.

Agrupamento Básico

Vamos começar com operações de agrupamento simples:

-- Contagem de vendas e quantidade total por categoria
SELECT
    category,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
ORDER BY total_quantity DESC;

Esta consulta mostra quantas vendas e o total de itens foram vendidos em cada categoria. Você deve ver uma saída como:

+-------------+-------------+----------------+
| category    | sales_count | total_quantity |
+-------------+-------------+----------------+
| Furniture   |           5 |             22 |
| Electronics |           5 |             21 |
| Appliances  |           2 |             10 |
+-------------+-------------+----------------+

Explicação:

• GROUP BY consolida linhas com a mesma categoria em uma única linha
• O ORDER BY total_quantity DESC classifica os resultados da quantidade mais alta para a mais baixa
• Cada função agregada (COUNT, SUM) opera dentro de cada grupo independentemente
• Colunas não agregadas em SELECT devem aparecer na cláusula GROUP BY

Agrupamento de Múltiplas Colunas

Podemos agrupar por várias colunas para obter insights mais detalhados:

-- Análise de vendas por categoria e região
SELECT
    category,
    region,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category, region
ORDER BY category, total_revenue DESC;

Explicação:

• Agrupar por várias colunas cria subgrupos para cada combinação única
• A ordem das colunas em GROUP BY afeta como os dados são agrupados
• Os resultados são classificados primeiro por categoria, depois por total_revenue dentro de cada categoria
• Essa abordagem ajuda a identificar as regiões com melhor desempenho dentro de cada categoria

Agrupamento Baseado em Data

O MySQL fornece funções para extrair partes de datas, úteis para agrupamento baseado em tempo:

-- Resumo diário de vendas
SELECT
    sale_date,
    COUNT(*) as transactions,
    SUM(quantity) as items_sold,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

Explicação:

• Agrupa todas as transações que ocorreram na mesma data
• O ORDER BY sale_date organiza os resultados cronologicamente
• Conta as transações por dia e calcula os totais diários
• Útil para identificar padrões e tendências diárias de vendas
• Pode ser modificado para agrupar por mês ou ano usando funções de DATA

Filtrando Dados Agrupados com HAVING

Nesta etapa, aprenderemos sobre a cláusula HAVING, que nos permite filtrar resultados após o agrupamento. Enquanto WHERE filtra linhas individuais antes do agrupamento, HAVING filtra os próprios grupos.

Uso Básico de HAVING

Vamos encontrar categorias com mais de 15 itens totais vendidos:

SELECT
    category,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
HAVING total_quantity > 15;

Isso mostra apenas as categorias onde a quantidade total excede 15 itens:

+-------------+----------------+
| category    | total_quantity |
+-------------+----------------+
| Electronics |             21 |
| Furniture   |             22 |
+-------------+----------------+

Explicação:

• HAVING filtra grupos após a aplicação de GROUP BY
• Você pode referenciar resultados de funções agregadas em HAVING
• A condição de filtro usa o alias 'total_quantity'
• Categorias com 15 ou menos itens são excluídas dos resultados

Combinando WHERE e HAVING

Podemos usar WHERE e HAVING juntos. WHERE filtra linhas antes do agrupamento, enquanto HAVING filtra após o agrupamento:

-- Encontrar categorias de alto volume na região Norte
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity
FROM sales
WHERE region = 'North'
GROUP BY category
HAVING total_quantity > 5;

Explicação:

• WHERE filtra linhas individuais (region = 'North') antes do agrupamento
• Em seguida, os dados são agrupados por categoria
• Finalmente, HAVING filtra os resultados agrupados (total_quantity > 5)
• A ordem importa: FROM → WHERE → GROUP BY → HAVING → SELECT

Condições Complexas de HAVING

Podemos usar múltiplas condições em HAVING:

-- Encontrar categorias com alto volume de vendas e receita
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
HAVING total_quantity > 10 AND total_revenue > 1000
ORDER BY total_revenue DESC;

Explicação:

• Múltiplas condições podem ser combinadas usando AND/OR
• Todos os cálculos agregados estão disponíveis para condições HAVING
• Você pode usar operações aritméticas em HAVING
• O ORDER BY é aplicado após a avaliação dos filtros HAVING
• Esse tipo de consulta é útil para identificar as categorias com melhor desempenho

Técnicas Avançadas de Agregação

Nesta etapa final, exploraremos técnicas de agregação mais avançadas, combinando tudo o que aprendemos. Criaremos relatórios de vendas abrangentes que demonstram o poder dessas ferramentas em conjunto.

Painel de Desempenho de Vendas

Vamos criar uma análise de vendas abrangente:

SELECT
    category,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as total_transactions,
    SUM(quantity) as total_quantity,
    ROUND(AVG(quantity), 1) as avg_quantity_per_sale,
    ROUND(MIN(unit_price), 2) as min_price,
    ROUND(MAX(unit_price), 2) as max_price,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

Explicação:

• Cria uma visão geral completa do desempenho por categoria
• Usa DISTINCT para contar produtos únicos dentro de cada categoria
• Combina múltiplas funções agregadas para análise abrangente
• Mostra faixas de preços com MIN e MAX
• Calcula quantidades médias e receita total
• Os resultados são ordenados por receita para destacar os melhores desempenhos

Análise de Desempenho Regional

Vamos analisar o desempenho de vendas por região:

SELECT
    region,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue,
    ROUND(SUM(quantity * unit_price) / SUM(quantity), 2) as avg_price_per_unit
FROM sales
GROUP BY region
HAVING total_revenue > 1000
ORDER BY total_revenue DESC;

Explicação:

• Agrupa dados de vendas por regiões geográficas
• Mostra a diversidade de produtos com contagens DISTINCT
• Calcula métricas de receita total e quantidade
• avg_price_per_unit é calculado usando a receita dividida pela quantidade
• HAVING filtra as regiões com menor receita
• Ajuda a identificar as regiões com melhor e pior desempenho

Análise de Tendências Diárias

Vamos criar um relatório de tendências de vendas diárias:

SELECT
    sale_date,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as transactions,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue,
    ROUND(AVG(quantity * unit_price), 2) as avg_transaction_value
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

Explicação:

• Acompanha as métricas de desempenho de vendas diárias
• Mostra a diversidade de produtos e categorias por dia
• Conta as transações e quantidades diárias
• Calcula a receita diária e o valor médio da transação
• A ordenação cronológica ajuda a identificar tendências
• Útil para identificar padrões e efeitos sazonais
• Pode ajudar no planejamento de estoque e nas decisões de pessoal

Resumo

Neste laboratório, cobrimos os aspectos essenciais da agregação e agrupamento de dados em MySQL:

1. Usando funções agregadas básicas (COUNT, SUM, AVG, MAX, MIN) para resumir dados
2. Agrupando dados usando GROUP BY para analisar padrões e tendências
3. Filtrando resultados agrupados usando a cláusula HAVING
4. Combinando múltiplas técnicas para criar análises de dados abrangentes

Essas habilidades são fundamentais para análise e relatórios de dados em MySQL. Compreender como agregar e agrupar dados de forma eficaz permite que você extraia insights significativos de seus bancos de dados e crie relatórios de negócios valiosos.