Prática: Integração de Recursos Avançados

Requisitos do Projeto

Esta lição aplicará abrangentemente recursos avançados de SQL através de quatro cenários práticos:

Consultas de classificação: Use funções de janela para implementar vários tipos de classificações
Cálculos acumulados: Implemente totais acumulados e médias móveis
Consultas recursivas: Use CTEs para lidar com dados hierárquicos
Lógica de negócios complexa: Combine transações para operações de negócios em lote

Preparação dos Dados

Primeiro, certifique-se de que temos uma estrutura de banco de dados unificada de quatro tabelas:

SQL

-- Tabela de departamentos
CREATE TABLE IF NOT EXISTS departments (
    id INTEGER PRIMARY KEY,
    department_name TEXT NOT NULL,
    manager_id INTEGER,
    location TEXT
);

-- Tabela de funcionários
CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER,
    position TEXT,
    salary REAL,
    hire_date TEXT,
    manager_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Tabela de produtos
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT,
    price REAL,
    stock_quantity INTEGER
);

-- Tabela de pedidos
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY,
    customer_name TEXT,
    product_id INTEGER,
    quantity INTEGER,
    amount REAL,
    order_date TEXT,
    status TEXT,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Inserir dados de teste
INSERT OR IGNORE INTO departments VALUES
(1, 'Tech', 1, 'Beijing'),
(2, 'Marketing', 4, 'Shanghai'),
(3, 'Finance', 6, 'Beijing');

INSERT OR IGNORE INTO employees VALUES
(1, 'Alice', 1, 'Engenheira Sênior', 15000, '2020-01-15', NULL),
(2, 'Bob', 1, 'Engenheiro Pleno', 12000, '2021-03-20', 1),
(3, 'Charlie', 1, 'Engenheiro Júnior', 8000, '2022-06-10', 1),
(4, 'Diana', 2, 'Diretora de Marketing', 18000, '2019-05-01', NULL),
(5, 'Eve', 2, 'Especialista em Marketing', 9000, '2021-08-15', 4),
(6, 'Frank', 3, 'Gerente Financeiro', 16000, '2020-02-28', NULL),
(7, 'Grace', 3, 'Contadora', 10000, '2021-11-05', 6),
(8, 'Henry', 1, 'Estagiário de Engenharia', 5000, '2023-07-01', 2);

INSERT OR IGNORE INTO products VALUES
(1, 'Laptop', 'Eletrônicos', 6999, 50),
(2, 'Mouse Sem Fio', 'Eletrônicos', 199, 200),
(3, 'Teclado Mecânico', 'Eletrônicos', 599, 100),
(4, 'Cadeira de Escritório', 'Móveis', 1299, 30),
(5, 'Monitor', 'Eletrônicos', 2499, 80);

INSERT OR IGNORE INTO orders VALUES
(1, 'ClienteA', 1, 2, 13998, '2024-01-10', 'completed'),
(2, 'ClienteB', 2, 5, 995, '2024-01-15', 'completed'),
(3, 'ClienteA', 3, 3, 1797, '2024-02-01', 'completed'),
(4, 'ClienteC', 1, 1, 6999, '2024-02-15', 'pending'),
(5, 'ClienteB', 4, 2, 2598, '2024-03-01', 'completed'),
(6, 'ClienteD', 5, 4, 9996, '2024-03-10', 'completed'),
(7, 'ClienteA', 2, 10, 1990, '2024-03-15', 'completed'),
(8, 'ClienteC', 3, 1, 599, '2024-04-01', 'cancelled');

Prática 1: Consultas de Classificação

Classificação de Salário de Funcionários

SQL

