Aula 11: CTEs — WITH queries e WITH RECURSIVE no PostgreSQL

Nesta aula, vamos explorar um recurso essencial no PostgreSQL conhecido como CTEs (Common Table Expressions). Vamos entender como as CTEs podem ser utilizadas para tornar suas consultas mais legíveis e eficientes, especialmente em cenários complexos. Entender o uso de CTEs é crucial para qualquer profissional que pretenda se aprimorar no uso do PostgreSQL.

O que você vai aprender nesta aula

• Entender o que são CTEs e como elas funcionam no PostgreSQL
• Aprender a escrever consultas com WITH queries
• Explorar o uso de WITH RECURSIVE para criar consultas recursivas
• Examinar casos práticos de uso de CTEs para resolver problemas complexos

Introdução às CTEs no PostgreSQL

As Common Table Expressions, ou CTEs, são uma forma poderosa de estruturar consultas SQL de forma mais clara e organizada. No PostgreSQL, as CTEs são definidas usando a cláusula WITH. Elas permitem dividir uma consulta em partes menores e mais fáceis de entender, especialmente útil quando lidamos com várias subconsultas.

Prática com CTEs: Criando Consultas com WITH

Para começar a utilizar CTEs, vamos explorar um exemplo simples onde usamos a cláusula WITH para calcular o total de vendas de um produto específico em nosso sistema.

WITH sales_by_product AS (
  SELECT product_id, SUM(amount) AS total_sales
  FROM sales
  GROUP BY product_id
)
SELECT products.name, sales_by_product.total_sales
FROM sales_by_product
JOIN products ON sales_by_product.product_id = products.id
WHERE products.name = 'Produto X';

Neste exemplo, uma subconsulta é definida no CTE sales_by_product, que calcula as vendas totais por produto. A consulta principal então utiliza essa tabela temporária para retornar o nome do produto e seu total de vendas. Esse método torna o SQL mais fácil de ler e mantém a lógica da subconsulta separada.

Explorando WITH RECURSIVE para consultas recursivas

O PostgreSQL também suporta CTEs recursivas através do uso de WITH RECURSIVE. Esse tipo de CTE é útil para resolver problemas em que uma hierarquia ou relação recursiva precisa ser navegada, como em estruturas de lista de tarefas ou organogramas.

WITH RECURSIVE employee_hierarchy AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  JOIN employee_hierarchy eh ON eh.employee_id = e.manager_id
)
SELECT * FROM employee_hierarchy;

Neste exemplo, estamos construindo uma hierarquia de funcionários a partir de uma tabela chamada employees. Primeiramente, selecionamos todos os funcionários sem um gerente (o início da recursão). Em seguida, utilizamos uma UNION ALL para continuar a consulta unindo os funcionários ao seu gerente, criando assim a hierarquia.

Vantagens de usar CTEs em consultas avançadas

As CTEs são extremamente úteis em situações onde você precisa de:

• Dividir consultas complexas em partes gerenciáveis e mais fáceis de entender
• Evitar a repetição de lógica ao reusar resultados intermediários em várias partes da consulta
• Substituir subconsultas nomeadas, melhorando a legibilidade

Em nossos projetos na JRT Technology Solutions, utilizamos CTEs para otimizar a estrutura de consultas complexas e melhorar a performance de sistemas legados.

Resumo da Aula 11

Hoje, aprendemos a utilizar CTEs no PostgreSQL para estruturar melhor nossas consultas, principalmente em cenários complexos. Exploramos exemplos práticos usando WITH e WITH RECURSIVE e discutimos as vantagens das CTEs em relação a outras estruturas de consultas. Na próxima aula, vamos mergulhar na otimização de consultas no PostgreSQL, abordando índices e planos de execução para atingir a máxima eficiência.