Quando devo usar uma Expressão de Mesa Comum (CTE)?

Interessada em ouvir mais sobre expressões comuns de mesa ou CTEs? Você gostaria de saber quando os CTEs são úteis? Leia neste artigo.

Se você já ouviu falar de expressões comuns em mesas, você provavelmente já se perguntou o que elas fazem. Mesmo que você não tenha ouvido, é bom que você esteja aqui! Os CTEs podem ser muito úteis, especialmente se você já dominou os conceitos básicos de SQL, como selecionar, ordenar, filtrar dados e juntar tabelas. Vamos direto ao ponto e ver o que são CTEs, bem como quando e como usá-los.

O que é uma expressão comum de tabelas?

Uma expressão de tabela comum (CTE) é um recurso SQL relativamente novo. Foi introduzida em SQL:1999, a quarta revisão SQL, com padrões ISO emitidos de 1999 a 2002 para esta versão de SQL.

As CTEs foram introduzidas no SQL Server pela primeira vez em 2005, depois o PostgreSQL as tornou disponíveis a partir da versão 8.4 em 2009. O MySQL esperou um pouco mais e as tornou disponíveis em 2018, começando com a Versão 8.0. Simplificando, é um conjunto de dados temporário retornado por uma consulta, que é então utilizado por outra consulta. É temporário porque o resultado não é armazenado em nenhum lugar; ele existe apenas quando a consulta é executada.

Existem dois tipos de CTEs:

• não-recursivo:
• recursivo

Discutirei apenas o CTE não recursivo neste artigo, e só mencionarei o CTE recursivo brevemente no final.

A sintaxe básica do CTE (não-recursivo) é a seguinte:

WITH expression_name AS (CTE definition)

Como você pode ver, ele é feito usando uma declaração WITH. Por este motivo, os CTEs também são chamados de consultas WITH. Após o WITH, você define um CTE entre parênteses. Definir CTE significa simplesmente escrever uma consulta SELECT que lhe dará um resultado que você deseja usar dentro de outra consulta.

Como você pode ver, isso é feito usando uma declaração WITH. Por esta razão, os CTEs também são chamados de CTE com consultas. Após o WITH, você define um CTE entre parênteses. Definir CTE significa simplesmente escrever uma consulta SELECT que lhe dará um resultado que você deseja utilizar dentro de outra consulta.

SELECT ... 
FROM expression_name

Você define sua consulta SELECT e depois faz referência a seu CTE, usando-o como faria com qualquer outra tabela após a cláusula FROM.

Se você quiser ler mais sobre CTEs antes de prosseguir para os exemplos, aqui está um artigo que os explica bem.

A sintaxe do CTE

Agora, vamos ver como a sintaxe do CTE funciona na prática. Suponha que exista um banco de dados contendo vários dados da universidade com as três tabelas a seguir:

• students
• subjects
• exams

A tabela students tem as seguintes colunas:

• id: o ID do estudante, uma chave primária
• first_nameo primeiro nome do estudante
• last_name: sobrenome do estudante

A tabela a seguir é a subjects tabela contendo os dados:

• ida identificação do sujeito, uma chave primária
• subject_nameo nome do sujeito

A terceira tabela é a exams tabela que armazena os seguintes dados:

• id: a identificação do exame dado
• exam_date: a data em que o exame foi realizado
• subject_idA identificação do assunto, uma chave estrangeira da tabela subjects
• student_ido ID do aluno que fez o exame, uma chave estrangeira da tabela students

Sua tarefa é calcular a nota média para os estudantes. Então, para cada aluno com nota média acima de 8,5, você precisa mostrar seu nome, sobrenome e nota média, e rotulá-los como alunos "excepcionais". Como você faria isso usando um CTE?

O código que lhe dará o resultado desejado pode ser escrito desta forma:

WITH grade_average AS (
SELECT	s.id,
		s.first_name,
		s.last_name,
		AVG (e.grade) AS average_grade 
FROM students s JOIN exams e ON s.id = e.student_id
GROUP BY s.id, s.first_name, s.last_name
)

SELECT	first_name,
		last_name,
		average_grade,
		'exceptional' AS tag
FROM grade_average
WHERE average_grade>8.5;