-- Várias consultas de classificação
WITH salary_ranking AS (
    SELECT 
        e.id,
        e.name,
        d.department_name,
        e.salary,
        e.hire_date,
        -- Classificação salarial em toda a empresa
        RANK() OVER (ORDER BY e.salary DESC) AS company_rank,
        -- Classificação salarial em toda a empresa (sem empates)
        ROW_NUMBER() OVER (ORDER BY e.salary DESC) AS company_row_num,
        -- Classificação salarial por departamento
        RANK() OVER (
            PARTITION BY e.department_id 
            ORDER BY e.salary DESC
        ) AS dept_rank,
        -- Classificação percentual de salário
        PERCENT_RANK() OVER (ORDER BY e.salary DESC) AS percentile,
        -- Agrupamento de salário (4 grupos)
        NTILE(4) OVER (ORDER BY e.salary DESC) AS salary_quartile
    FROM employees e
    JOIN departments d ON e.department_id = d.id
)
SELECT 
    name,
    department_name,
    salary,
    company_rank AS "Classificação Empresa",
    dept_rank AS "Classificação Depto",
    CASE salary_quartile
        WHEN 1 THEN 'Faixa Superior'
        WHEN 2 THEN 'Faixa Média Superior'
        WHEN 3 THEN 'Faixa Média Inferior'
        WHEN 4 THEN 'Faixa Inferior'
    END AS "Faixa Salarial",
    ROUND(percentile * 100, 1) || '%' AS "Percentil"
FROM salary_ranking
ORDER BY company_rank;

Classificação de Vendas de Produtos

SQL

-- Classificação de vendas e receita de produtos
WITH product_sales AS (
    SELECT 
        p.id,
        p.product_name,
        p.category,
        p.price,
        COALESCE(SUM(o.quantity), 0) AS total_quantity,
        COALESCE(SUM(o.amount), 0) AS total_revenue,
        COUNT(o.id) AS order_count
    FROM products p
    LEFT JOIN orders o ON p.id = o.product_id AND o.status = 'completed'
    GROUP BY p.id, p.product_name, p.category, p.price
)
SELECT 
    product_name AS "Produto",
    category AS "Categoria",
    price AS "Preço Unitário",
    total_quantity AS "Qtd Total",
    total_revenue AS "Receita Total",
    order_count AS "Pedidos",
    RANK() OVER (ORDER BY total_revenue DESC) AS "Classif. Receita",
    RANK() OVER (
        PARTITION BY category 
        ORDER BY total_quantity DESC
    ) AS "Classif. Qtd Categoria",
    DENSE_RANK() OVER (ORDER BY total_quantity DESC) AS "Classif. Vendas"
FROM product_sales
ORDER BY total_revenue DESC;

Prática 2: Cálculos Acumulados

Vendas Mensais Acumuladas

SQL

-- Vendas mensais e cálculos acumulados
WITH monthly_sales AS (
    SELECT 
        strftime('%Y-%m', order_date) AS month,
        SUM(amount) AS monthly_total,
        COUNT(*) AS order_count
    FROM orders
    WHERE status = 'completed'
    GROUP BY strftime('%Y-%m', order_date)
)
SELECT 
    month AS "Mês",
    monthly_total AS "Vendas Mensais",
    order_count AS "Pedidos",
    -- Vendas acumuladas
    SUM(monthly_total) OVER (ORDER BY month) AS "Vendas Acumuladas",
    -- Pedidos acumulados
    SUM(order_count) OVER (ORDER BY month) AS "Pedidos Acumulados",
    -- Taxa de crescimento mês a mês
    ROUND(
        (monthly_total - LAG(monthly_total) OVER (ORDER BY month)) 
        / LAG(monthly_total) OVER (ORDER BY month) * 100, 
        2
    ) AS "Crescimento M/M %",
    -- Diferença do mês anterior
    monthly_total - LAG(monthly_total) OVER (ORDER BY month) AS "Diferença M/M"
FROM monthly_sales
ORDER BY month;

Cálculo de Média Móvel

SQL

