A linguagem SQL é indispensável para análise e desenvolvimento de dados. Este guia abrange operações essenciais e recursos avançados, como funções de janela, com exemplos práticos.

1. Inserção de Dados com Tratamento de Duplicatas

---

Ao inserir dados, existem duas abordagens para lidar com registros que possam conflitar em uma chave primária ou índice único.

INSERT IGNORE INTO: Tenta inserir o registro. Se houver um conflito, a operação é silenciosamente ignorada e a linha existente permanece inalterada.

REPLACE INTO: Tenta inserir o registro. Se um conflito for detectado, a linha existente é excluída e a nova linha é inserida em seu lugar.

Escolha: Use INSERT IGNORE para evitar erros em operações em lote onde a existência é incerta. Prefira REPLACE quando a intenção for garantir que os dados da nova versão prevaleçam.

2. Funções de Manipulação de Strings

---

SQL fornece uma sintaxe rica para transformar dados de texto.

Comprimento e Substituição

• LENGTH(texto): Retorna o número de caracteres da string.
• REPLACE(texto_original, 'trecho_antigo', 'trecho_novo'): Substitui todas as ocorrências de um substring.

Exemplo:

-- Substitui barras por hífens em uma coluna de data.
SELECT REPLACE(data_registro, '/', '-') AS data_formatada
FROM pedidos;

Extração de Substrings

Para extrair partes de uma string, use SUBSTRING ou seus atalhos.

-- Extrai os primeiros 5 caracteres.
SELECT SUBSTRING(codigo_produto, 1, 5) FROM estoque;
-- Extrai os 3 últimos caracteres (ex.: extensão de arquivo).
SELECT RIGHT(nome_arquivo, 3) AS extensao FROM documentos;

3. Lógica Condicional com CASE

---

A expressão CASE permite criar lógica condicional dentro de uma consulta, semelhante a blocos if-else.

Sintaxe de Pesquisa (mais comum)

CASE
    WHEN <condição_1> THEN <resultado_1>
    WHEN <condição_2> THEN <resultado_2>
    ...
    ELSE <resultado_padrao>
END

Exemplo Prático: Categorizar produtos por faixa de preço.

SELECT
    nome,
    preco,
    CASE
        WHEN preco > 500 THEN 'Premium'
        WHEN preco BETWEEN 100 AND 500 THEN 'Médio'
        ELSE 'Básico'
    END AS categoria_preco
FROM produtos;

4. Consultas por Existência com EXISTS

---

O operador EXISTS verifica se uma subconsulta retorna alguma linha, frequentemente oferecendo melhor desempenho que IN para conjuntos de dados grandes.

-- Encontrar clientes que NÃO fizeram pedidos no último mês.
SELECT c.id, c.nome
FROM clientes c
WHERE NOT EXISTS (
    SELECT 1
    FROM pedidos p
    WHERE p.cliente_id = c.id
      AND p.data_pedido >= '2024-01-01'
);

5. Análise Avançada com Funções de Janela

---

Funções de janela (OVER()) realizam cálculos em um conjunto de linhas definido (a "janela"), mantendo a granularidade original dos dados, diferentemente do GROUP BY.

Estrutura Fundamental

FUNÇÃO() OVER (
    [PARTITION BY coluna_partição]
    [ORDER BY coluna_ordem]
    [ROWS | RANGE entre_linhas]
)

• PARTITION BY: Divide os dados em grupos independentes. A função é recalculada para cada partição.
• ORDER BY: Define a ordem das linhas dentro de cada partição, crucial para cálculos sequenciais.
• ROWS/RANGE: Especifica o quadro da janela (ex.: "3 linhas anteriores até a linha atual").

Categorias de Funções de Janela

a) Funções de Agregação como Janela

Calculam uma agregação (soma, média) sobre a janela, anexando o resultado a cada linha da partição.

-- Mostra o salário de cada funcionário e a média salarial do seu departamento.
SELECT
    funcionario,
    departamento,
    salario,
    AVG(salario) OVER (PARTITION BY departamento) AS media_departamento
FROM folha_pagamento;

b) Funções de Classificação (Ranking)

Atribuem uma ordem ou posição às linhas dentro da janela.

• ROW_NUMBER(): Atribui um número de linha único e sequencial, mesmo com empates.
• RANK(): Atribui o mesmo rank para empates, pulando o próximo número.
• DENSE_RANK(): Atribui o mesmo rank para empates, sem pular números.

-- Classificar alunos por nota, demonstrando diferentes funções de ranking.
SELECT
    aluno,
    nota,
    ROW_NUMBER() OVER (ORDER BY nota DESC) AS num_linha,
    RANK() OVER (ORDER BY nota DESC) AS ranking,
    DENSE_RANK() OVER (ORDER BY nota DESC) AS ranking_denso
FROM provas;

c) Funções de Deslocamento (Offset)

Acessam valores de outras linhas na janela, relativas à linha atual.

• LAG(coluna, N): Acessa o valor de N linhas antes da linha atual.
• LEAD(coluna, N): Acessa o valor de N linhas depois da linha atual.

-- Calcular a variação diária de vendas em relação ao dia anterior.
SELECT
    data_venda,
    valor_total,
    LAG(valor_total, 1) OVER (ORDER BY data_venda) AS venda_dia_anterior,
    valor_total - LAG(valor_total, 1) OVER (ORDER BY data_venda) AS variacao
FROM vendas_diarias;

Cenários Clássicos de Aplicação

Soma Acumulada

-- Mostrar o saldo acumulado de uma conta.
SELECT
    data,
    valor,
    SUM(valor) OVER (ORDER BY data) AS saldo_acumulado
FROM transacoes;

Média Móvel (ex.: janela de 7 dias)

-- Suavizar dados de vendas usando uma média móvel de 7 dias.
SELECT
    dia,
    receita,
    AVG(receita) OVER (
        ORDER BY dia
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS media_movel_7d
FROM metricas_diarias;

Obter o Registro Mais Recente por Grupo

-- Encontrar a última transação de cada cartão de crédito.
WITH transacoes_ranqueadas AS (
    SELECT
        cartao_id,
        valor,
        data_transacao,
        ROW_NUMBER() OVER (PARTITION BY cartao_id ORDER BY data_transacao DESC) AS rn
    FROM transacoes
)
SELECT cartao_id, valor, data_transacao
FROM transacoes_ranqueadas
WHERE rn = 1;

6. Diferença Crucial: Soma Cumulativa vs. Soma Agrupada

---

Um erro comum é confundir SUM(col) OVER (ORDER BY ...) com SUM(col) GROUP BY ....

Soma Cumulativa (OVER): Retorna o mesmo número de linhas da tabela original. Cada linha contém a soma acumulada até aquele ponto. Ideal para ver tendências.

Soma Agrupada (GROUP BY): Reduz o número de linhas, retornando uma linha por grupo com a soma total desse grupo. Ideal para resumos.

7. Nota sobre GROUP_CONCAT (MySQL)

---

No MySQL, ao usar GROUP_CONCAT() para concatenar valores de um grupo em uma string, tenha cuidado com os apelidos (AS). Evite aspas simples no apelido. Prefira não usar delimitadores ou usar crases (```).

-- Concatenar categorias de produto para um fornecedor.
SELECT
    fornecedor_id,
    GROUP_CONCAT(DISTINCT categoria ORDER BY categoria SEPARATOR ', ') AS categorias
FROM produtos
GROUP BY fornecedor_id;