CTEs SQL Explicados com Exemplos

Aprenda como você pode aproveitar o poder das Expressões de Mesa Comum (CTEs) para melhorar a organização e a legibilidade de suas consultas SQL.

A abreviação comumente usada CTE significa Common Table Expression (Expressão Comum de Mesa).

Para aprender sobre as Expressões da Tabela Comum SQL através da prática, eu recomendo a Consultas Recursivas curso em LearnSQL.com.br. Ele contém mais de 100 exercícios práticos em CTEs simples e complexos recursivos.

O que um CTE faz? Por que você poderia querer usar um em seu código SQL? Vamos responder a essas perguntas.

O que é um CTE?

Uma Expressão de Tabela Comum é um conjunto de resultados chamado temporário. Você cria um CTE usando uma consulta WITH, depois a referencia dentro de uma declaração SELECT, INSERT, UPDATE, ou DELETE.

Digamos que você tenha uma tabela chamada schools com as colunas school_id, school_name, district_id, e o número de estudantes. É necessário escrever uma consulta para exibir uma lista de escolas ao lado de sua identificação de distrito e o número médio de alunos por escola naquele distrito.

Sua lógica pode ser a seguinte:

1. Crie uma tabela com a lista de distritos e o número médio correspondente de alunos por escola.
2. Junte-se a esta tabela com a lista de escolas e exiba as informações necessárias.
3. Solte a tabela com o número médio de alunos por escola para cada distrito.

Se você utiliza um CTE, não é necessário criar e soltar uma tabela. Você pode simplesmente fazer referência ao resultado temporário criado pela consulta WITH, como você vê abaixo:

WITH avg_students AS (
SELECT district_id, AVG(students) as average_students
FROM schools
GROUP BY district_id)
SELECT s.school_name, s.district_id, avg.average_students
FROM schools s
JOIN avg_students avg
ON s.district_id = avg.district_id;

Então, aqui você começa definindo o conjunto de resultados temporários avg_students na consulta COM AQUI. Nos parênteses, há uma declaração SELECT que define este conjunto de resultados; ela contém uma lista de distritos e o número médio correspondente de alunos por escola. Após o parêntese de fechamento, você inicia a declaração SELECT principal. Observe que você faz referência ao conjunto de resultados temporários como uma tabela comum, usando seu nome atribuído (avg_students). O resultado incluirá o nome da escola, a identificação de seu distrito e o número médio de alunos neste distrito.

No sentido de que não pode ser usado em nenhuma outra consulta SQL, o conjunto de resultados temporários é "descartado". Naturalmente, você pode defini-lo novamente se for necessário.

Agora que você tem um entendimento básico das Expressões da Tabela Comum e sua sintaxe, é hora de ver como usar CTEs em casos de negócios do mundo real.

CTEs em Ação

Vamos começar explorando os dados. Suponha que você seja um analista de dados de um banco de varejo e queira analisar os bônus concedidos aos funcionários no mês passado. A tabela a seguir é o ponto de partida:

Bonus_jan

Agora vamos dizer que você quer ver o bônus pago a cada funcionário junto com o bônus médio por sua posição. Para isso, primeiro é preciso calcular o bônus médio para cada cargo. Isto pode ser feito em um conjunto de resultados temporários (um CTE). Toda a consulta terá este aspecto:

WITH avg_position AS (
    SELECT position, AVG(bonus) AS average_bonus_for_position
    FROM bonus_jan
    GROUP BY position)
SELECT b.employee_id, b.first_name, b.last_name, b.position, b.bonus, ap.average_bonus_for_position
FROM bonus_jan b
JOIN avg_position ap
ON b.position = ap.position;

Como você pode ver, começamos por definir o conjunto de resultados temporários avg_position. Depois há a declaração principal do SELECT, onde você se junta ao bonus_jan com o conjunto de resultados temporários avg_position para exibir informações sobre cada funcionário, seus bônus e o bônus médio para aquele cargo:

Múltiplos CTEs em Uma Consulta

Você pode ter várias expressões de tabelas comuns em uma única consulta - basta usar uma palavra-chave WITH e separar os CTEs com vírgulas.

Digamos que você queira comparar o bônus de cada funcionário com o bônus médio para aquela posição e o bônus médio para aquela região. Para fazer isso, crie dois conjuntos de resultados temporários: um com o bônus médio para cada cargo e outro com o bônus médio para cada região. Aqui está a consulta completa:

WITH avg_position AS (
    SELECT position, AVG(bonus) AS average_bonus_for_position
    FROM bonus_jan
    GROUP BY position),
    avg_region AS (
    SELECT region, AVG (bonus) AS average_bonus_for_region
    FROM bonus_jan
    GROUP BY region)    
SELECT b.employee_id, b.first_name, b.last_name, b.position, b.region, b.bonus, ap.average_bonus_for_position, ar.average_bonus_for_region
FROM bonus_jan b
JOIN avg_position ap
ON b.position = ap.position
JOIN avg_region ar
ON b.region = ar.region;