-- Média móvel dos valores de pedidos
WITH order_details AS (
    SELECT 
        o.id,
        o.order_date,
        o.amount,
        o.customer_name,
        -- Média móvel de 3 dias
        AVG(o.amount) OVER (
            ORDER BY o.order_date 
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS avg_3day,
        -- Média móvel de 5 dias
        AVG(o.amount) OVER (
            ORDER BY o.order_date 
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
        ) AS avg_5day,
        -- Média acumulada
        AVG(o.amount) OVER (ORDER BY o.order_date) AS cumulative_avg,
        -- Valor do pedido anterior
        LAG(o.amount, 1) OVER (ORDER BY o.order_date) AS prev_amount,
        -- Valor máximo de pedido acumulado
        MAX(o.amount) OVER (ORDER BY o.order_date) AS running_max
    FROM orders o
    WHERE o.status = 'completed'
)
SELECT 
    order_date AS "Data do Pedido",
    amount AS "Valor",
    ROUND(avg_3day, 2) AS "MM 3 Dias",
    ROUND(avg_5day, 2) AS "MM 5 Dias",
    ROUND(cumulative_avg, 2) AS "Média Acumulada",
    running_max AS "Máx Acumulado"
FROM order_details
ORDER BY order_date;

Prática 3: Consultas Recursivas

Consulta de Hierarquia Organizacional

SQL

-- Consulta recursiva para hierarquia organizacional
WITH RECURSIVE org_hierarchy AS (
    -- Âncora: gerentes de nível superior (funcionários sem gerente)
    SELECT 
        e.id,
        e.name,
        e.position,
        e.manager_id,
        0 AS level,
        e.name AS path,
        e.salary
    FROM employees e
    WHERE e.manager_id IS NULL
    
    UNION ALL
    
    -- Parte recursiva: encontrar subordinados
    SELECT 
        e.id,
        e.name,
        e.position,
        e.manager_id,
        oh.level + 1,
        oh.path || ' -> ' || e.name,
        e.salary
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.id
)
SELECT 
    CASE level
        WHEN 0 THEN ''
        WHEN 1 THEN '├─ '
        WHEN 2 THEN '│  ├─ '
        ELSE '│  │  ├─ '
    END || name AS "Organograma",
    position AS "Cargo",
    level AS "Nível",
    path AS "Caminho de Reporte",
    salary AS "Salário"
FROM org_hierarchy
ORDER BY path;

Estatísticas por Nível de Departamento

SQL

-- Estatísticas de hierarquia de pessoal e distribuição salarial por departamento
WITH dept_stats AS (
    SELECT 
        d.id,
        d.department_name,
        COUNT(e.id) AS employee_count,
        COALESCE(SUM(e.salary), 0) AS total_salary,
        COALESCE(AVG(e.salary), 0) AS avg_salary,
        COALESCE(MAX(e.salary), 0) AS max_salary,
        COALESCE(MIN(e.salary), 0) AS min_salary
    FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
    GROUP BY d.id, d.department_name
)
SELECT 
    department_name AS "Departamento",
    employee_count AS "Funcionários",
    ROUND(total_salary, 2) AS "Salário Total",
    ROUND(avg_salary, 2) AS "Salário Médio",
    max_salary AS "Salário Máximo",
    min_salary AS "Salário Mínimo",
    max_salary - min_salary AS "Diferença Salarial",
    -- Distribuição salarial
    CASE 
        WHEN avg_salary > 15000 THEN 'Salário Alto'
        WHEN avg_salary > 10000 THEN 'Salário Médio'
        ELSE 'Salário Base'
    END AS "Nível Salarial"
FROM dept_stats
ORDER BY total_salary DESC;

Prática 4: Lógica de Negócios Complexa

Processamento de Pedidos em Lote (Transação)

SQL

-- Processar pedidos em lote usando transações
BEGIN TRANSACTION;

-- Ponto de salvamento: para fácil reversão
SAVEPOINT before_process;

-- 1. Atualizar inventário de produtos para pedidos concluídos
UPDATE products 
SET stock_quantity = stock_quantity - (
    SELECT COALESCE(SUM(o.quantity), 0)
    FROM orders o
    WHERE o.product_id = products.id
    AND o.status = 'completed'
    AND o.order_date >= '2024-01-01'
)
WHERE id IN (
    SELECT DISTINCT product_id 
    FROM orders 
    WHERE status = 'completed' 
    AND order_date >= '2024-01-01'
);

-- 2. Verificar se algum inventário ficou negativo
SELECT 
    CASE 
        WHEN MIN(stock_quantity) < 0 THEN 'ERRO'
        ELSE 'OK'
    END AS inventory_check
FROM products;

-- 3. Se a verificação de inventário passar, gerar relatório mensal
INSERT INTO monthly_report (month, total_revenue, total_orders)
SELECT 
    strftime('%Y-%m', order_date),
    SUM(amount),
    COUNT(*)
FROM orders
WHERE status = 'completed'
GROUP BY strftime('%Y-%m', order_date);

-- Confirmar a transação
COMMIT;

-- Ver resultados
SELECT * FROM products ORDER BY id;

Análise de Valor do Cliente

SQL

-- Análise abrangente de valor do cliente
WITH customer_analysis AS (
    SELECT 
        customer_name,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spent,
        AVG(amount) AS avg_order_value,
        MIN(order_date) AS first_order,
        MAX(order_date) AS last_order,
        -- Calcular tempo de vida do cliente (dias)
        julianday(MAX(order_date)) - julianday(MIN(order_date)) AS customer_lifetime,
        -- Dias desde a última compra
        julianday('now') - julianday(MAX(order_date)) AS days_since_last_order
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_name
),
customer_rfm AS (
    SELECT 
        *,
        -- Pontuação R: classificação de recência (1-5, 5 = mais recente)
        CASE 
            WHEN days_since_last_order <= 30 THEN 5
            WHEN days_since_last_order <= 60 THEN 4
            WHEN days_since_last_order <= 90 THEN 3
            WHEN days_since_last_order <= 180 THEN 2
            ELSE 1
        END AS r_score,
        -- Pontuação F: classificação de frequência
        CASE 
            WHEN order_count >= 5 THEN 5
            WHEN order_count >= 3 THEN 4
            WHEN order_count >= 2 THEN 3
            WHEN order_count >= 1 THEN 2
            ELSE 1
        END AS f_score,
        -- Pontuação M: classificação de valor monetário
        NTILE(5) OVER (ORDER BY total_spent) AS m_score
    FROM customer_analysis
)
SELECT 
    customer_name AS "Cliente",
    order_count AS "Pedidos",
    ROUND(total_spent, 2) AS "Total Gasto",
    ROUND(avg_order_value, 2) AS "Valor Médio Pedido",
    first_order AS "Primeiro Pedido",
    last_order AS "Último Pedido",
    r_score AS "Pontuação R",
    f_score as "Pontuação F",
    m_score AS "Pontuação M",
    r_score + f_score + m_score AS "Total RFM",
    CASE 
        WHEN r_score + f_score + m_score >= 12 THEN 'Cliente VIP'
        WHEN r_score + f_score + m_score >= 9 THEN 'Cliente Importante'
        WHEN r_score + f_score + m_score >= 6 THEN 'Cliente Regular'
        ELSE 'Cliente de Baixo Valor'
    END AS "Classificação do Cliente"
FROM customer_rfm
ORDER BY r_score + f_score + m_score DESC;

Análise de Faixa Salarial

SQL

-- Relatório de análise de faixa salarial
WITH salary_bands AS (
    SELECT 
        e.id,
        e.name,
        d.department_name,
        e.salary,
        e.hire_date,
        -- Calcular anos de emprego
        (julianday('now') - julianday(e.hire_date)) / 365.25 AS years_employed,
        -- Classificação de faixa salarial
        CASE 
            WHEN e.salary >= 15000 THEN 'Faixa A (15000+)'
            WHEN e.salary >= 12000 THEN 'Faixa B (12000-14999)'
            WHEN e.salary >= 8000 THEN 'Faixa C (8000-11999)'
            WHEN e.salary >= 5000 THEN 'Faixa D (5000-7999)'
            ELSE 'Faixa E (<5000)'
        END AS salary_band
    FROM employees e
    JOIN departments d ON e.department_id = d.id
)
SELECT 
    salary_band AS "Faixa Salarial",
    COUNT(*) AS "Funcionários",
    ROUND(AVG(salary), 2) AS "Salário Médio",
    ROUND(AVG(years_employed), 1) AS "Tempo Médio",
    MIN(salary) AS "Salário Mínimo",
    MAX(salary) AS "Salário Máximo",
    GROUP_CONCAT(name, ', ') AS "Funcionários"
FROM salary_bands
GROUP BY salary_band
ORDER BY salary_band;

❓ Perguntas Frequentes

P: Qual a diferença entre funções de janela e GROUP BY? R: GROUP BY mescla múltiplas linhas em uma (agregação), enquanto funções de janela mantêm todas as linhas originais e simplesmente adicionam resultados calculados a cada linha. Funções de janela são adequadas para cenários onde você precisa ver detalhes e resumos simultaneamente.

P: CTEs recursivas têm limite de profundidade? R: SQLite tem como padrão uma profundidade de recursão de 1000, que pode ser ajustada via sqlite3_limit(db, SQLITE_LIMIT_RECURSION_DEPTH, n). Profundidade de recursão excessiva pode causar problemas de performance; considere otimizar a lógica da consulta.

P: Uma transação reverte automaticamente se ocorrer um erro? R: Não, ela não reverte automaticamente. Você precisa usar explicitamente uma instrução ROLLBACK, ou usar SAVEPOINT e ROLLBACK TO SAVEPOINT para reversão parcial. No SQLite, se estiver usando BEGIN...COMMIT, você precisa reverter manualmente ao encontrar erros.

P: Como posso otimizar a performance de consultas complexas? R: 1) Use EXPLAIN QUERY PLAN para analisar o plano de execução; 2) Crie índices em colunas frequentemente consultadas; 3) Evite usar funções em colunas em cláusulas WHERE; 4) Use CTEs com sabedoria para melhorar a legibilidade; 5) Para resultados consultados frequentemente, considere criar views.

