Prática: Análise de Dados

Q: P: Qual a diferença entre funções de janela e GROUP BY?

R: GROUP BY mescla múltiplas linhas em uma (reduzindo a contagem de linhas após a agregação); funções de janela mantêm todas as linhas originais e adicionam resultados de agregação a cada linha. Se você precisa tanto de agregação quanto de detalhes, use funções de janela.

📋 Requisitos do Projeto

Esta lição aborda dois cenários práticos — Análise de Relatório de Vendas e Estatísticas de Desempenho de Funcionários — aplicando funções de agregação, consultas com agrupamento, expressões condicionais, JOINs e subconsultas aprendidas em lições anteriores.

Estrutura das Tabelas do Banco de Dados

Usamos um banco de dados unificado de 4 tabelas:

SQL

-- Tabela de departamentos
CREATE TABLE departments (
    department_id   INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    city            VARCHAR(50),
    budget          DECIMAL(12,2)
);

-- Tabela de funcionários
CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    email         VARCHAR(100),
    salary        DECIMAL(10,2),
    department_id INT,
    hire_date     DATE,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Tabela de pedidos
CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    employee_id  INT,
    order_date   DATE,
    total_amount DECIMAL(12,2),
    status       VARCHAR(20),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

-- Tabela de produtos
CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price        DECIMAL(10,2),
    stock        INT,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Dados de Teste

SQL

-- Dados de departamentos
INSERT INTO departments VALUES (1, 'Technology', 'Beijing', 500000);
INSERT INTO departments VALUES (2, 'Sales', 'Shanghai', 300000);
INSERT INTO departments VALUES (3, 'Marketing', 'Guangzhou', 200000);
INSERT INTO departments VALUES (4, 'Finance', 'Shenzhen', 150000);

-- Dados de funcionários
INSERT INTO employees VALUES (1, 'Zhang', 'Wei', 'zhangwei@co.com', 12000, 1, '2023-03-15');
INSERT INTO employees VALUES (2, 'Li', 'Na', 'lina@co.com', 9500, 1, '2023-06-20');
INSERT INTO employees VALUES (3, 'Wang', 'Qiang', 'wangqiang@co.com', 8000, 2, '2022-11-10');
INSERT INTO employees VALUES (4, 'Zhao', 'Min', 'zhaomin@co.com', 11000, 2, '2024-01-05');
INSERT INTO employees VALUES (5, 'Liu', 'Yang', 'liuyang@co.com', 7500, 3, '2024-04-18');
INSERT INTO employees VALUES (6, 'Chen', 'Jing', 'chenjing@co.com', 9000, NULL, '2025-02-28');

-- Dados de pedidos
INSERT INTO orders VALUES (1001, 101, 3, '2026-01-10', 2500.00, 'completed');
INSERT INTO orders VALUES (1002, 102, 3, '2026-02-15', 1800.00, 'completed');
INSERT INTO orders VALUES (1003, 103, 4, '2026-02-20', 3200.00, 'shipped');
INSERT INTO orders VALUES (1004, 101, 1, '2026-03-05', 950.00, 'pending');
INSERT INTO orders VALUES (1005, 104, 4, '2026-03-10', 4100.00, 'completed');
INSERT INTO orders VALUES (1006, 105, 3, '2026-04-01', 2800.00, 'cancelled');
INSERT INTO orders VALUES (1007, 101, 1, '2026-04-15', 5600.00, 'completed');
INSERT INTO orders VALUES (1008, 106, 4, '2026-05-02', 1500.00, 'completed');
INSERT INTO orders VALUES (1009, 102, 3, '2026-05-20', 3800.00, 'shipped');
INSERT INTO orders VALUES (1010, 107, 1, '2026-06-01', 2200.00, 'pending');
INSERT INTO orders VALUES (1011, 103, 4, '2026-06-10', 6300.00, 'completed');
INSERT INTO orders VALUES (1012, 108, 3, '2026-06-15', 1900.00, 'completed');