Primeiro, você precisa definir seu CTE. Como você já aprendeu, isto é feito usando uma declaração WITH. É seguido pelo nome do CTE, que é grade_average neste caso. Uma consulta CTE é definida entre os parênteses. Quando se olha para ela por si só, não é complicada; é uma consulta de aparência bastante regular SELECT. Ela seleciona id, first_name, e o last_name a partir da tabela students. Ele também calcula a nota média, usando a nota da coluna da tabela. exams. O resultado é mostrado na nova coluna average_grade. As tabelas students e exams são unidas na coluna apropriada de identificação de estudante de cada tabela. O resultado é agrupado pelas colunas id, first_name, e last_name da tabela students. Os registros são agrupados, já que é necessário obter o resultado por aluno.

Depois que o CTE é definido, há outra consulta SELECT que utiliza o CTE. Esta consulta seleciona as colunas first_name, last_name, e average_grade do CTE, grade_average. Ele também atribui o valor "excepcional". Há uma cláusula WHERE no final para mostrar somente os estudantes com nota média acima de 8,5.

A execução da consulta resultará nos nomes de três alunos "excepcionais".

Usando mais de um CTE em uma consulta

É possível definir e utilizar mais de um CTEs em uma consulta. Você o faz separando cada CTE com uma vírgula, e usa uma declaração WITH somente ao definir o primeiro CTE.

Deixe-me mostrar-lhe um exemplo. Com as mesmas tabelas do exemplo anterior, você tem a seguinte tarefa: mostrar o nome dos sujeitos e suas respectivas médias e notas mínimas, mas somente para aqueles sujeitos nos quais todos passaram no exame, ou seja, sua nota é 5 ou superior.

Para obter o resultado desejado, sua consulta deve ter o seguinte aspecto:

WITH subject_average AS (
SELECT	su.id,
su.subject_name,
		AVG (e.grade) AS subject_average_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
),

min_grade AS (
SELECT	su.id,
		su.subject_name,
		MIN (e.grade) AS subject_min_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
HAVING MIN (e.grade) > 5
)

SELECT	sa.id,
		sa.subject_name,
		sa.subject_average_grade
FROM subject_average sa JOIN min_grade m ON sa.id =m.id;

Primeiro, um CTE chamado subject_average está definido. Ele seleciona as colunas id e subject_name da tabela subjects. Em seguida, calcula as notas médias usando os dados da tabela exams e atribui os resultados na nova coluna subject_average_grade. Em seguida, ele agrupa os dados para obter o resultado por assunto.

Agora, você define o segundo CTE. Lembre-se do que eu disse anteriormente - você separa os CTEs com vírgulas e escreve o segundo CTE omitindo a declaração WITH. O segundo CTE aqui é chamado min_grade. Ele também seleciona id e subject_name da tabela subjects depois calcula as notas mínimas, mostrando o resultado na nova coluna subject_min_grade. Ele agrupa os dados como foi feito no primeiro CTE. Como você precisa do resultado somente para os assuntos em que todos passaram, você usa uma cláusula HAVING para selecionar somente os assuntos em que a nota mínima é 5 ou superior.

Finalmente, você escreve a consulta SELECT que mostrará o ID do assunto, o nome do assunto, e a nota média para cada assunto que atende aos critérios. Existem apenas dois assuntos deste tipo:

Uma vez que você aprenda o básico dos CTEs, há o curso Consultas Recursivas com muitos mais exemplos onde você pode praticar a escrita da sintaxe.

Quando usar os CTEs

Os CTEs permitem realizar agregações multiníveis. Quais são elas?

Voltemos às tabelas que usamos nos exemplos anteriores. A tarefa agora é calcular a nota média mínima e a nota média máxima por assunto.

Por onde você começaria? Se você pensar logicamente, primeiro você deve encontrar a nota mínima e máxima por assunto, então encontre a média dos resultados por assunto. É simples - o código se parece com isto:

SELECT		su.id,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade,
		AVG (MIN (e.grade)) AS avg_min_grade,
		AVG (MAX (e.grade)) AS avg_max_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name;

Logicamente falando, isto tenta calcular primeiro a nota mínima e a nota máxima por assunto, depois a média desses valores. Voilá! Agora você executa o código, e recebe uma mensagem que se parece com isto:

Msg 130, Level 15, State 1, Line 16
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Hm, não é o resultado que você esperava? Isso porque SQL não permite construções como AVG (MIN (e.grade)). Suas idéias estavam corretas, mas você tem que usar um CTE para traduzi-las em um código SQL. Veja como fazer isso:

