SQL: Common Table Expressions

Common Table Expressions - SQL: Common Table Expressions

Olá, boas-vindas ao curso de Simplificando Consultas com CTE (Common Table Expressions ou, em português, Expressões de Tabela Comum). Eu sou o Igor do Nascimento Alves, instrutor na Escola de Dados, e vou te acompanhar neste conteúdo.

Audiodescrição: Igor se descreve como uma pessoa branca. Tem cabelos escuros e curtos, olhos castanhos e uma pinta acima do lábio superior. Usa uma camiseta cinza. No fundo, parede branca iluminada pelas cores azul e verde. À esquerda, vaso de planta para decoração.

Objetivo do curso

Esse curso foi pensado para quem já conhece Common Table Expressions (CTEs), mas deseja praticar essa técnica em diversos contextos diferentes. Dessa forma, podemos entender como elas realmente funcionam e aprofundar nesse conteúdo.

Caso você já tenha feito a Formação Conhecendo SQL, você já conhecerá esse conceito e será capaz de praticá-lo neste curso.

Vamos passar por alguns exemplos de CTE para, depois de relembrar o conceito, praticá-lo com uma lista de exercícios. Traremos diversos problemas de dados que serão respondidos através de consultas utilizando CTEs.

Também contamos com um glossário na atividade seguinte a este vídeo, onde podemos esclarecer dúvidas, por exemplo, como as CTEs funcionam para múltiplas tabelas. Caso a dúvida persista, conte com a comunidade tanto no Discord quanto no Fórum da Alura para te ajudar.

Vamos conferir nesse conteúdo?

Tipos de CTEs

Primeiramente, vamos relembrar são os tipos de CTEs com os quais podemos trabalhar:

• CTE simples;
• CTE com múltiplas consultas.

Vamos explorar isso?

Estrutura da CTE

Uma CTE é composta pela palavra reservada WITH, seguida do nome da CTE, que será o nome de uma tabela temporária que estamos criando. Em seguida, usamos a cláusula AS e, entre parênteses, a consulta que gerará essa tabela temporária.

Depois de fechar os parênteses, podemos acessar essa tabela da mesma forma que acessaríamos qualquer outra através do SELECT.

WITH NomeCTE AS (
    -- Consulta SQL
)
SELECT * FROM NomeCTE;

Exemplo de CTE simples

Considerando uma tabela de vendas com as colunas Cliente, Produto, Categoria, Valor e Ano, vamos construir algumas CTEs para responder dúvidas de negócio.

Resultado da consulta SELECT FROM Vendas:

Cliente	Produto	Categoria	Valor	Ano
Ana	Notebook	Eletrônicos	3000	2023
Bruno	Smartphone	Eletrônicos	2000	2023
Ana	Livro A	Livros	50	2023
Carla	Livro B	Livros	30	2023
Daniel	Cadeira Gamer	Móveis	500	2023
Ana	Teclado	Eletrônicos	150	2023
Elisa	Smartphone	Eletrônicos	2000	2022
Bruno	Notebook	Eletrônicos	3000	2022
Carla	Livro C	Livros	40	2022
Daniel	Mesa	Móveis	600	2022

A primeira CTE simples que vamos montar será para calcular o total de vendas. Imaginemos que temos essa tabela de Vendas com essas colunas e queremos calcular a soma dos produtos por categoria.

Criamos essa tabela CTE temporária da seguinte forma:

WITH TotalPorCategoria AS (
    SELECT Categoria, SUM(Valor) AS TotalVendas
    FROM Vendas
    GROUP BY Categoria
)

Essa consulta agrupa todas as categorias e resume as informações somando o valor de venda de cada produto daquela categoria. O resultado será uma tabela contendo o registro de cada categoria e seu total de vendas.

Podemos ver isso utilizando a CTE na consulta seguinte:

SELECT * FROM TotalPorCategoria;

O resultado dessa consulta é a tabela temporária TotalPorCategoria com duas colunas: Categoria e TotalVendas, com os valores de vendas de eletrônicos, livros e móveis.

Mas, sem a CTE, teríamos o mesmo resultado. Então, como agregamos valor? A consulta pode se tornar complexa se quisermos fazer operações dentro dela. Separando cada um dessas consultas em uma CTE diferente, fica mais simples trabalhar com os dados. Assim, será mais simples fazer uma modificação no código.

Por exemplo, se quisermos mudar como calculamos o total de vendas, basta alterar a CTE correspondente. Assim, o código fica mais organizado e fácil de entender.

Exemplo de CTE com múltiplas consultas

Vamos conferir um exemplo mais elaborado de CTE com múltiplas consultas. Além da CTE que fizemos anteriormente de TotalPorCategoria, agora queremos calcular a média por categoria.

