← voltar aos artigos
19/07/2026SQL · PostgreSQL · Performance · Data Engineering

Dominando SQL Complexo: Técnicas Avançadas de Otimização, Janelas e Modelagem de Dados

Window functions, CTEs recursivas, subqueries correlacionadas, pivot condicional e EXPLAIN ANALYZE, padrões avançados de SQL para analytics e produção em PostgreSQL, MySQL 8+ e SQL Server.

À medida que o volume de dados cresce e os sistemas de análise se tornam mais exigentes, a escrita de queries relacionais exige mais do que simples cláusulas SELECT... WHERE. Em bancos de dados de produção escaláveis, como PostgreSQL, MySQL 8.0+ e SQL Server, gargalos surgem quase sempre por varreduras completas de tabelas (table scans), junções ineficientes ou processamento inadequado na memória.

Este artigo disseca padrões avançados de SQL utilizados para engenharia de dados, relatórios complexos e otimização de performance, com exemplos baseados em dados transacionais do mundo real.

1. Window Functions (funções de janela)

Ao contrário das agregações tradicionais com GROUP BY, que colapsam linhas em um único resultado, as window functions realizam cálculos em um conjunto de linhas relacionadas à linha atual, mantendo a identidade e a granularidade de cada registro.

Cenário prático: média móvel e análise detalhada de vendas

Imagine uma tabela de faturamento onde precisamos calcular, para cada dia, a receita atual, a soma acumulada do mês corrente e a média móvel de faturamento dos últimos 3 dias, uma métrica crucial para suavizar picos sazonais.

sql
SELECT
  id,
  user_id,
  created_at::date AS data_pedido,
  total_price,

  -- 1. Soma acumulada por usuário ordenada por data
  SUM(total_price) OVER (
    PARTITION BY user_id
    ORDER BY created_at
  ) AS total_acumulado_usuario,

  -- 2. Média móvel dos últimos 3 dias (linha atual + 2 anteriores)
  AVG(total_price) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS media_movel_3_dias
FROM orders
WHERE status = 'completed';

Explicação interna do mecanismo

  • ▸PARTITION BY user_id: segrega o universo de cálculo em grupos isolados por cliente.
  • ▸ORDER BY created_at: define a direção temporal do cálculo dentro da janela.
  • ▸ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: delimita o frame físico da query. O motor não olha para a tabela inteira, mas para um buffer restrito a 3 linhas por iteração, otimizando RAM e reduzindo spill to disk.

2. Common Table Expressions (CTEs) e recursividade

As CTEs (WITH) funcionam como tabelas temporárias nomeadas que existem apenas durante a execução da query. Elas aumentam a legibilidade e permitem resolver hierarquias por meio de estruturas recursivas.

Cenário prático: árvore genealógica de categorias de produtos

Em e-commerces ou ERPs, categorias possuem subcategorias em níveis infinitos (Eletrônicos → Computadores → Laptops → Acessórios). Para descobrir o caminho completo de uma subcategoria até a raiz (o breadcrumb), recorremos a uma CTE recursiva.

sql
WITH RECURSIVE category_tree AS (
  -- Âncora: seleciona a categoria folha desejada
  SELECT
    id,
    name,
    parent_id,
    1 AS level,
    name::text AS path
  FROM categories
  WHERE id = 42  -- ID da subcategoria específica

  UNION ALL

  -- Membro recursivo: sobe na árvore
  SELECT
    c.id,
    c.name,
    c.parent_id,
    t.level + 1,
    (c.name || ' > ' || t.path) AS path
  FROM categories c
  INNER JOIN category_tree t ON c.id = t.parent_id
)
SELECT id, name, level, path
FROM category_tree
ORDER BY level DESC;

3. Subqueries avançadas e cláusulas correlacionadas

Uma subquery é dita correlacionada quando a consulta interna depende explicitamente de valores passados pela consulta externa. Embora frequentemente acusadas de lentidão, elas são extremamente poderosas quando combinadas com EXISTS e NOT EXISTS para filtragens cirúrgicas.

Cenário prático: identificação de churn (clientes inativos)

Precisamos listar todos os clientes que fizeram compras acima de R$ 500,00 no passado, mas que não realizaram nenhum pedido nos últimos 60 dias.

