CTE do PostgreSQL: o que é e como usá-lo

Os CTEs, ou Common Table Expressions (expressões comuns de tabela), são uma ferramenta poderosa do PostgreSQL que é frequentemente ignorada. Este artigo examina vários CTEs do PostgreSQL PostgreSQL - incluindo CTEs aninhados e recursivos - e o que você pode fazer com eles.

Se você escrever consultas complexas em SQL, logo perceberá que seu código se tornará confuso e difícil de ler. Os CTEs, também conhecidos como cláusulas WITH, são principalmente uma forma de simplificar as consultas. Entretanto, eles também permitem que você use a recursão. A recursão, entre outros usos, permite que você navegue facilmente por estruturas hierárquicas.

Os CTEs (ou Common Table Expressions) do PostgreSQL são muito semelhantes às subconsultas; a diferença é que os CTEs são nomeados e definidos na parte superior do seu código. Isso permite que você divida uma consulta grande em pequenas seções.

Neste artigo, apresentarei vários exemplos de CTEs do PostgreSQL. Presumo que você já esteja familiarizado com a escrita de consultas no PostgreSQL. Caso contrário, vale a pena fazer o download da nossaFolha de dicas do PostgreSQL .

Se você acha que os CTEs do PostgreSQL o ajudarão em seu trabalho, talvez queira dar uma olhada em nosso curso interativo Common Table Expressions in PostgreSQL. Esse curso foi desenvolvido para aqueles que já estão familiarizados com o SQL básico. Você terá muita prática no uso de CTEs do PostgreSQL, graças a mais de cem exercícios interativos.

Vamos nos aprofundar nas expressões de tabela comuns no Postgres!

Expressões de tabela comuns no PostgreSQL

Sintaxe do CTE no PostgreSQL

Vamos agora dar uma olhada mais de perto na sintaxe do CTE. Em sua forma mais simples, ela tem a seguinte aparência:

WITH cte_name AS (query_1)
query_2;

• cte_name é o nome que você atribui ao CTE. Você pode se referir a esse nome em sua consulta principal ou em subconsultas, da mesma forma que faria com uma tabela.
• query_1 é qualquer SELECT válido
• query_2 é uma instrução SQL válida. Pode ser um SELECT, um UPDATE, um INSERT ou um DELETE.

Os resultados de query_1 estarão disponíveis como se fossem uma tabela. O nome da tabela será o nome que você especificou como cte_name. Você pode usá-la no restante de sua consulta da mesma forma que usa outras tabelas.

Exemplo de CTE no PostgreSQL

Vamos dar uma olhada em um exemplo. A Alpha Sales é uma varejista on-line. Eles querem saber se sua estratégia de marketing mais recente foi eficaz e que tipo de cliente respondeu melhor a ela.

Aqui está uma amostra de sua tabela order_summary que contém o valor de cada pedido feito em abril, maio e junho de 2024.

Como primeira etapa da análise do sucesso de sua campanha de marketing, os líderes da empresa querem comparar as vendas de junho por cliente com a média mensal de vendas por cliente de abril e maio e calcular a variação percentual.

É claro que você poderia fazer isso usando subconsultas, mas o código seria bastante complexo. Você deseja ver a média do mês anterior no relatório, mas também usá-la no cálculo da variação percentual.

Usando um CTE, a consulta teria a seguinte aparência:

WITH april_may_sales AS
(SELECT 
   customer_id, 
   SUM(value) / 2 AS prev_avg
 FROM order_summary 
 WHERE EXTRACT (MONTH FROM order_date) in (4,5)
 GROUP BY customer_id;
)
SELECT 
  order_summary.customer_id, 
  prev_avg, 
  SUM(value) AS jun_total, 
  (SUM(value) - prev_avg) * 100 / prev_avg AS percent_change
FROM order_summary  
JOIN april_may_sales
ONapril_may_sales.customer_id = order_summary.customer_id
WHERE EXTRACT (MONTH FROM order_date) = 6
GROUP BY order_summary.customer_id, prev_avg
ORDER BY customer_id;

Essa consulta usa a cláusula WITH para criar uma tabela virtual chamada april_may_sales. Ela extrai o total de vendas por cliente para os meses de abril e maio, divide o resultado por 2 para obter uma média mensal e armazena essas informações em uma coluna chamada prev_avg.