-- Dados de produtos
INSERT INTO products VALUES (1, 'Laptop', 6999.00, 50, 1);
INSERT INTO products VALUES (2, 'Wireless Mouse', 129.00, 200, 1);
INSERT INTO products VALUES (3, 'Office Desk', 899.00, 30, 2);
INSERT INTO products VALUES (4, 'Projector', 3500.00, 15, 3);
INSERT INTO products VALUES (5, 'Printer', 2200.00, 25, 1);
INSERT INTO products VALUES (6, 'Meeting Chair', 599.00, 40, 2);
INSERT INTO products VALUES (7, 'Whiteboard', 450.00, 60, 3);

🏗️ Prática de Consultas

Consulta 1: Relatório de Desempenho de Vendas por Funcionário

Requisito: Gerar um relatório de desempenho de vendas para cada funcionário, incluindo contagem de pedidos, total de vendas, valor médio do pedido, pedidos por status e uma classificação de desempenho.

SQL

SELECT 
    CONCAT(e.first_name, e.last_name) AS nome_funcionario,
    d.department_name AS departamento,
    COUNT(o.order_id) AS total_pedidos,
    COALESCE(SUM(o.total_amount), 0) AS total_vendas,
    COALESCE(ROUND(AVG(o.total_amount), 2), 0) AS valor_medio_pedido,
    SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS concluidos,
    SUM(CASE WHEN o.status = 'shipped'   THEN 1 ELSE 0 END) AS enviados,
    SUM(CASE WHEN o.status = 'pending'   THEN 1 ELSE 0 END) AS pendentes,
    SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelados,
    CASE
        WHEN COALESCE(SUM(o.total_amount), 0) >= 10000 THEN 'S - Excepcional'
        WHEN COALESCE(SUM(o.total_amount), 0) >= 5000  THEN 'A - Excelente'
        WHEN COALESCE(SUM(o.total_amount), 0) >= 2000  THEN 'B - Bom'
        WHEN COUNT(o.order_id) > 0                      THEN 'C - Precisa Melhorar'
        ELSE 'Sem Desempenho'
    END AS classificacao_desempenho
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN orders o ON e.employee_id = o.employee_id
GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name
ORDER BY total_vendas DESC;

Resultado:

TEXT

nome_funcionario | departamento | total_pedidos | total_vendas | valor_medio_pedido | concluidos | enviados | pendentes | cancelados | classificacao_desempenho
-----------------+--------------+---------------+--------------+--------------------+------------+----------+-----------+------------+------------------------
ZhaoMin          | Sales        |             4 |     15100.00 |            3775.00 |          3 |        1 |         0 |          0 | S - Excepcional
WangQiang        | Sales        |             4 |     10900.00 |            2725.00 |          2 |        1 |         0 |          1 | S - Excepcional
ZhangWei         | Technology   |             3 |      8750.00 |            2916.67 |          1 |        0 |         2 |          0 | A - Excelente
LiNa             | Technology   |             0 |         0.00 |               0.00 |          0 |        0 |         0 |          0 | Sem Desempenho
LiuYang          | Marketing    |             0 |         0.00 |               0.00 |          0 |        0 |         0 |          0 | Sem Desempenho
ChenJing         | NULL         |             0 |         0.00 |               0.00 |          0 |        0 |         0 |          0 | Sem Desempenho

Explicação do Código:

LEFT JOIN — Mantém todos os funcionários, mesmo aqueles sem pedidos
COALESCE(SUM(...), 0) — Exibe 0 para funcionários sem pedidos
SUM(CASE WHEN ... THEN 1 ELSE 0 END) — Contagem condicional de pedidos por status (pivô de linhas para colunas)
CASE — Atribui classificação de desempenho com base no total de vendas
ROUND(AVG(...), 2) — Arredonda a média para duas casas decimais

Consulta 2: Comparação Trimestral de Vendas por Departamento

Requisito: Agregar vendas por departamento e trimestre, calcular a porcentagem de vendas de cada departamento e encontrar o departamento líder de vendas para cada trimestre.

SQL

