Como Escrever Múltiplos CTEs em SQL

Aproveite todo o potencial dos CTEs, combinando dois ou mais deles em uma única consulta SQL.

As expressões comuns de tabela, ou CTEs, podem ser uma poderosa ferramenta SQL. Quando você escreve dois (ou até mais) CTEs juntos, este poder se multiplica. Neste artigo, vou lhe mostrar três formas de escrever múltiplos CTEs:

• Usando dois CTEs independentes em uma consulta SQL.
• Usando dois CTEs em que o segundo CTEs se refere ao primeiro.
• Usando dois CTEs em que um CTE é recursivo.

Se você não estiver familiarizado com CTEs, recomendo nosso curso interativo de Expressões de Tabela Comum, que cobre todos os tipos de CTEs, inclusive os recursivos, em 114 exercícios práticos. Você pode ler sobre o curso em um episódio de nossa série Curso do Mês.

Se você quiser refrescar seus conhecimentos rapidamente, estes artigos sobre CTEs e quando você deve usá-los são um bom começo.

Introduzindo os dados

Mostrarei os exemplos do CTE em um conjunto de dados composto por duas tabelas.

A primeira tabela é cars. Ele contém dados sobre os carros fabricados pela Renault e Nissan. Suas colunas são:

• id - O ID do carro e a chave primária (PK) da tabela.
• car_make - O fabricante do carro.
• model - O modelo do carro.
• motor_type - Os detalhes sobre o tipo de motor do modelo.
• year_of_production - O ano em que o carro foi fabricado.

Aqui está uma amostra dos dados da tabela:

A segunda tabela, car_sales, tem estas colunas:

• id - A identificação das informações de venda e a chave primária (PK) da tabela.
• report_period - A data do relatório de vendas.
• sales - O número de carros vendidos.
• cars_id - A chave estrangeira (FK) que faz referência à tabela cars.

Alguns dados de amostra da tabela:

Dois CTEs: Independente:

Neste primeiro exemplo, vou lhe mostrar como produzir o total de vendas em geral, bem como fazer com dois CTEs independentes. O código está bem aqui:

WITH sales_per_make AS (
	SELECT car_make,
		 SUM(sales) AS total_sales_per_make
	FROM cars c
JOIN car_sales cs
		ON c.id = cs.cars_id
GROUP BY car_make
),

sales_sum AS (
	SELECT SUM(sales) AS total_sales
	FROM car_sales
)
	
SELECT car_make,
	 total_sales_per_make,
	 total_sales
FROM sales_per_make, sales_sum ss;

Começo a escrever o primeiro CTE como se fosse o primeiro e único CTE em minha consulta. O nome do CTE, sales_per_makeA palavra-chave AS, segue a palavra-chave WITH, depois vem a palavra-chave AS. Depois disso, escrevo o que quero que o CTE faça entre parênteses.

Neste caso, estou usando a função agregada SUM() para encontrar as vendas por marca de carro. Para fazer isso, tenho que juntar as duas tabelas que tenho à minha disposição.

Depois disso, vem o segundo CTE. O principal aqui é que uma vírgula deve separar os dois CTEs. Então, o segundo CTE não começa com a palavra-chave WITH, mas sim imediatamente com o nome do segundo CTE. Eu o nomeei sales_sum. A declaração SELECT entre parênteses calcula o total de vendas em todas as marcas do carro.

Estes dois CTEs são independentes porque o segundo CTE não faz referência ao primeiro.

Para utilizar estas consultas, tenho que escrever uma declaração SELECT (a consulta principal) que faça referência a elas. Isto é o mesmo que quando você escreve apenas um CTE. A declaração SELECT aqui junta os resultados dos dois CTEs para retornar esta saída:

O resultado significa que os revendedores venderam 176.569 carros Renault e 185.359 carros Nissan, para um total de 361.928 carros.

Agora você vê que escrever dois CTEs não é tão difícil assim. Entretanto, há certas armadilhas ao usar múltiplos CTEs em uma consulta. As principais coisas que você deve estar atento ao escrever múltiplos CTEs são:

• Use apenas um WITH.
• Separar os CTEs com vírgulas.
• Não usar vírgulas antes da consulta principal.
• Há apenas uma consulta principal.

Ter múltiplos CTEs funcionando somente se você escrever a palavra-chave WITH uma vez. Mas isto não é apenas onde você quiser. Você tem que escrevê-la antes do primeiro CTE. O segundo e qualquer CTE seguinte começa com o nome dos respectivos CTEs, ao contrário do primeiro CTE que começa com a palavra-chave WITH.

O primeiro CTE é separado do segundo por uma vírgula. Isto também vale se você escrever mais de dois CTEs: todos os CTEs são separados por uma vírgula. Entretanto, não importa quantos CTEs você tenha, não há vírgula entre o último CTE e a consulta principal.

Finalmente, há apenas uma consulta principal. O que quer que você queira calcular, ele só funciona se houver uma consulta principal. Parece lógico porque você pode fazer referência a todos os CTEs que quiser juntar como qualquer outra tabela. Esse é um dos benefícios dos CTEs, então aproveite!

Dois CTEs: Um referenciando o outro

Neste cálculo um pouco mais complexo, eu uso dois CTEs novamente. Desta vez, o segundo faz referência ao primeiro. Esta é a única opção, se você quiser que um CTE faça referência a outro. Você pode fazer referência apenas aos CTEs antes do atual e não aos CTEs que se seguem.

Vou escrevê-los para calcular as vendas reais em 2022 (isto é, em janeiro e fevereiro), orçar as vendas anuais de 2022 usando a média de vendas, e finalmente encontrar as vendas ainda a serem feitas em 2022.

Vou guiá-lo passo a passo através do código abaixo, para que você entenda o que está acontecendo aqui:

