Como usar 2 CTEs em uma única consulta SQL

Você já se perguntou como usar vários CTEs em uma consulta SQL? Leia este artigo e descubra sobre os CTEs recursivos.

Após aprender expressões comuns em tabelas ou CTEs, uma pergunta natural é "Posso usar vários CTEs em uma consulta? Sim, você pode! E você pode fazê-lo com bastante facilidade, especialmente se você já tiver algum conhecimento básico de CTEs. Se você conhece um pouco sobre CTEs ou se é inteiramente novo no mundo dos CTE, ler sobre o que é um CTE é sempre um bom começo.

O que você aprenderá neste artigo pode ser praticado no curso LearnSQL.com.br Recursive Queries, que foi nosso curso do mês em agosto.

Vou começar explicando como usar dois CTEs em uma consulta. Em seguida, vou ensinar como usar os CTEs onde o segundo CTE se refere ao primeiro. Para apimentar este artigo, terminarei mostrando-lhe duas consultas com CTEs, uma das quais é recursiva.

2 CTEs, 1 consulta SQL, 0 problemas

Vamos imaginar que haja uma tabela chamada logins que armazena login dados. Ele contém as colunas:

• id - A identificação do login.
• username - O usuário que efetuou o login.
• login_date - A data desse login.
• login_start - Quando o usuário efetuou o login.
• login_end - Quando o usuário fez o logout.

Se você olhar para a tabela, notará que cada nome de usuário aparece pelo menos uma vez.

Se sua tarefa for calcular o tempo médio (em minutos) que cada usuário passou logado, como você faria usando CTEs?

Você precisará de dois CTEs, com o código com este aspecto:

WITH distinct_user AS (
	SELECT DISTINCT username
FROM logins),

minutes_logged AS (
	SELECT	username,
			DATEDIFF (minute, login_start, login_end) AS minutes
FROM logins)

SELECT	u.username,
		AVG (minutes) AS avg_time_logged
FROM distinct_user AS u 
JOIN minutes_logged AS m 
ON u.username = m.username
GROUP BY u.username;

Primeiro, vou apenas mencionar que você precisa ter nomes de usuário distintos para que isto funcione. Agora, vamos começar a escrever o primeiro CTE! Nada de anormal aqui - como em todos os CTE, você o define por WITH; eles não são chamados de WITH consultas por nada!

O que se segue é o nome do CTE; neste caso, o primeiro CTE é chamado distinct_user. Você segue isto com a palavra-chave AS, e então você simplesmente define a declaração regular SELECT dentro dos parênteses. Esta declaração SELECT simplesmente seleciona nomes de usuário únicos da tabela logins.

Uma vez que você tenha nomes de usuário distintos, você tem que calcular quanto tempo o usuário esteve logado. Esta é a parte crítica - escrever o segundo CTE. Meu segundo CTE é chamado minutes_logged.

Você vai notar que não há WITH antes do segundo CTE. Isto é extremamente importante! Após ter definido o primeiro CTE, ele é separado do segundo apenas pela vírgula, ou seja, você escreve WITH apenas uma vez. Depois disso, não importa quantos CTE você define; é importante apenas separá-los por vírgula e começar cada CTE usando seu nome.

Vamos agora analisar o que o minutes_logged CTE faz. Primeiro tira o nome de usuário da coluna da tabela logins. Depois calcula a diferença entre o login_start e o login_end em minutos. Este novo valor será mostrado na coluna minutes.

Você terá que calcular o tempo médio (em minutos) que cada usuário gastou sendo logado. Para essa parte do cálculo, eu usei a consulta externa. Ela seleciona o nome de usuário do CTE distinct_user e depois calcula a média de minutos de log-in usando a coluna minutes do CTE minutes_logged.

Esses dois CTEs são unidos usando o nome de usuário da coluna. Finalmente, o resultado é agrupado pela mesma coluna, já que queremos o resultado em nível de usuário.

Mas será que posso usar um CTE dentro de outro CTE?

Uma pergunta direta merece uma resposta direta: sim, você pode. Agora que você sabe como usar vários CTEs, escrever um CTE que faça referência a outro CTE é apenas uma variação do que você aprendeu. Como é uma variação, acho melhor mostrar-lhe como fazê-lo usando um exemplo que você já está familiarizado.

Voltemos ao exemplo anterior. Vou ajustar um pouco o código para mostrar como escrever um CTE que faça referência ao primeiro CTE. Esta é a nova versão de nosso código anterior:

WITH difference AS (
	SELECT 	username,
		 	DATEDIFF (minute, login_start, login_end) AS minutes
FROM logins),

average_logged AS (
SELECT	username,
			AVG (minutes) AS average
FROM difference
GROUP BY username)

SELECT DISTINCT 		username,
				average
FROM average_logged;

A lógica é a mesma do primeiro exemplo, é apenas que os passos estão em uma ordem different. O primeiro CTE, diferença, calcula os minutos gastos com cada login; isto é o mesmo que no primeiro exemplo. Agora que tenho os minutos, tenho que calcular o tempo médio (em minutos) gasto por cada usuário. É para isso que vou usar a média do CTE. Como você se lembra, colocamos uma vírgula entre os dois CTEs e iniciamos o segundo pelo seu nome - não é preciso usar WITH novamente! Este segundo CTE calcula o tempo médio para cada login; para isso, ele usa a coluna minutes do primeiro CTE e armazena o resultado na coluna average.

Para referenciar o CTE difference no segundo CTE, você o trata como uma tabela: FROM difference. Finalmente, você agrupa o resultado pelo nome de usuário, já que não quer a média para todos os usuários.