Essa tabela é unida à tabela order_summary na consulta principal para que possamos ver o total de junho junto com a média de abril e maio.

A consulta produz o seguinte conjunto de resultados:

Definição de nomes de colunas CTE

Opcionalmente, você pode definir especificamente os nomes das colunas para a tabela CTE usando a seguinte sintaxe:

WITH cte_name (column_name_list)
AS (query_1)
query_2;

Aqui, column_name_list é uma lista de nomes de colunas separados por vírgulas.

Alterar o exemplo anterior para usar essa sintaxe nos dá a seguinte consulta:

WITH april_may_sales (customer_id, prev_avg)
AS (
  SELECT 
 	customer_id, 
      SUM(value) /2 
  FROM order_summary 
  WHERE EXTRACT (MONTH FROM order_date) in (4,5)
  GROUP BY customer_id
)
SELECT 
order_summary.customer_id,
prev_avg, 
SUM(value) AS jun_total, 
(SUM(value) - prev_avg) * 100/prev_avg AS percent_change
FROM order_summary  
JOIN april_may_sales
ON april_may_sales.customer_id = order_summary.customer_id
WHERE EXTRACT (MONTH FROM order_date) = 6
GROUP BY order_summary.customer_id, prev_avg
ORDER BY customer_id;

Isso não faz diferença na saída da consulta, que permanece igual à consulta de exemplo anterior. No entanto, torna mais fácil para outra pessoa entender sua consulta.

Consultas aninhadas usando o CTE do PostgreSQL

Você pode definir dois ou mais CTEs usando uma palavra-chave WITH no PostgreSQL. Você simplesmente começa usando a palavra-chave WITH e, em seguida, especifica cada CTE separado por vírgulas. A sintaxe é semelhante a esta:

WITH 
cte_name_1 AS (query_1),
cte_name_2 AS (query_2)
query_3;

Cada CTE tem seu próprio nome e instrução select. Cada CTE pode se referir a qualquer CTE definido anteriormente para obter os dados de que precisa. Observe que você não repete a palavra-chave WITH: você apenas lista os CTEs separados por vírgulas.

Vamos ver isso em ação. Suponha que a Alpha Sales agora queira levar essa análise a um estágio mais avançado. Ela gostaria de extrair dados demográficos dos clientes que compraram mais em junho do que a média de compras de abril e maio.

Para isso, ela precisa combinar os dados extraídos na consulta anterior com os dados de sua tabela customer tabela. Aqui está uma amostra dos dados:

Para fazer isso, você pode:

• Mover a consulta principal anterior para a frente como um CTE aninhado. Isso cria efetivamente uma tabela virtual que contém o customer_id, a média anterior, o total de junho e a alteração percentual.
• Escrever uma nova consulta principal que junte essa tabela com a tabela customer para calcular a idade do cliente e extrair seu estado.

A nova consulta tem a seguinte aparência:

WITH april_may_sales AS
  (SELECT 
     customer_id, 
     SUM(value) / 2 AS prev_avg
   FROM order_summary 
   WHERE EXTRACT (MONTH FROM order_date) in (4,5)
   GROUP BY customer_id
),
comparison AS
  (
    SELECT 
      order_summary.customer_id, 
      prev_avg, 
      SUM(value) AS jun_total, 
      (SUM(value) - prev_avg) * 100/prev_avg AS percent_change
    FROM order_summary  
    JOIN april_may_sales
    ON april_may_sales.customer_id = order_summary.customer_id
    WHERE EXTRACT (MONTH FROM order_date) = 6
    GROUP BY order_summary.customer_id, prev_avg
  )
SELECT 
  customer.customer_id,
  name,
  EXTRACT(YEAR from CURRENT_DATE) - 
		EXTRACT(YEAR from date_of_birth) AS age,
  state,
  prev_avg, 
  jun_total,
  percent_change
FROM customer
JOIN comparison 
    ON comparison.customer_id = customer.customer_id
WHERE percent_change > 0;

Como antes, a consulta define o CTE chamado april_may_sales como uma tabela virtual que contém a média de vendas de abril e maio.

Em seguida, ela define um novo CTE chamado comparação, que contém uma comparação dos totais de junho por cliente com o conteúdo de april_may_sales.

Por fim, a consulta principal combina os dados da tabela virtual comparison com os dados da tabela customer tabela.

O conjunto de resultados tem a seguinte aparência:

