Noções Básicas de SQL Avançado

9 exemplos para você começar com SQL avançado - 6 básicos e 3 intermediários.

Pré-requisitos

Conforto com JOIN e GROUP BY da seção Consultas SQL.

# Desenvolvimento local com Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Exemplos Básicos

1. Pipeline CTE

Nomeie etapas intermediárias.

WITH recent AS (
  SELECT * FROM app.orders WHERE created_at > now() - interval '7 days'
)
SELECT account_id, count(*) FROM recent GROUP BY 1;

• Melhora a legibilidade
• O planejador pode embutir ou materializar
• Não é uma varinha mágica de performance

2. Número de Linha de Janela

Classifique sem o colapso do GROUP BY.

SELECT id, account_id,
  row_number() OVER (PARTITION BY account_id ORDER BY created_at DESC) AS rn
FROM app.orders;

• PARTITION BY define os grupos
• ORDER BY em OVER define a classificação
• Filtre rn na consulta externa

3. LAG para Linha Anterior

Compare com o evento anterior por partição.

SELECT id, total,
  lag(total) OVER (PARTITION BY account_id ORDER BY id) AS prev_total
FROM app.orders;

• LAG/LEAD comuns em análise
• O frame padrão é RANGE UNBOUNDED PRECEDING
• NULL quando não há linha anterior

4. FILTER em Agregados

Contagens condicionais em uma única passagem.

SELECT account_id,
  count(*) FILTER (WHERE total > 100) AS big_orders
FROM app.orders GROUP BY 1;

• Mais limpo que SUM(CASE)
• Funciona com muitos agregados
• A mesma consulta escaneia uma vez

5. Formatação Legível

Alinhe CTEs e JOINs.

WITH open_orders AS (
  SELECT * FROM app.orders WHERE status = 'open'
)
SELECT a.name, o.total
FROM open_orders o
JOIN app.accounts a ON a.id = o.account_id;

• Revisores detectam fan-out cedo
• Mantenha as linhas abaixo da largura da equipe
• Armazene em arquivos .sql

6. Intenção de Comentário

Comentários SQL para regras de negócio não óbvias.

-- Receita reconhecida apenas para pedidos enviados
SELECT sum(total) FROM app.orders WHERE status = 'shipped';

• Comentários sobrevivem no histórico de migração
• Não comente SQL óbvio
• Linke IDs de tickets com moderação

Exemplos Intermediários

7. EXPLAIN Consulta Complexa

Linha de base antes do ajuste.

EXPLAIN (ANALYZE, BUFFERS)
WITH x AS (SELECT account_id, sum(total) s FROM app.orders GROUP BY 1)
SELECT * FROM x WHERE s > 1000;

• Verifique os nós de ordenação e agregação hash
• work_mem descarrega para disco se for muito baixo
• Compare após alterações de índice

8. LATERAL Preview

Subconsulta correlacionada por linha.

SELECT a.id, latest.total
FROM app.accounts a
JOIN LATERAL (
  SELECT total FROM app.orders o
  WHERE o.account_id = a.id ORDER BY created_at DESC LIMIT 1
) latest ON true;

• LATERAL é poderoso e preciso
• Precisa de índice em (account_id, created_at)
• Veja o artigo dedicado sobre LATERAL

9. Pivot com FILTER

Agregação condicional pivotada.

SELECT account_id,
  sum(total) FILTER (WHERE status='open') AS open_total,
  sum(total) FILTER (WHERE status='closed') AS closed_total
FROM app.orders GROUP BY 1;

• Evita crosstab para poucas colunas
• Colunas dinâmicas precisam de SQL dinâmico ou crosstab
• Veja o artigo sobre pivot

Versões do Stack: Esta página foi escrita para PostgreSQL 18.4 (estável 18, manutenção 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x e PostGIS 3.5+.