📖 Resumo

Através desta lição prática, dominamos:

Implementação de várias consultas de classificação com funções de janela
Técnicas de cálculos acumulados e implementação de médias móveis
Técnicas de CTE recursivas para lidar com dados hierárquicos
Processamento de transações em lógica de negócios complexa
Análise de valor do cliente (modelo RFM)
Técnicas avançadas de consulta como análise de faixa salarial

📝 Exercícios

Exercício de Classificação: Escreva uma consulta para encontrar os 2 funcionários com maior tempo de serviço em cada departamento, mostrando seu nome, departamento, data de contratação e anos de serviço.
Cálculo Acumulado: Escreva uma consulta para calcular as vendas mensais para cada categoria de produto, mostrando:
- Vendas mensais
- Vendas acumuladas
- Taxa de crescimento mês a mês
- A porcentagem de vendas da categoria em relação ao total de vendas mensais
Consulta Recursiva: Suponha que produtos tenham uma hierarquia de categorias (Eletrônicos -> Acessórios de Computador -> Mouses). Projete uma tabela de categorias e escreva uma consulta recursiva para exibir o caminho completo da categoria.
Exercício Abrangente: Projete uma consulta de "Avaliação de Desempenho de Funcionário" que combine as seguintes métricas:
- Desempenho de vendas (junção com tabela de pedidos)
- Anos de serviço
- Classificação salarial dentro do departamento
- Fornecer uma pontuação geral e recomendação

Próxima Lição → 25-database-design.md

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