CTE do PostgreSQL em Linguagem de Manipulação de Dados

Vamos agora dar uma olhada nas instruções de manipulação de dados como INSERT, UPDATE e DELETE.

Uma das limitações dos CTEs é que você não pode usá-los diretamente no lugar de um valor em uma instrução UPDATE da mesma forma que você pode fazer com uma subconsulta.

Digamos que você queira atualizar o saldo na tabela customer adicionando o valor de todos os pedidos de junho. Com subconsultas comuns, você pode fazer algo assim:

UPDATE customer 
SET balance = balance + 
(select SUM(value) FROM order_summary 
WHERE customer.customer_id = order_summary.customer_id
   AND EXTRACT (MONTH from order_date) = 6);

Não é possível fazer isso com um CTE. O que você pode fazer, entretanto, é usar a seguinte sintaxe:

WITH cte_name AS (select_statement)
UPDATE tablename 
SET column_name_1 = column_name_2
FROM cte_name 
WHERE join_clause;

• cte_name é o nome que você usará para se referir à "tabela" criada pelo CTE.
• select_statement é a instrução que você usará para preencher o CTE.
• column_name_1 é o nome da coluna na tabela principal que você deseja atualizar.
• column_name_2 é o nome da coluna em seu CTE que você usará para definir o novo valor.
• join_clause especifica a condição que você usará para unir as duas tabelas.

A consulta a seguir adiciona o total de pedidos de junho da tabela order_summary ao saldo da tabela customer tabela:

WITH june_total AS
(SELECT 
   customer_id, 
   SUM(value) AS jun_tot
 FROM order_summary WHERE EXTRACT(MONTH FROM order_date) = 6
 GROUP BY customer_id
)
UPDATE customer
SET balance = balance + jun_tot
FROM june_total 
WHERE customer.customer_id = june_total.customer_id;

Primeiro, a cláusula WITH cria uma pseudotabela chamada june_total. Ela contém totais por customer_id de pedidos em que o mês de order_date é 6.

Em seguida, a coluna jun_tot dessa tabela é usada para aumentar o saldo onde customer_id corresponde entre as duas tabelas.

A tabela customer agora contém os seguintes dados:

Você também pode usar CTEs para inserir ou excluir linhas da mesma forma.

Consultas Recursivas

As consultas recursivas são um recurso dos CTEs. Essas consultas permitem que você faça um loop a partir de uma única consulta base para executar repetidamente uma tarefa específica. Elas são especialmente úteis para consultar dados hierárquicos, como estruturas organizacionais e listas de materiais.

Uma cobertura completa das consultas recursivas está além do escopo deste artigo. Veremos apenas a sintaxe e um exemplo simples. Para obter mais detalhes, dê uma olhada em What Is a Recursive CTE in SQL (O que é um CTE recursivo em SQL), que fornece uma explicação completa e vários exemplos.

A sintaxe das consultas recursivas no PostgreSQL é:

WITH RECURSIVE cte_name AS 
(query_1 UNION query_2)
query_3;

• A palavra-chave RECURSIVE indica que essa é uma consulta recursiva.
• query_1 é a consulta base, ou ponto de partida. Por exemplo, suponha que você esteja trabalhando com uma estrutura organizacional. Nesse caso, query_1 poderia ser uma consulta que seleciona o gerente de nível superior em um arquivo de funcionários.
• query_2 é a consulta recursiva. Essa consulta será repetida até que não haja mais linhas que atendam aos critérios especificados no site WHERE. Ela pode fazer referência à última linha adicionada ao conjunto de resultados para coletar dados. Isso pode ser usado para encontrar todos os funcionários que se reportam a um gerente.
• UNION combina os resultados. Se você usar UNION ALL, as duplicatas serão mantidas; caso contrário, serão omitidas.
• query_3 é usado para retornar o conjunto de resultados finais. Ele pode fazer referência à tabela virtual criada pelo CTE.

Vamos pensar em um exemplo de uma tabela employee em que os registros dos funcionários têm um campo que identifica o gerente ao qual eles se reportam. O que realmente acontece se você usar uma consulta recursiva para navegar nessa hierarquia?

Os resultados da consulta de base são adicionados à tabela virtual. A consulta de base extrai o registro do funcionário do CEO. Em seguida, o mecanismo de banco de dados usa essa linha para encontrar todas as linhas que correspondem aos critérios da parte recursiva da consulta. Esses serão todos os funcionários que se reportam diretamente ao gerente de nível superior.