Após definir os conjuntos de resultados temporários avg_position e avg_region, você escreve a principal declaração SELECT para exibir os bônus de posição média e bônus de região juntamente com as informações de cada funcionário:

CTEs aninhados

Expressões comuns da tabela também podem ser aninhadas. Isto significa ter vários CTEs na mesma consulta onde pelo menos um CTE se refere a outro CTE. Isto será mais claro depois de olharmos um exemplo.

Digamos que você queira avaliar o desempenho de diferentes pontos de venda na rede de varejo do banco. Em particular, você gostaria de comparar o bônus médio entre os funcionários de cada estabelecimento com o bônus mínimo e máximo médio entre os estabelecimentos.

A lógica pode ser a seguinte:

1. Calcular o bônus médio entre os funcionários de cada ponto de venda (CTE: avg_per_outlet).
2. Encontre a média mínima de bônus entre pontos de venda (CTE: min_bonus_outlet).
3. Encontrar a média máxima de bônus entre pontos de venda (CTE: max_bonus_outlet).
4. Produza a identificação de cada estabelecimento junto com o bônus médio para este estabelecimento e os bônus mínimo e máximo médio entre estabelecimentos.

Para criar os CTEs min_bonus_outlet e max_bonus_outlet, você precisará fazer referência ao primeiro CTE, avg_per_outlet. Aqui está a consulta completa:

WITH avg_per_outlet AS (
    SELECT outlet, AVG(bonus) AS average_bonus_for_outlet
    FROM bonus_jan
    GROUP BY outlet),
    min_bonus_outlet AS (
    SELECT MIN (average_bonus_for_outlet) AS min_avg_bonus_for_outlet
    FROM avg_per_outlet),
    max_bonus_outlet AS (
    SELECT MAX (average_bonus_for_outlet) AS max_avg_bonus_for_outlet
    FROM avg_per_outlet)    
SELECT ao.outlet, ao.average_bonus_for_outlet, min.min_avg_bonus_for_outlet,
max.max_avg_bonus_for_outlet
FROM avg_per_outlet ao
CROSS JOIN min_bonus_outlet min
CROSS JOIN max_bonus_outlet max;

Note que existem três diferentes Expressões de Tabela Comum; duas delas (min_bonus_outlet e max_bonus_outlet) referem-se a outro CTE (avg_per_outlet). Isto os torna CTEs aninhados.

Na declaração principal do SELECT, exibimos a identificação do estabelecimento, o bônus médio para todos os funcionários deste estabelecimento e o bônus médio mínimo e máximo entre estabelecimentos. Para este fim, nós cruzamos os três conjuntos de resultados temporários. Aqui está o resultado desta consulta:

Para mais exemplos de CTES, veja LearnSQL.com.br's guias introdutórios sobre o que é um CTE e quando você deve utilizá-lo.

Por que usar um CTE?

Agora, você deve ter notado que na maioria dos casos, você poderia usar uma ou mais subconsultas em vez de CTEs. Então, por que usar um CTE?

• Expressões comuns de tabela organizam melhor as longas consultas. Múltiplas subconsultas muitas vezes parecem confusas.
• Os CTEs também tornam uma consulta mais legível, pois você tem um nome para cada uma das Expressões de Tabelas Comuns usadas em uma consulta.
• Os CTEs organizam a consulta de modo que ela reflita melhor a lógica humana. Com os CTEs, você começa definindo o(s) conjunto(s) de resultados temporários e depois se refere a ele(s) na consulta principal. Com as subconsultas, você começa com a consulta principal e depois coloca as subconsultas no meio da consulta.
• Finalmente, há também uma categoria específica de CTEs chamados CTEs recursivos que podem se referir a si mesmos. Estes CTEs podem resolver problemas que não podem ser resolvidos com outras consultas. As consultas recursivas são especialmente úteis no trabalho com dados hierárquicos.

Saiba mais sobre os CTEs recursivos em nossos guias detalhados sobre as capacidades de consultas recursivas SQL e consultas hierárquicas no PostgreSQL e Oracle.

Tempo para Praticar Expressões Comuns de Tabela!

Você está entusiasmado em aproveitar o poder dos CTEs em suas consultas SQL? Se você quer escrever CTEs como um profissional, você precisa de muita prática.

LearnSQL.com.br oferece um curso abrangente em Consultas Recursivas. Ele inclui 114 exercícios interativos que abrangem CTEs simples, CTEs aninhados, e CTEs recursivos. Este curso é uma oportunidade perfeita para aprender como gerenciar suas consultas SQL com Expressões de Tabela Comum, como e quando aninhar CTEs, e como usar CTEs recursivos.

Você gostaria de aprender mais sobre os CTEs SQL? Confira nossos guias para iniciantes:

• O que é um CTE?
• Quando devo usar uma Expressão de Tabela Comum (CTE)?
• Como Organizar Consultas SQL com CTEs

Feliz aprendizagem!