sql
SELECT u.id, u.name, u.email
FROM users u
WHERE EXISTS (
  -- Subquery correlacionada 1: teve compras grandes no passado?
  SELECT 1
  FROM orders o1
  WHERE o1.user_id = u.id
    AND o1.total_price > 500
)
AND NOT EXISTS (
  -- Subquery correlacionada 2: comprou recentemente?
  SELECT 1
  FROM orders o2
  WHERE o2.user_id = u.id
    AND o2.created_at >= CURRENT_DATE - INTERVAL '60 days'
);

Por que usar EXISTS em vez de LEFT JOIN / IS NULL?

O operador EXISTS utiliza short-circuit evaluation: assim que o motor encontra a primeira linha correspondente na subquery, interrompe a varredura daquele ID e retorna TRUE. Um LEFT JOIN processaria todas as linhas correspondentes da tabela filha para só depois aplicar WHERE ... IS NULL, gerando desperdício de I/O.

4. Agregações condicionais e pivot tables dinâmicas

Transformar linhas em colunas diretamente na engine do banco poupa processamento na camada da aplicação (Ruby, Node, Python) e reduz consideravelmente o tráfego de rede.

Cenário prático: matriz de faturamento mensal por categoria

Extraia um relatório consolidando o faturamento total de categorias específicas mapeadas lado a lado pelos meses do ano:

sql
SELECT
  c.name AS categoria,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM o.created_at) = 1 THEN oi.quantity * oi.price_at_purchase END), 0) AS janeiro,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM o.created_at) = 2 THEN oi.quantity * oi.price_at_purchase END), 0) AS fevereiro,
  COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM o.created_at) = 3 THEN oi.quantity * oi.price_at_purchase END), 0) AS marco,
  SUM(oi.quantity * oi.price_at_purchase) AS total_anual
FROM line_items oi
INNER JOIN orders o ON oi.order_id = o.id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN categories c ON p.category_id = c.id
WHERE o.status = 'completed'
  AND o.created_at >= '2026-01-01'
GROUP BY c.name
ORDER BY total_anual DESC;

5. Anatomia da otimização: o plano de execução (EXPLAIN ANALYZE)

Nenhuma query complexa está pronta até que seu plano de execução seja validado. O comando EXPLAIN ANALYZE executa a query e exibe o mapa de decisões tomado pelo otimizador de consultas (query planner).

Ao analisar a saída, rastreie três grandes vilões:

  • ▸Seq Scan (Sequential Scan): o banco leu a tabela inteira do disco sequencialmente. Corrija adicionando índices adequados nas colunas do WHERE e do JOIN.
  • ▸Nested Loop: o banco executa um laço para cada linha da tabela A sobre a tabela B. Aceitável para poucos registros, catastrófico para milhões. Pode ser atenuado atualizando estatísticas para que o planner mude para Hash Join.
  • ▸External Sort Disk: ocorre quando work_mem (PostgreSQL) é menor que o volume de dados do ORDER BY. O banco grava temporariamente no disco, derrubando a performance em ordens de magnitude.

Exemplo de otimização por índices compostos

Se a aplicação busca frequentemente pedidos concluídos de um usuário específico ordenados pela data, um índice simples em user_id não basta. Um índice composto permite index scan direto, localizando as linhas exatas e evitando ordenação em memória:

sql
CREATE INDEX idx_orders_user_status_date
ON orders (user_id, status, created_at DESC);

Conclusão

SQL avançado não é sinônimo de query incompreensível. Window functions, CTEs recursivas, subqueries correlacionadas com EXISTS e agregações condicionais resolvem problemas reais de analytics e operação, desde que validados com EXPLAIN ANALYZE e índices alinhados ao padrão de acesso da aplicação.

  • ▸Use window functions quando precisar de agregações sem perder a granularidade da linha.
  • ▸Prefira CTEs recursivas para hierarquias; indexe as colunas de ligação.
  • ▸EXISTS costuma ser mais eficiente que LEFT JOIN ... IS NULL para verificação de existência.
  • ▸Pivot condicional no banco reduz tráfego de rede e processamento na aplicação.
  • ▸Valide todo plano de execução antes de promover queries complexas para produção.

Referências e links acadêmicos

✓ EOF, Israel Santos

← voltar aos artigos