SQL Avançado Desmistificado
SQL avançado não é uma linguagem diferente do SQL que você já escreve, é o mesmo modelo declarativo estendido para cobrir problemas que antes forçavam uma ida de volta ao código da aplicação.
CTEs, funções de janela, joins LATERAL e consultas recursivas existem para permitir que uma única instrução descreva lógica que, de outra forma, exigiria loops, tabelas temporárias ou várias idas e vindas ao banco de dados.
Esta página constrói o modelo mental por trás dessas quatro ferramentas para que as páginas dedicadas a CTEs, funções de janela, joins LATERAL e CTEs recursivas sejam lidas como aplicações de uma única ideia, em vez de quatro sintaxes não relacionadas para memorizar.
Resumo
- Recursos avançados de SQL permitem nomear resultados intermediários, computar entre conjuntos de linhas sem colapsá-los, correlacionar subconsultas por linha e iterar até um ponto fixo, tudo dentro de uma única instrução declarativa.
- Por que é Importante: Expressar essa lógica em SQL mantém o trabalho próximo aos dados, evita idas e vindas linha por linha e dá ao planejador de consultas visibilidade total para otimizar toda a computação em conjunto.
- Conceitos Chave: expressão de tabela comum (CTE), função de janela, partição, correlação LATERAL, ponto fixo recursivo.
- Quando Usar: Construir relatórios que classificam ou comparam linhas dentro de grupos, percorrer hierarquias ou grafos, obter um resultado "top N por grupo", ou nomear uma transformação de várias etapas para legibilidade.
- Limitações / Compromissos: Essas ferramentas trocam alguma simplicidade bruta por poder expressivo, e uma CTE mal formatada ou uma consulta recursiva podem ocultar um problema de desempenho que um join simples teria exposto imediatamente.
- Tópicos Relacionados: planejamento de consultas e EXPLAIN, ordenação suportada por índice, views materializadas, pensamento baseado em conjuntos versus loops procedurais.
Fundamentos
Todo recurso avançado de SQL nesta página ainda é apenas uma consulta sobre conjuntos de linhas, a diferença está apenas em quanta estrutura você pode impor a essa computação antes que o resultado final retorne.
Uma CTE (a cláusula WITH) não é nada mais do que uma subconsulta nomeada que você pode referenciar posteriormente na mesma instrução, existindo puramente para tornar uma consulta longa legível, dividindo-a em etapas nomeadas.
Uma função de janela computa um valor em um conjunto de linhas relacionadas, chamado sua partição, sem mesclar essas linhas em uma única linha de saída como o GROUP BY faria.
LATERAL permite que uma subconsulta na cláusula FROM veja colunas de itens FROM que aparecem antes dela, executando efetivamente uma pequena consulta correlacionada uma vez por linha externa, ainda produzindo um único resultado baseado em conjuntos.
Uma CTE recursiva repete uma consulta contra seu próprio resultado crescente até que nenhuma nova linha apareça, que é como o SQL expressa caminhadas de hierarquia e travessia de grafos sem um loop do lado do cliente.
A maneira mais simples de manter as quatro em mente é pensar em uma instrução SQL como uma linha de montagem: CTEs nomeiam as estações, funções de janela adicionam colunas computadas sem quebrar a linha em cintos separados, LATERAL permite que uma estação olhe para trás para a linha de saída de uma estação anterior, linha por linha, e a recursão permite que uma estação realimente sua própria saída até que a linha pare naturalmente.
Mecânicas e Interações
O planejador de consultas processa as cláusulas de uma instrução em uma ordem lógica fixa que é diferente da ordem em que você as digita, e entender essa ordem explica por que as funções de janela podem referenciar resultados de GROUP BY, mas não o contrário.
FROM / JOIN -> WHERE -> GROUP BY -> HAVING
-> funções de janela -> lista SELECT
-> DISTINCT -> ORDER BY -> LIMIT / OFFSETComo as funções de janela são executadas após o agrupamento e a filtragem, mas antes que a lista SELECT final seja montada, elas podem classificar ou comparar linhas já agregadas sem a necessidade de uma segunda passagem pela tabela.
Uma CTE não recursiva não é automaticamente uma "barreira de otimização" como era em versões mais antigas do PostgreSQL; desde o PostgreSQL 12, o planejador pode incorporar uma consulta WITH na instrução circundante exatamente como uma subconsulta, a menos que seja referenciada mais de uma vez ou marcada como MATERIALIZED.
Essa distinção é importante porque uma CTE incorporada permite que o planejador empurre filtros para dentro dela, enquanto uma materializada é computada uma vez e depois lida novamente, o que pode ajudar quando a mesma CTE é reutilizada várias vezes, mas pode prejudicar quando impede o empurrão de filtros.
A correlação LATERAL é mecanicamente semelhante a uma subconsulta por linha, mas como ela reside na cláusula FROM, ela pode retornar várias colunas e várias linhas por linha externa, o que é exatamente o que uma subconsulta correlacionada simples na lista SELECT não pode fazer.
SELECT a.id, recent.total
FROM app.accounts a
JOIN LATERAL (
SELECT total FROM app.orders o
WHERE o.account_id = a.id
ORDER BY o.created_at DESC LIMIT 1
) recent ON true;CTEs recursivas são executadas como um loop de tabela de trabalho: o termo não recursivo semeia o primeiro lote de linhas, o termo recursivo é executado novamente contra apenas as linhas produzidas na iteração anterior, e o loop para no instante em que uma iteração produz zero novas linhas.
Esse loop não tem detecção de ciclo embutida, portanto, uma hierarquia autorreferenciada com um ciclo girará até que você adicione uma guarda explícita, geralmente um array de chaves visitadas verificadas com NOT (id = ANY(path)).
Considerações e Aplicações Avançadas
Funções de janela não têm um tipo de índice dedicado próprio, mas um índice que corresponde às colunas PARTITION BY e ORDER BY permite que o planejador evite uma etapa de ordenação explícita antes de computar a janela, o que geralmente é o maior custo em uma consulta de janela.
CTEs recursivas têm um custo real de materialização por iteração, pois o PostgreSQL precisa armazenar a tabela de trabalho entre as etapas, portanto, uma caminhada de grafo em uma tabela grande e mal delimitada pode consumir muito mais work_mem e tempo do que o loop equivalente em código de aplicação.
O pivoteamento de linhas em colunas pode ser feito com agregação condicional usando FILTER, com crosstab() da extensão tablefunc, ou com jsonb_object_agg() quando as colunas de destino não são conhecidas antecipadamente, e cada uma dessas opções troca a simplicidade de formato fixo por flexibilidade dinâmica de maneiras diferentes.
| Abordagem | Força | Fraqueza | Melhor Ajuste |
|---|---|---|---|
CTE (WITH) | Nomes de etapas intermediárias para legibilidade e reutilização | A materialização pode bloquear o empurrão de filtros se reutilizada ou forçada | Dividir uma consulta longa em estágios nomeados e revisáveis |
| Função de janela | Computa classificações por linha ou valores correntes sem colapsar linhas | Sem tipo de índice dedicado, depende de índices amigáveis à ordenação | Top-N por grupo, totais correntes, comparações linha a linha |
| Join LATERAL | Subconsulta correlacionada que pode retornar múltiplas linhas e colunas por linha externa | Precisa de um índice de suporte na coluna correlacionada ou degrada para loops aninhados em tudo | Consultas "últimas N" por linha que um join simples não pode expressar |
| CTE Recursiva | Expressa travessia de hierarquia e grafo de forma declarativa | Materializa uma tabela de trabalho por iteração, precisa de guardas de ciclo explícitas | Organogramas, listas de materiais, grafos de dependência |
Pivô FILTER | Nenhuma extensão necessária, amigável ao planejador em uma única varredura | O conjunto de colunas deve ser conhecido no momento da consulta | Pequenos conjuntos fixos de colunas de pivô |
Muitas equipes recorrem a loops do lado da aplicação por hábito, mesmo depois que uma consulta poderia expressar a mesma lógica declarativamente, e o compromisso honesto é que as versões SQL desses padrões são menos familiares para ler rapidamente, mas evitam a latência de enviar linhas de volta e para frente entre o banco de dados e a camada de aplicação.
Equívocos Comuns
- "Toda CTE é uma barreira de otimização." Isso era verdade antes do PostgreSQL 12, mas uma consulta
WITHsimples hoje é incorporada como uma subconsulta, a menos que seja referenciada várias vezes ou explicitamente marcada comoMATERIALIZED. - "Funções de janela precisam de
GROUP BYpara funcionar." Uma função de janela pode ser executada em todo o conjunto de resultados sem nenhumGROUP BY, poisPARTITION BYdentro da cláusulaOVERdefine seu próprio agrupamento independente doGROUP BYda consulta. - "LATERAL é apenas uma subconsulta correlacionada." Uma subconsulta correlacionada na lista
SELECTsó pode retornar um valor escalar por linha externa, enquantoLATERALna cláusulaFROMpode retornar várias linhas e colunas por linha externa. - "CTEs recursivas usam recursão real como uma pilha de chamadas de função." O PostgreSQL as avalia iterativamente como um loop de ponto fixo sobre uma tabela de trabalho, não como chamadas de função aninhadas, razão pela qual a recursão descontrolada aparece como uma tabela de trabalho em constante crescimento em vez de um estouro de pilha.
- "Pivoteamento sempre requer a extensão
tablefunc." A agregação condicional comFILTERlida com colunas de pivô fixas e conhecidas sem instalar nada extra.
FAQs
Qual é a diferença real entre uma CTE e uma subconsulta?
Uma CTE é uma subconsulta nomeada declarada em uma cláusula WITH, e desde o PostgreSQL 12, o planejador trata uma CTE referenciada uma única vez, não recursiva, exatamente como uma subconsulta inline, a menos que você force a materialização.
Quando devo forçar uma CTE a materializar?
- Quando a mesma CTE é referenciada várias vezes e recompô-la seria um desperdício
- Quando você deseja um limite de otimização deliberado para estabilizar um plano
- Quando a CTE executa um
INSERT/UPDATE/DELETEcom efeito colateral que deve ser executado exatamente uma vez
As funções de janela são executadas antes ou depois de `GROUP BY`?
Funções de janela são executadas após GROUP BY e HAVING, mas antes que a lista SELECT final seja montada, então elas podem operar em linhas já agrupadas.
Posso filtrar diretamente no resultado de uma função de janela?
Não no mesmo SELECT, pois as funções de janela são computadas após WHERE, então você precisa envolver a consulta em uma CTE ou subconsulta e filtrar a consulta externa em vez disso.
Por que minha consulta de função de janela parece lenta mesmo em uma tabela pequena?
Geralmente é a ordenação por trás de PARTITION BY/ORDER BY, e não a computação da janela em si, e um índice que corresponde a essas colunas muitas vezes remove essa ordenação completamente.
Como o LATERAL é diferente de um JOIN normal?
Uma condição de JOIN normal não pode referenciar colunas computadas dentro da subconsulta unida, enquanto LATERAL permite que a subconsulta à direita veja colunas de itens FROM à sua esquerda, avaliada uma vez por linha externa.
Qual é um caso de uso realista para LATERAL?
Obter o único pedido mais recente de cada conta, ou seus três principais pedidos, em uma única consulta em vez de executar uma consulta separada para cada conta a partir do código da aplicação.
Por que minha CTE recursiva nunca para?
- O termo recursivo está produzindo novas linhas a cada iteração, muitas vezes porque os dados de origem realmente contêm um ciclo
- Não há uma guarda explícita rastreando linhas visitadas
- Corrija isso carregando um array de caminho visitado e verificando
NOT (id = ANY(path))antes de recursar mais
Uma CTE recursiva é o mesmo que recursão em uma linguagem de programação?
Não, é um loop iterativo de ponto fixo sobre uma tabela de trabalho, avaliado pelo executor etapa por etapa em vez de através de chamadas de função aninhadas.
Preciso da extensão `tablefunc` para pivotar dados?
Apenas quando as colunas de pivô não são conhecidas antecipadamente ou você deseja a conveniência de crosstab(), pois a agregação condicional com FILTER lida com conjuntos de colunas fixos sem nenhuma extensão.
CTEs e funções de janela podem ser combinadas?
Sim, e é um padrão comum computar uma função de janela dentro de uma CTE e, em seguida, filtrar ou unir contra seu resultado na consulta externa, pois as funções de janela não podem ser filtradas no mesmo SELECT em que aparecem.
Qual é o maior erro de desempenho com esses recursos?
Assumir que nomear algo em uma cláusula WITH o torna automaticamente mais rápido, quando na realidade o comportamento de incorporação do planejador significa que o desempenho de uma CTE é geralmente idêntico a escrever a mesma lógica como uma subconsulta simples.
Devo sempre preferir esses recursos SQL em vez de loops do lado da aplicação?
Prefira-os quando a lógica for naturalmente baseada em conjuntos e, de outra forma, custaria várias idas e vindas, mas mantenha a lógica de negócios genuinamente procedural e ramificada no código da aplicação, onde é mais fácil testar e raciocinar sobre ela.
Relacionados
- Noções Básicas de SQL Avançado - exemplos práticos de CTEs, funções de janela e LATERAL
- Expressões de Tabela Comuns - sintaxe de CTE, nomenclatura e materialização em profundidade
- Funções de Janela - a sintaxe completa
OVER,PARTITION BYe frame - CTEs Recursivas - caminhadas de hierarquia e grafo com guardas de ciclo
- Joins LATERAL - subconsultas correlacionadas por linha na cláusula
FROM - Noções Básicas de EXPLAIN - lendo o plano por trás de qualquer uma dessas consultas
Versões do Stack: Esta página foi escrita para PostgreSQL 18.4 (linha estável 18, linha de manutenção 17), onde o comportamento de incorporação de CTE introduzido no PostgreSQL 12 e os mecanismos de função de janela e consulta recursiva descritos aqui permanecem atuais.