SQL avançado — window functions e análise de dados

SQL é uma das linguagens mais subestimadas na stack moderna. A maioria das pessoas aprende SELECT, INSERT, JOIN básico — e pára aí. Mas o SQL moderno (SQL:2003 e posterior) tem ferramentas que tornam análises complexas elegantes e que seriam dolorosas em código aplicacional.

Window Functions — o feature mais poderoso

Window functions calculam agregações sobre um conjunto de linhas relacionadas (a "janela") sem colapsar as linhas como um GROUP BY faz. A sintaxe base é:

SELECT
  id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY date) AS running_total
FROM orders;

Isto devolve todas as linhas, cada uma com o total acumulado de compras do seu cliente até àquela data. Com GROUP BY, perderíamos o detalhe de cada linha.

Funções de ranking

• ROW_NUMBER(): número sequencial único por partição — útil para pegar o registo mais recente por grupo
• RANK(): ranking com gaps quando há empates (1, 1, 3, 4)
• DENSE_RANK(): ranking sem gaps (1, 1, 2, 3)
• NTILE(n): divide as linhas em n grupos iguais — perfeito para percentis
• LAG() / LEAD(): acede ao valor da linha anterior/seguinte — essencial para análise de variações

Exemplo real: encontrar a variação de vendas em relação ao mês anterior sem self-join:

SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS delta
FROM monthly_sales;

CTEs — queries legíveis e manuteníveis

Common Table Expressions (WITH) permitem nomear subqueries intermédias, tornando queries complexas legíveis como código. As CTEs recursivas são especialmente poderosas para dados hierárquicos:

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY depth, name;

EXPLAIN ANALYZE — antes de optimizar qualquer coisa

Antes de optimizar qualquer query, EXPLAIN ANALYZE. Sem excepções. Os problemas mais comuns são: sequential scans onde podia haver index scans, joins na ordem errada (o optimizador nem sempre acerta), e N+1 implícitos em subqueries correlacionadas.

Aprende a ler um plano de execução: o custo estimado (nem sempre preciso), o custo real (após ANALYZE), e onde estão os nós mais caros. Um index mal escolhido pode ser pior do que nenhum index — o planner pode fazer full scan porque o index tem baixa selectividade.