SELECT 
    d.department_name AS departamento,
    CONCAT('Q', QUARTER(o.order_date)) AS trimestre,
    COUNT(o.order_id) AS qtd_pedidos,
    SUM(o.total_amount) AS vendas_trimestrais,
    ROUND(
        SUM(o.total_amount) / 
        SUM(SUM(o.total_amount)) OVER (PARTITION BY QUARTER(o.order_date)) * 100, 
    2) AS porcentagem,
    RANK() OVER (
        PARTITION BY QUARTER(o.order_date) 
        ORDER BY SUM(o.total_amount) DESC
    ) AS ranking_trimestre
FROM orders o
INNER JOIN employees e ON o.employee_id = e.employee_id
INNER JOIN departments d ON e.department_id = d.department_id
WHERE o.status != 'cancelled'
GROUP BY d.department_name, QUARTER(o.order_date)
ORDER BY trimestre, vendas_trimestrais DESC;

Resultado:

TEXT

departamento | trimestre | qtd_pedidos | vendas_trimestrais | porcentagem | ranking_trimestre
-------------+-----------+-------------+--------------------+ ------------+------------------
Sales        | Q1        |           2 |            4300.00 |       69.35 |                 1
Technology   | Q1        |           1 |             950.00 |       15.32 |                 2
Sales        | Q2        |           2 |            5600.00 |       26.54 |                 1
Technology   | Q2        |           1 |            5600.00 |       26.54 |                 1
Marketing    | Q2        |           1 |            3800.00 |       18.01 |                 3

Explicação do Código:

QUARTER(o.order_date) — Extrai o trimestre da data do pedido (Q1-Q4)
SUM(o.total_amount) OVER (PARTITION BY ...) — Função de janela calculando o total de vendas por trimestre
porcentagem — Vendas trimestrais de cada departamento ÷ total de vendas trimestrais × 100
RANK() OVER (PARTITION BY ... ORDER BY ...) — Classifica dentro de cada trimestre

Consulta 3: Segmentação de Valor do Cliente (Modelo RFM Simplificado)

Requisito: Segmentar clientes com base em Recência (data da última compra), Frequência (contagem de compras) e Monetário (total gasto).

SQL

WITH customer_stats AS (
    SELECT 
        customer_id,
        MAX(order_date) AS data_ultima_compra,
        COUNT(order_id) AS qtd_compras,
        SUM(total_amount) AS total_gasto
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY customer_id
)
SELECT 
    customer_id,
    data_ultima_compra,
    qtd_compras,
    total_gasto,
    CASE
        WHEN qtd_compras >= 3 AND total_gasto >= 5000 THEN 'Cliente VIP'
        WHEN qtd_compras >= 2 AND total_gasto >= 3000 THEN 'Cliente Chave'
        WHEN qtd_compras >= 2                       THEN 'Cliente Ativo'
        WHEN total_gasto >= 2000                     THEN 'Cliente Potencial'
        ELSE 'Cliente Regular'
    END AS nivel_cliente
FROM customer_stats
ORDER BY total_gasto DESC;

Resultado:

TEXT

customer_id | data_ultima_compra | qtd_compras | total_gasto | nivel_cliente
------------+-------------------+-------------+-------------+----------------
        103 | 2026-06-10        |           2 |     9500.00 | Cliente VIP
        101 | 2026-04-15        |           2 |     3450.00 | Cliente Chave
        104 | 2026-03-10        |           1 |     4100.00 | Cliente Potencial
        102 | 2026-05-20        |           2 |     5600.00 | Cliente VIP
        106 | 2026-05-02        |           1 |     1500.00 | Cliente Regular
        105 | 2026-04-01        |           0 |        0.00 | Cliente Regular
        107 | 2026-06-01        |           1 |     2200.00 | Cliente Potencial
        108 | 2026-06-15        |           1 |     1900.00 | Cliente Regular

Explicação do Código:

WITH customer_stats AS (...) — CTE (Expressão de Tabela Comum) primeiro calcula estatísticas para cada cliente
Dentro do CTE: MAX(order_date) para última compra, COUNT para contagem de compras, SUM para total gasto
CASE externo segmenta clientes com base em condições multidimensionais
Exclui pedidos cancelados (status != 'cancelled')

Consulta 4: Análise de Correlação entre Estoque e Vendas de Produtos