WITH sales_per_car AS (
	SELECT c.id,
		 c.car_make,
	   	 c.model,
	       c.motor_type,
	       c.year_of_production,
	       AVG(cs.sales)::INT AS average_sales_2022
	FROM cars c JOIN car_sales cs ON c.id = cs.cars_id
	WHERE c.year_of_production = 2022
	GROUP BY c.id, c.car_make, c.model, c.motor_type, c.year_of_production
),

budget AS (
	SELECT *,
		 average_sales_2022 * 12 AS annual_planned_sales_2022
	FROM sales_per_car
)
	
SELECT b.car_make,
	 b.model,
	 b.motor_type,
	 b.year_of_production,
	 SUM(cs.sales) AS actual_ytd_sales_2022,
	 b.annual_planned_sales_2022,
	 b.annual_planned_sales_2022 - SUM(cs.sales) AS remaining_annual_sales_2022
FROM budget b
JOIN car_sales cs
ON b.id = cs.cars_id
GROUP BY b.car_make, b.model, b.motor_type, b.year_of_production, b.annual_planned_sales_2022;

A sintaxe aqui é a mesma que no exemplo anterior. O CTE sales_per_car retorna algumas colunas da tabela cars. Também utilizo a função AVG() para calcular a média de vendas em 2022. Este número é convertido em um número inteiro porque estou falando de carros, então quero ver a média como um número inteiro de carros.

Este CTE me dá a média de vendas para cada modelo produzido em 2022. Ele também indica porque eu sei que as vendas são apenas para 2022: carros produzidos em 2022 não puderam ser vendidos em 2021. Essa é a suposição aqui, pelo menos.

Uma vírgula separa este CTE do segundo, que começa com seu nome, budget. Este CTE agora faz referência ao primeiro CTE como qualquer outra tabela. Você pode ver isso na cláusula FROM. Estou usando a coluna average_sales_2022 porque é a venda média mensal real para 2022. Digamos que o método para orçar aqui é multiplicar a média de vendas mensais para aquele ano por 12 para obter as vendas anuais planejadas. É exatamente isso que este segundo CTE está fazendo, e esta é a razão pela qual ele tem que fazer referência ao primeiro CTE.

A principal pergunta se junta ao CTE budget e a mesa car_sales. Estou usando esta consulta para encontrar as vendas reais por modelo em 2022. Depois, estou mostrando a coluna annual_planned_sales_2022 do segundo CTE. Finalmente, ao calcular a diferença entre estas duas colunas, obtenho o número de vendas ainda a serem feitas para o restante de 2022.

Aqui está o relatório que recebo ao fazer a consulta:

Dois CTEs: Um dos CTEs é Recursivo

Agora vou passar destes dados, mas permaneço dentro destas marcas de carros. Imaginemos que a Renault está considerando três investimentos: compra da Jaguar por 2.300.000.000, Volvo por 1.548.470.000, ou Alfa Romeo por 2.450.000.000. Está considerando fazer isso por conta própria, junto com a Nissan, ou com a Nissan e a Citroën.

Este exemplo é perfeito para escrever dois CTEs, sendo um deles recursivo:

WITH RECURSIVE company_purchase AS (
	SELECT 2300000000 AS amount
UNION
	SELECT 1548470000 AS amount
UNION
	SELECT 2450000000 AS amount
),

per_buyer AS (
	SELECT  0 AS number_of_buyers,
		  0::DECIMAL AS purchase_amount,
		  0::DECIMAL AS amount_per_buyer
UNION
	SELECT  number_of_buyers + 1,
		  amount,
		  amount/(number_of_buyers + 1)::DECIMAL
	FROM company_purchase, per_buyer
	WHERE number_of_buyers <= 3)

SELECT *
FROM per_buyer
ORDER BY purchase_amount, number_of_buyers;

Sempre que você quiser um CTE recursivo, você precisa começar a escrever CTEs com RECURSOS. Você sempre anuncia sua intenção de escrever um CTE recursivo, quer esta consulta recursiva seja a primeira ou a segunda CTE. Neste caso, meu primeiro CTE é não recursivo.

Eu uso o company_purchase CTE para criar diferentes investimentos na Jaguar, Volvo, ou Alfa Romeo.

Depois vem a consulta recursiva. O princípio é o mesmo: separar CTEs por vírgula e iniciar o segundo CTE sem a palavra-chave WITH. Neste segundo CTE, meu ponto de partida não é nenhum investimento e nenhum comprador. Os valores em todos os lugares serão zero. Então eu utilizo a recorrência, e a consulta calcula o valor por comprador para um, dois, ou três investidores para o primeiro investimento. A recursividade então repete o mesmo cálculo para o segundo e terceiro investimentos. É claro, para fazer isso, tenho que juntar a consulta recursiva com a não recursiva.

Finalmente, a consulta principal seleciona todos os dados do per_buyer CTE, com os seguintes resultados:

O que estes dados me dizem? Por exemplo, se três compradores (Renault, Nissan e Citroën) comprarem a Volvo por 1.548.470.000, cada empresa deverá investir 516.156.666,67.

Outros exemplos podem ser encontrados no artigo sobre as 5 principais perguntas da entrevista CTE SQL e em um artigo adicional falando sobre o uso de dois CTEs.

Multiplique ainda mais o poder dos CTEs

Estes três são apenas exemplos do que os CTEs podem fazer, especialmente do que eles podem fazer se você combinar vários CTEs de maneiras diferentes. Este não é um conceito fácil e requer muita prática.

Em vez de criar seus próprios dados e cenários, faça nosso curso interativo de Expressões de Mesa Comum! Ele lhe dá tudo isso e elimina todas as preocupações que você tem sobre como praticar os CTEs, com mais de 100 exercícios práticos!