A consulta externa então simplesmente seleciona as colunas username e a média a partir do CTE average_logged. Dessa forma, você obtém o mesmo resultado que no primeiro exemplo:

Se esses exemplos não forem suficientes para você, há um artigo que lhe dá vários outros exemplos para praticar. Isto inclui o uso de mais de um CTE também.

Usando CTEs Recursivos com CTEs Não-Recursivos

Há uma razão pela qual os CTEs são ensinados em nosso Consultas Recursivas curso. É porque os CTEs também podem ser recursivos. Esta é a parte mais complicada do aprendizado dos CTEs. (Presumo que você já saiba o que é um CTE recursivo e como escrevê-lo. Se não, veja Consulta SQL Longa vs. Consulta SQL Recursiva para uma introdução e Faça em SQL: Traversal SQL recursivo em árvore para um exemplo detalhado. ) Entretanto, talvez você não tenha percebido que pode usar mais de um CTE, mesmo que um deles seja recursivo. Vamos ver como isso é feito.

Para este exemplo, vamos imaginar que você queira comprar uma empresa com seus amigos. Existem quatro opções para o investimento, cada uma exigindo uma quantia de dinheiro diferente. Você ainda está em conversações iniciais. Você não tem certeza de quantos amigos participarão; sua participação depende da quantidade de dinheiro que eles têm para investir. Esta quantia depende do número total de investidores envolvidos e da opção de investimento escolhida.

Para ajudá-los a decidir, você decidiu calcular a quantia necessária por investidor para as quatro opções de investimento com um número de investidores de um a dez.

Neste exemplo, temos primeiro um CTE não-recorrente seguido de um CTE recursivo:

WITH RECURSIVE investment AS (
	SELECT amount 5897645 AS investment_amount
	UNION 
SELECT 4536841 AS investment_amount
	UNION 
SELECT 3852457 AS investment_amount
	UNION 
SELECT 3452115 AS investment_amount
),

per_investor AS (
	SELECT	0 AS investors_number,
			0 AS amount,
			0 AS individual_amount
	UNION 
	SELECT	investors_number + 1,
			investment_amount,
			investment_amount/(investors_number + 1)
	FROM investment, per_investor
	WHERE investors_number < 10)

SELECT *
FROM per_investor
ORDER BY  amount, investors_number;

A principal distinção é evidente desde o início! Ao escrever consultas recursivas, você tem que iniciar seu código usando WITH RECURSIVE. Como você vai notar, você não precisa começar com o CTE recursivo em si; neste exemplo, um CTE não recursivo vem em primeiro lugar, mesmo que utilizemos WITH RECURSIVE imediatamente antes de defini-lo. Você pode escrever CTEs na ordem que quiser; o recursivo pode ser o primeiro ou pode ser o último. Mas é importante lembrar que se você vai ter pelo menos uma consulta recursiva, escrever WITH RECURSIVE é obrigatório.

No exemplo atual, meu primeiro CTE (não recursivo) é denominado investimento. Como não tenho a tabela com todos os valores de investimento, decidi usar o CTE como uma tabela temporária. Como você pode ver, os possíveis valores de investimento são os seguintes:

• 5,897,645
• 4,536,841
• 3,852,457
• 3,452,115

Ao utilizar UNION com SELECT no primeiro CTE, estou virtualmente criando uma tabela que contém essas quatro possibilidades de investimento. Elas serão mostradas na coluna investment_amount. A tabela resultante para este CTE é:

O segundo CTE é recursivo. Entretanto, isto não muda nada em comparação à escrita de dois CTEs não recursivos: a vírgula novamente separa os CTEs.

Vamos analisar um pouco o segundo CTE. A primeira declaração SELECT define três colunas que têm o valor 0: investors_number, quantidade, e individual_amount. Como disse, o CTE recursivo se refere a si mesmo, ou seja, o operador UNION é necessário.

O operador UNION é seguido por uma ou mais declarações SELECT, que realizará a operação desejada na primeira declaração SELECT. Isto significa que ele adicionará 1 à coluna investors_number. Em seguida, ele colocará investment_amount do primeiro CTE na quantidade da coluna. Ele dividirá o valor do investimento com o número de investidores envolvidos; o resultado é mostrado na coluna individual_amount. Ele realizará esta operação para todos os quatro montantes de investimento até chegar a dez investidores.

A consulta externa simplesmente seleciona todos os dados do CTE per_investor, com os dados sendo ordenados pelo montante das colunas e investors_number. Execute esta consulta e aproveite todas as possibilidades que você calculou:

Bastante impressionante, não é?

A combinação de dois ou mais CTEs realmente funciona

As expressões comuns das tabelas SQL são uma ferramenta poderosa. Elas são úteis se você quiser que seu código seja mais legível, mas seu valor não é apenas cosmético; suas possibilidades reais brilham se você souber como usar vários CTEs em uma consulta ou mesmo escrever um CTE recursivo em SQL.

Como você viu, não é difícil combinar dois ou mais CTEs. Uma vez que você saiba disso, ele realmente abre as possibilidades de uso de CTEs em várias situações. Agora cabe a você encontrar casos em que você terá que usar CTEs. E pratique, pratique, pratique! É sempre aconselhável usar nosso curso Consultas Recursivas para isso. Ele lhe dará tabelas para praticar, para que você não tenha que se preocupar em criar cenários você mesmo.

Esteja à vontade para compartilhar sua experiência com os CTEs SQL em nossa seção de comentários.