Requisito: Analisar o estoque de produtos por departamento, combinado com dados de vendas para avaliar a rotatividade do estoque.

SQL

SELECT 
    d.department_name AS departamento,
    COUNT(DISTINCT p.product_id) AS qtd_produtos,
    SUM(p.stock) AS estoque_total,
    COALESCE(SUM(p.price * p.stock), 0) AS valor_estoque,
    COUNT(DISTINCT o.order_id) AS pedidos_relacionados,
    COALESCE(SUM(o.total_amount), 0) AS total_pedidos,
    CASE 
        WHEN SUM(p.price * p.stock) > 0 
        THEN ROUND(COALESCE(SUM(o.total_amount), 0) / SUM(p.price * p.stock) * 100, 2)
        ELSE 0
    END AS taxa_rotatividade_pct
FROM departments d
LEFT JOIN products p ON d.department_id = p.department_id
LEFT JOIN employees e ON d.department_id = e.department_id
LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
GROUP BY d.department_id, d.department_name
ORDER BY valor_estoque DESC;

Resultado:

TEXT

departamento | qtd_produtos | estoque_total | valor_estoque | pedidos_relacionados | total_pedidos | taxa_rotatividade_pct
-------------+--------------+---------------+---------------+---------------------+---------------+---------------------
Technology   |            3 |           275 |     369945.00 |                   4 |        8750.00 |                 2.36
Sales        |            2 |            70 |      68930.00 |                   8 |       26000.00 |                37.71
Marketing    |            2 |            75 |      86250.00 |                   0 |           0.00 |                 0.00

Explicação do Código:

LEFT JOIN multi-tabela: departamentos → produtos + funcionários → pedidos
COUNT(DISTINCT ...) — Contagem desduplicada para evitar duplicatas
p.price * p.stock — Calcula o valor do estoque do produto
Taxa de rotatividade = total de pedidos ÷ valor do estoque × 100%

Consulta 5: Classificação de Desempenho de Funcionários e Comparação entre Departamentos

Requisito: Classificar funcionários por desempenho geral e comparar com as médias do departamento.

SQL

WITH emp_performance AS (
    SELECT 
        e.employee_id,
        CONCAT(e.first_name, e.last_name) AS nome,
        d.department_name AS departamento,
        e.salary AS salario_base,
        COUNT(o.order_id) AS qtd_pedidos,
        COALESCE(SUM(o.total_amount), 0) AS valor_vendas,
        COALESCE(AVG(o.total_amount), 0) AS valor_medio_pedido
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id
    LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
    GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name, e.salary
)
SELECT 
    nome,
    departamento,
    salario_base,
    qtd_pedidos,
    valor_vendas,
    ROUND(valor_medio_pedido, 2) AS valor_medio_pedido,
    RANK() OVER (ORDER BY valor_vendas DESC) AS ranking_empresa,
    RANK() OVER (PARTITION BY departamento ORDER BY valor_vendas DESC) AS ranking_dept,
    ROUND(valor_vendas - AVG(valor_vendas) OVER (PARTITION BY departamento), 2) AS vs_media_dept
FROM emp_performance
ORDER BY valor_vendas DESC;

Resultado:

TEXT

nome      | departamento | salario_base | qtd_pedidos | valor_vendas | valor_medio_pedido | ranking_empresa | ranking_dept | vs_media_dept
----------+--------------+--------------+-------------+--------------+--------------------+-----------------+--------------+--------------
ZhaoMin   | Sales        |     11000.00 |           4 |     15100.00 |            3775.00 |               1 |            1 |      3550.00
WangQiang | Sales        |      8000.00 |           3 |     10900.00 |            3633.33 |               2 |            2 |      -650.00
ZhangWei  | Technology   |     12000.00 |           2 |      8750.00 |            4375.00 |               3 |            1 |      8750.00
LiNa      | Technology   |      9500.00 |           0 |         0.00 |               0.00 |               4 |            2 |     -8750.00
LiuYang   | Marketing    |      7500.00 |           0 |         0.00 |               0.00 |               4 |            1 |         0.00
ChenJing  | NULL         |      9000.00 |           0 |         0.00 |               0.00 |               4 |            1 |         0.00