Para isso, acrescentamos uma vírgula no final dos parênteses do WITH para digitar a nova CTE - sem precisar repetir o WITH. O nome dessa CTE será MediaPorCategoria:

WITH TotalPorCategoria AS (
    SELECT Categoria, SUM(Valor) AS TotalVendas
    FROM Vendas
    GROUP BY Categoria
),
MediaPorCategoria AS (
    SELECT Categoria, AVG(Valor) AS MediaVendas
    FROM Vendas
    GROUP BY Categoria
)

Essa consulta agrupa todas as categorias e calcula a média do valor de venda dos produtos de cada categoria.

Novamente, poderíamos fazer essa consulta em uma única operação, mas o exemplo foi simplificado para focar no que realmente importa: temos duas consultas separadas que fazem cálculos diferentes, uma somando o valor de vendas e outra calculando a média das vendas. Com isso, conseguimos fazer uma operação em cima delas depois.

Dessa forma, podemos combinar os valores das colunas de Categoria, TotalVendas e MediaVendas que vem das tabelas TotalPorCategoria (apelidada como T) e MediaPorCategoria (apelidada como M). Ambas são unidas com base na coluna Categoria, através da cláusula JOIN:

SELECT T.Categoria, T.TotalVendas, M.MediaVendas
FROM TotalPorCategoria T
JOIN MediaPorCategoria M ON T.Categoria = M.Categoria;

Estamos juntando essas duas tabelas temporárias que criamos anteriormente pela categoria. Assim, o total de valor de eletrônicos ficará na mesma linha que a média de eletrônicos, por exemplo.

Podemos juntar CTEs para resolver problemas de maneira simples. A parte de calcular a média está separada em um bloco, a parte do total por categoria está em outro bloco, e a consulta principal é um terceiro bloco.

Essa modularização torna entendimento, pois cada parte tem um objetivo claro. Além de facilitar alterações no código.

O resultado final é uma tabela com três colunas: categoria, total de vendas e média de vendas. Assim, temos os cálculos em relação às categorias de eletrônicos, livros e móveis.

Dessa forma, pegamos um resultado complexo e o dividimos em módulos separados.

Dicas práticas

Vamos trazer um último exemplo prático. Além da CTE de MediaPorCategoria, queremos calcular o gasto total dos clientes em cada categoria. Por isso, criamos uma segunda CTE, chamada ClientesGastos.

Para isso, selecionamos as colunas Cliente e Categoria da tabela Vendas, bem como calculamos a soma da coluna Valor que será o TotalCliente. Além disso, agruparemos tanto pelo Cliente quanto pela Categoria através do GROUP BY.

WITH MediaPorCategoria AS (
    SELECT Categoria, AVG(Valor) AS MediaCategoria
    FROM Vendas
    GROUP BY Categoria
),
ClientesGastos AS (
    SELECT Cliente, Categoria, SUM(Valor) AS TotalCliente
    FROM Vendas
    GROUP BY Cliente, Categoria
)

Agora, juntamos essas duas tabelas temporárias.

Para isso, selecionamos as colunas Cliente, Categoria e TotalCliente da CTE ClientesGastos (apelidada como C). Em seguida, a unimos com a CTE MediaPorCategoria (apelidada como M) base na coluna Categoria.

Por fim, adicionamos um filtro para buscar apenas clientes que gastaram um valor total (TotalCliente) superior à média da categoria (MediaCategoria).

SELECT C.Cliente, C.Categoria, C.TotalCliente
FROM ClientesGastos C
JOIN MediaPorCategoria M ON C.Categoria = M.Categoria
WHERE C.TotalCliente > M.MediaCategoria;

Com isso, conseguimos um resultado onde temos o nome dos clientes que tiveram uma compra maior que a média de gastos em uma das categorias - seja eletrônicos, livros ou móveis.

Esse é um código mais elaborado, com a construção de duas tabelas diferentes e uma terceira consulta resultante, ligada à dúvida de negócio que queremos responder. Imagine ter que montar tudo isso em uma única consulta; seria extenso e confuso.

Resumo

Neste conteúdo, estamos interessados em reforçar os conceitos de CTEs simples, onde queremos modularizar um único código, e CTEs mais complexas, que envolvem diversas construções de tabelas temporárias para responder a uma dúvida de negócio.

Agora que entendemos o que queremos aprender neste curso, te convidamos a realizar a próxima atividade. Temos um glossário sobre CTEs e, principalmente, atividades com contextos e dúvidas de negócio que devem ser respondidas utilizando CTEs.

Novamente, qualquer dúvida, pode procurar o fórum ou a comunidade do Discord. Estamos a postos para te ajudar nesse momento de prática e reforço dos conceitos. Até daqui a pouco!

Sobre o curso SQL: Common Table Expressions

O curso SQL: Common Table Expressions possui 11 minutos de vídeos, em um total de 13 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Common Table Expressions