Para cada um desses registros, por sua vez, o mecanismo encontrará todos os funcionários que se reportam a essa pessoa. Isso é repetido até que não haja mais funcionários que atendam à condição.

Vamos dar uma olhada em um exemplo simples. Uma empresa de consultores de TI tem vários projetos em andamento e sua política é agendar reuniões semanais de progresso para cada projeto. Uma tabela chamada projects contém detalhes de novos projetos. Um exemplo dessa tabela é o seguinte:

A empresa deseja criar detalhes das reuniões agendadas em uma tabela chamada meetingsEssas informações serão usadas para enviar lembretes e reservar um local a cada semana. A coluna meet_day contém o dia da semana em que as reuniões serão agendadas. Ela é armazenada como um número de dia dentro da semana, em que 0 representa domingo.

Você poderia fazer isso com a seguinte consulta recursiva:

WITH RECURSIVE date_list
   (proj_name, meet_date, end_date, meet_day, meet_time)
AS (
    SELECT proj_name, start_date, end_date, meet_day, meet_time
	FROM projects
    UNION ALL
    SELECT 
proj_name, 
meet_date + 1,
	end_date, 
meet_day, 
meet_time
    FROM date_list
    WHERE meet_date + 1 <= end_date
	
)
INSERT INTO meetings
SELECT proj_name, meet_date, meet_time
FROM date_list 
WHERE meet_day = EXTRACT (DOW from meet_date)
ORDER BY proj_name, meet_date;

Após a execução da consulta, a tabela meetings contém os seguintes dados:

Vamos dividir a consulta em partes e ver o que ela realmente faz.

Primeiro, ela define as colunas que serão incluídas no CTE date_list:

WITH RECURSIVE date_list
   (proj_name, meet_date, end_date, meet_day, meet_time)

Em seguida, ela estabelece os dados de base para a recursão, que é o conteúdo da tabela de projetos:

AS (
    SELECT proj_name, start_date, end_date, meet_day, meet_time
	from projects

Em seguida, especifica quais dados devem ser incluídos em cada recursão, com uma condição que garante que a recursão termine quando concluída:

    UNION ALL
    SELECT proj_name, 
	meet_date + 1,
	end_date, meet_day, meet_time
	FROM date_list
    WHERE meet_date + 1 <= end_date

Por fim, a consulta principal insere os resultados mantidos na tabela virtual na tabela meetings.

Isso parece útil? Você pode aprender mais sobre consultas recursivas e praticar alguns exemplos do mundo real se fizer nosso curso on-line CTE no PostgreSQL.

Saiba mais sobre os CTEs do PostgreSQL

Embora os CTEs no PostgreSQL possam não melhorar o desempenho de suas consultas, eles certamente tornam as consultas complexas mais fáceis de escrever e de entender. Ao dividir uma consulta longa em partes componentes, você pode organizar seus pensamentos e manter a codificação simples. Os CTEs também facilitam o trabalho com estruturas hierárquicas usando a cláusula RECURSIVE.

Este artigo usa especificamente a sintaxe e os exemplos do PostgreSQL, mas os CTEs funcionam da mesma forma em outros dialetos SQL, como o MS SQL Server.

Se você quiser se familiarizar com o uso de CTEs, o curso Common Table Expressions in PostgreSQL do LearnSQL tem mais de 100 exercícios práticos que o ajudarão a realmente entender essa ferramenta.

Se você quiser praticar um pouco mais, experimente estes 11 exercícios gratuitos sobre expressões de tabelas comuns do SQL. Cada exercício apresenta o tipo de desafio que você enfrentaria no mundo real, e as soluções e explicações estão incluídas. E se estiver se preparando para uma entrevista, aqui estão alguns exemplos de perguntas e respostas sobre CTE para entrevistas.

Espero que este artigo tenha lhe dado uma boa ideia do que o CTE do PostgreSQL pode fazer por você. Se você quiser aprender alguns outros conceitos avançados do PostgreSQL, este artigo é um bom lugar para começar.

Agora é com você! Lembre-se de que a prática leva à perfeição, portanto, confira nossa trilha de aprendizado SQL Avançado Practice para praticar mais os recursos avançados de SQL!