Explicação do Código:

CTE emp_performance — Primeiro calcula dados de desempenho base para cada funcionário
RANK() OVER (ORDER BY ...) — Classificação geral da empresa
RANK() OVER (PARTITION BY ... ORDER BY ...) — Classificação dentro do departamento
AVG(valor_vendas) OVER (PARTITION BY departamento) — Calcula a média de vendas do departamento
vs_media_dept — Diferença entre vendas individuais e média do departamento

❓ Perguntas Frequentes

P: Qual a diferença entre CTE (instrução WITH) e subconsultas? R: CTEs possuem melhor legibilidade e podem ser referenciadas múltiplas vezes dentro da mesma consulta; subconsultas são re-executadas cada vez que são referenciadas. CTEs também suportam consultas recursivas. Para consultas complexas, CTEs são recomendadas.

P: Qual a diferença entre funções de janela e GROUP BY? R: GROUP BY mescla múltiplas linhas em uma (reduzindo a contagem de linhas após a agregação); funções de janela mantêm todas as linhas originais e adicionam resultados de agregação a cada linha. Se você precisa tanto de agregação quanto de detalhes, use funções de janela.

P: Existe um limite para a profundidade de aninhamento do CASE WHEN? R: Teoricamente não, mas aninhamento excessivo indica lógica excessivamente complexa. Considere usar CTEs para decompor etapas, ou lidar com lógica complexa na camada de aplicação.

P: Como otimizar consultas de agregação com grandes volumes de dados? R: Crie índices em colunas usadas em GROUP BY e WHERE; use CTEs para decompor consultas complexas; evite usar funções em colunas agregadas (ex: mude WHERE YEAR(date) = 2026 para WHERE date >= '2026-01-01' AND date < '2027-01-01'); use EXPLAIN para analisar planos de execução.

📖 Resumo

Habilidade	Aplicação nesta Lição
Funções de Agregação	COUNT, SUM, AVG, MAX, MIN
GROUP BY	Estatísticas por dimensão de grupo
HAVING	Filtrar resultados de agregação
CASE WHEN	Estatísticas condicionais (pivô de linhas para colunas), atribuição de classificação
JOIN	Consultas multi-tabela
Subconsulta / CTE	Construir consultas complexas passo a passo
Funções de Janela	RANK() classificação, AVG() OVER() análise comparativa
COALESCE	Lidar com valores NULL

Pontos-Chave:

Use CTEs para construir consultas complexas passo a passo para melhor legibilidade
CASE WHEN + funções de agregação = estatísticas condicionais (pivô de linhas para colunas)
Funções de janela mantêm detalhes enquanto adicionam informações de agregação
Sempre use COALESCE para lidar com NULL e evitar valores vazios em relatórios
Use EXPLAIN para analisar desempenho de consultas e crie índices para colunas-chave

📝 Exercícios

Exercício 1: Escreva uma consulta para gerar um "Relatório de Tendência de Vendas Mensais" — agregue contagem de pedidos, total de vendas e valor médio do pedido por mês, e calcule a taxa de crescimento mês a mês: (mês atual - mês anterior) / mês anterior × 100%.

Exercício 2: Escreva uma consulta para encontrar "o funcionário com o maior salário em cada departamento" juntamente com seu "número de pedidos processados", usando CTE + funções de janela.

Exercício 3: Escreva uma consulta para segmentar clientes por valor gasto (≥5000 como VIP, ≥3000 como Chave, ≥1000 como Regular, <1000 como Novo Cliente), e conte o número de clientes e o total gasto para cada nível.

Exercício 4: Crie uma visualização v_employee_dashboard contendo nome do funcionário, departamento, salário, contagem de pedidos, total de vendas e classificação de desempenho. Use a visualização para consultar o desempenho dos funcionários do departamento "Sales".

Próxima Lição

👉 19-window-functions - Funções de Janela: Aprofunde-se em ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD e outras funções de janela para dominar técnicas avançadas de análise de dados!

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

Sobre Nós