WITH min_max_grade AS (
SELECT		su.id,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
)

SELECT		AVG (min_grade) AS avg_min_grade,
		AVG (max_grade) AS avg_max_grade
FROM min_max_grade;

O CTE é nomeado min_max_grade. Nele, há uma declaração SELECT que calcula as notas mínimas e máximas por assunto, como eu pretendia fazer na consulta que retornou a mensagem de erro. O resultado é mostrado nas novas colunas min_grade e max_grade. Um CTE agora ajuda a traduzir sua lógica em código.

Após definir o CTE, você escreve uma declaração SELECT que calcula a média de min_grade e max_grade a partir do CTE. O resultado será mostrado nas novas colunas avg_min_grade e avg_max_grade. Agora que você o vê, é fácil, certo?

Os CTEs também são muito úteis quando você precisa organizar consultas longas e complexas. O uso dos CTEs melhorará a legibilidade de seu código, uma vez que ele divide o código em etapas separadas. Torna-se mais fácil mudar o código ou corrigir erros. Se você insistir em não usar os CTEs, seu código poderá ter este aspecto:

SELECT		AVG (min_grade) AS avg_min_grade,
		AVG (max_grade) AS avg_max_grade
FROM (
SELECT	su.id,
		su.subject_name,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade
	FROM subjects su JOIN exams e ON su.id = e.subject_id
	GROUP BY su.id, su.subject_name
) AS min_max;

Em comparação com a solução que utiliza um CTE, isto parece um pouco por todo o lado e mais difícil de ler. A leitura de subconsultas pode ser difícil, porque você tem que primeiro pensar no que cada subconsulta faz, depois voltar à consulta principal, e de alguma forma conectá-las todas em sua cabeça. Além disso, usar subconsultas como esta vai contra a forma como sua mente funciona logicamente e como você pensa sobre os passos que o levariam à solução. Lembre-se de que você dividiu o problema em duas etapas: primeiro, calcule as notas mínimas e máximas para cada assunto, depois calcule a média das notas mínimas e máximas. O código CTE reflete exatamente esta ordem.

A lógica no código com uma subquisição é o oposto de como você pensou sobre a solução. Aqui, primeiro escrevemos que você quer alguma média das notas, depois especificamos na subconsulta que você quer que as médias sejam das notas mínimas e máximas. Quando você usa uma subconsulta, como você escreve o código geralmente vai contra a forma como você pensa na lógica.

E se o código com uma subconsulta é menos legível e mais difícil de entender do que o código com um CTE neste exemplo simples, imagine como seria se você tivesse que escrever consultas mais complexas! Você estaria coçando a cabeça, tentando muito só para entender o que cada parte do código faz. Ter dificuldades para entender um código pode ser muito frustrante. É aqui que os CTEs podem ajudar você.

Você provavelmente já notou que os CTEs são muito parecidos com subconsultas. Talvez você estivesse se perguntando por que estou usando CTEs quando tudo que fiz poderia ter sido feito com subconsultas. Isso é verdade, mas além de serem mais legíveis, os CTEs têm uma grande vantagem sobre as subconsultas: os resultados de um CTE podem ser usados mais de uma vez em uma consulta. Se você estiver interessado em mais sobre este tópico, recomendo ler sobre mais diferenças entre os CTEs e as subconsultas.

Mencionei anteriormente que os CTEs podem ser não-recursivos ou recursivos. Até o momento, temos analisado apenas CTEs não-recorrentes. Os CTEs recursivos são CTEs que se referem a si mesmos; ao fazer isso, eles retornam o subresultado e repetem o processo até que retornem o resultado final. O uso de CTEs recursivos realmente desbloqueia o poder dos CTEs; eles são úteis ao processar estruturas hierárquicas, tais como árvores e gráficos.

Como obter a vantagem de como usar os CTEs?

Neste artigo, abordamos alguns dos conceitos básicos dos CTEs. Você aprendeu o que é um CTE, entendeu sua sintaxe e revisou alguns exemplos simples para lhe dar uma idéia do que os CTEs podem fazer. Eu também apontei alguns usos comuns dos CTEs para ajudá-lo a encontrar uma maneira de usá-los em seu estudo ou trabalho. Espero ter lhe dado algumas boas orientações; agora é sua vez de colocar em prática o que você aprendeu.

Se você tiver alguma pergunta ou comentário, me avise na seção de comentários!