O que é um CTE?

Após dominar instruções como SELECT, DELETE, INSERT e GROUP BY, você pode procurar maneiras de melhorar a manutenção, reprodutibilidade e legibilidade do código. Nesse ponto, você provavelmente começará a aprender sobre conceitos SQL modernos que foram introduzidos no início dos anos 2000. Uma dessas técnicas SQL é o CTE? (expressão comum de tabela) -?um conjunto de resultados nomeado temporariamente. Neste artigo, você aprenderá o que é um CTE e como usá-lo para melhorar a manutenção e a legibilidade de seu código.

CTE: Definição e Sintaxe Básica

Uma expressão de tabela comum, ou CTE, é um conjunto de resultados temporário criado a partir de uma simples instrução SQL que pode ser usada em instruções subseqüentes SELECT, DELETE, INSERT, ou UPDATE.

Vamos começar com um exemplo. Considere as duas tabelas a seguir:

• job_offersO código de dados é uma tabela que contém funções de ciência de dados, com salários por nível e localização no Reino Unido.

  role	location	level	salary
  data scientist	London	entry	45000
  data scientist	outside London	entry	34000
  data scientist	London	mid	65000
  data scientist	outside London	mid	60000
  data scientist	London	tech lead	95000
  data scientist	outside London	tech lead	73000
  data scientist	London	director	140000
  data scientist	outside London	director	101000
  quantitative analyst	London	entry	55000
  quantitative analyst	outside London	entry	43000
  quantitative analyst	London	mid	83000
  quantitative analyst	outside London	mid	66000
  quantitative analyst	London	tech lead	100000
  quantitative analyst	outside London	tech lead	72000
  quantitative analyst	London	director	155000
  quantitative analyst	outside London	director	120000
  machine learning engineer	London	entry	44000
  machine learning engineer	outside London	entry	36000
  machine learning engineer	London	mid	67000
  machine learning engineer	outside London	mid	58000
  machine learning engineer	London	tech lead	95000
  machine learning engineer	outside London	tech lead	84000

• employee_occupationTabela que contém 5 funcionários da Empresa X, com suas respectivas ocupações e locais de trabalho.

  name	role	location
  Tim Smith	data scientist	London
  Joana Lo	quantitative analyst	outside London
  Ed Merith	machine learning engineer	London
  Maria Soldini	machine learning engineer	outside London
  Tina Morito	quantitative analyst	outside London

Agora, imagine que você gostaria de listar todos os funcionários com o salário médio que corresponde ao seu papel específico (coluna role). O que você precisaria fazer é:

1. Calcule o salário médio para cada função. Para isso, você pode usar o job_offers mesa.
2. Fundir os valores agregados com a tabela employee_occupation tabela (ou seja, juntá-los).

Aqui está o CTE para calcular e exibir o resultado desejado:

WITH average_salary AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
)
SELECT a.*, b.avg_salary  
FROM employee_occupation a 
LEFT JOIN average_salary b 
ON a.role = b.role;

Aqui está o que está acontecendo no código:

1. A definição do CTE começa com uma palavra-chave especial WITH.
2. Após o WITH, o CTE recebe um nome. Em nosso exemplo, o nome do CTE é average_salary.
3. O nome do CTE é seguido por uma palavra-chave especial AS.
4. A declaração SELECT está dentro dos parênteses, cujo conjunto de resultados é armazenado como CTE. Em nosso exemplo, o conjunto de resultados temporários average_salary é criado com um SELECT.
5. Agora que você tem um CTE, você pode usá-lo em uma declaração SQL subseqüente, referenciando-o como faria em qualquer outra tabela. Aqui, usamos nosso resultado temporário definido em uma declaração JOIN. A consulta principal é a seguinte, na qual o CTE average_salary é utilizado.
6. O conjunto de resultados temporários average_salary vive apenas dentro do escopo da declaração imediatamente após a cláusula WITH. Em nosso exemplo, isto significa que average_salary desaparece automaticamente depois que a consulta principal é executada e não pode ser usada em nenhum outro lugar do código. É como se você criasse uma tabela, utilizá-la em uma instrução SQL e depois excluí-la ou soltá-la de seu banco de dados.

Como resultado, SQL retorna para cada funcionário o salário médio para seu papel:

A sintaxe básica para o uso do CTE é a seguinte:

Como você pode ver na imagem, definimos um conjunto de resultados temporários (em nosso exemplo, average_salary) após o que o usamos nessa mesma declaração (em nosso exemplo, average_salary é usado na declaração JOIN ). Há um grande curso, Consultas Recursivas, oferecidas por LearnSQL.com.brSugiro começar com isto, se você quiser saber mais sobre a sintaxe do CTE.

Subconsultas vs. CTEs

Você deve ter notado que nosso último exemplo pode ser escrito com uma subconsulta como esta:

SELECT a.*, b.avg_salary  
FROM employee_occupation a 
LEFT JOIN 
  (SELECT role, avg(salary) AS avg_salary 
   FROM job_offers 
   GROUP BY role) b
ON a.role = b.role;

Embora escrito de forma diferente, ele retornará exatamente a mesma saída que nosso código CTE de antes. Aqui está o que este código com uma subconsulta faz:

• Dentro da declaração JOIN, temos uma subconsulta entre parênteses: "select role, avg(salary) as avg_salary from job_offers group by role" é uma subconsulta.
• O motor SQL executa esta subquisição primeiro, depois realiza a junção com o resultado retornado da subquisição.

Embora uma subquisição retorne o mesmo resultado que uma declaração que usa um CTE, eu sempre recomendo CTEs sobre subquisições em um caso como este. Por quê? Bem, eles tornam o código mais legível e compreensível. Consultas longas e complicadas podem ter muitas, muitas subconsultas que rapidamente se tornam difíceis de ler e seguir.

Múltiplos CTEs em uma declaração

Até agora, utilizamos apenas um resultado temporário estabelecido em uma cláusula WITH. E se tivermos a necessidade de criar mais CTEs em uma única declaração? Isto pode acontecer se você precisar reescrever uma declaração SQL que tenha muitas subconsultas. Bem, você pode fazer isso - é possível ter mais CTEs em uma única instrução SQL.

Vamos passar por um exemplo com duas subconsultas. Imagine que você perceba as diferenças salariais por local, além da diferença por função. Você deseja exibir informações adicionais em sua produção, a saber, o salário médio por função e local, e exibi-las com informações em nível de funcionário.

Neste caso, além da subconsulta como a seguinte, que calcula o salário médio por função:

SELECT role, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role

você precisa escrever outra subconsulta como esta:

SELECT role, location, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role, location

Usando estas subconsultas, sua última SELECT será parecida com esta:

SELECT a.*, b.avg_salary, c.avg_salary_additional 
FROM employee_occupation a
LEFT join 
  (SELECT role, avg(salary) as avg_salary 
   FROM job_offers 
   GROUP BY role) b 
ON a.role = b.role
LEFT JOIN 
  (SELECT role, location, avg(salary) AS avg_salary_additional 
   FROM job_offers 
   GROUP BY role, location) c
ON a.role = c.role AND a.location = c.location;

A consulta final agora parece muito mais complicada; é mais difícil de ser seguida.

Aqui está como se parece a mesma consulta usando CTEs:

WITH average_salary AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
average_salary_additional AS (
  SELECT 
    role, location, avg(salary) AS avg_salary_additional 
  FROM job_offers 
  GROUP BY role, location
)
SELECT a.*, b.avg_salary, c.avg_salary_additional 
FROM employee_occupation a
LEFT JOIN average_salary b 
  ON a.role = b.role
LEFT JOIN average_salary_additional c 
  ON a.role = c.role and a.location = c.location;

Parece melhor, certo? Aqui, definimos dois CTEs. O que se segue é o que está acontecendo neste código:

• Dois CTEs são definidos e utilizados dentro de uma única cláusula WITH: average_salary e average_salary_additional. Eles são separados por uma vírgula dentro da mesma declaração com a mesma. Cada subconsulta é nomeada separadamente, tornando sua última SELECT mais legível.
• O primeiro CTE SQL (average_salary) armazena o salário médio por função. O segundo CTE (average_salaries_additional) armazena o salário médio para cada combinação de função e localização na tabela job_offers.

• Uma vez definidos, ambos os CTEs são usados na consulta principal em uma única declaração. O resultado exibe todas as médias - média por função e a média para cada combinação de função e localização - com os detalhes de cada funcionário:

  name	role	location	avg_salary	avg_salary_additional
  Tim Smith	data scientist	London	76625	86250
  Ed Merith	machine learning engineer	London	64000	68666.6667
  Maria Soldini	machine learning engineer	outside London	64000	59333.3333
  Joana Lo	quantitative analyst	outside London	86750	75250
  Tina Morito	quantitative analyst	outside London	86750	75250

• Uma vez que esta instrução SQL única é executada e o resultado é exibido na tela, ambos os CTEs são descartados e ficam indisponíveis para uso posterior no código.

O mesmo resultado pode ser obtido com as funções SQL da janela e apenas um CTE, mas aqui usamos várias expressões comuns de tabela. Esta é uma boa ilustração de como dois CTEs podem ser usados em uma consulta.

Você deve ter notado que cada CTE de nosso exemplo lê uma tabela SQL chamada job_offers. Naturalmente, cada CTE pode ler tabelas diferentes do banco de dados ou mesmo ler outro CTE definido na mesma declaração. Ou seja, é permitido fazer ninhos quando se trabalha com CTEs - um CTE pode ler ou fazer referência a outro CTE. O único requisito é que eles devem ser definidos na mesma consulta WITH.

Aqui está um exemplo com os CTEs aninhados:

WITH avg_salary_per_role AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
min_avg_salary AS (
  SELECT min(avg_salary) AS min_avg_salary 
  FROM avg_salary_per_role
)
SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

Neste código:

• Definimos dois CTEs: avg_salary_per_role e min_avg_salary. O primeiro CTE (avg_salary_per_role) calcula o salário médio por função. O segundo CTE (min_avg_salary) lê o primeiro CTE (avg_salary_per_role) e calcula o mínimo dos salários médios em nível de função. Os salários médios foram calculados pelo primeiro CTE.

• A consulta principal utiliza ambos os CTEs em uma declaração JOIN e retorna a função com o menor salário médio entre todas as funções:

  role	avg_salary	min_avg_salary
  machine learning engineer	64000	64000

• Uma vez que esta instrução SQL é executada e o resultado é exibido na tela, ambos os CTEs são descartados e não estão mais disponíveis para uso posterior no código.

Criação de tabela vs. CTEs

Temos visto como a utilização de CTEs é uma ótima alternativa para subconsultas. É também uma ótima alternativa para criar uma mesa de verdade.

Os desenvolvedores freqüentemente criam tabelas temporárias no banco de dados, as utilizam na próxima consulta e depois as soltam. Esta abordagem pode ser substituída por uma que utilize CTEs.

Por que menciono isto? Antes de começar a trabalhar com CTEs, eu costumava fazer exatamente o que descrevi acima.

Considere uma consulta de nosso último exemplo que se parece com isto:

WITH avg_salary_per_role AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
min_avg_salary AS (
  SELECT min(avg_salary) AS min_avg_salary 
  FROM avg_salary_per_role
)
SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

Eu a teria escrito assim:

CREATE TABLE avg_salary_per_role AS
SELECT role, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role;

CREATE TABLE min_avg_salary AS
SELECT min(avg_salary) AS min_avg_salary 
FROM avg_salary_per_role;

SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

DROP TABLE avg_salary_per_role;
DROP TABLE min_avg_salary;

Embora ambas as soluções lhe dêem o mesmo resultado, é melhor usar os CTEs para que você não precise se preocupar com metadados... Você não precisa se lembrar de quedas na tabela, porque o motor SQL trata disso para você com CTEs. Legal, certo?

CTEs não-recursivos e recursivos

Até agora, discutimos os chamados CTEs "não-recursivos". Entretanto, geralmente dividimos os CTEs em dois tipos principais:

• CTEs não-recursivos
• CTEs recursivos

Um CTE recursivo é uma forma especial de CTEs aninhados. Um CTE recursivo é aquele que se refere a si mesmo dentro da mesma expressão de tabela comum.

Aqui é como se parece um CTE recursivo:

WITH recursive_cte (column_list)
AS
(
   -----(in this query we are referencing recursive_cte)----
)

SELECT *
FROM recursive_cte

Os CTEs recursivos trabalham com dados hierárquicos, portanto, os dados devem ser definidos corretamente. O código é executado até que todos os níveis de hierarquia tenham sido esgotados.

Lembro-me de quando escrevi meu primeiro CTE recursivo. Eu precisava extrair informações básicas sobre a estrutura organizacional em um banco. Um CTE recursivo é apropriado aqui, porque a estrutura organizacional em um banco é hierárquica (ou seja, tem uma estrutura em árvore):

• Há uma unidade principal à qual todas as unidades pertencem (a chamada "âncora"). Em um banco, este é o conselho fiscal.
• Há unidades organizacionais para as quais é definida uma hierarquia clara. Por exemplo, o grupo de análise de dados está sob o departamento de TI junto com outras unidades, o departamento de TI é uma parte da unidade de suporte aos negócios, e a unidade de suporte aos negócios está sob o conselho supervisor que é a unidade principal (âncora).

Como eu precisava do número de funcionários para cada unidade (âncora, pai, filho), o CTE recursivo era o caminho a seguir. Lembro como fiquei feliz quando meu script funcionou - ele tinha apenas uma declaração SQL! Eu poderia ter escrito uma consulta separada para cada departamento para o qual eu precisava das informações. Como eu usava um CTE recursivo, porém, obtive os resultados sem ter que escrever tantas consultas.

Não vamos mergulhar fundo nos CTEs recursivos aqui; para mais detalhes, confira nosso curso interativo on-line em LearnSQL.com.br que cobre este tópico. Se você quiser ver os CTEs em um exemplo real de negócios, sugiro Como Criar Relatórios Básicos em SQL-aqui, a agregação multinível, muitas vezes exigida nos relatórios comerciais, é demonstrada com o uso de CTEs. Além disso, há vários grandes artigos sobre vários tópicos de CTE com exemplos; você pode encontrá-los aqui e aqui.

Legibilidade de código e CTEs

Para resumir, aqui estão algumas razões pelas quais os CTEs são importantes, e por que e quando você deve usar os CTEs:

• Para evitar subconsultas nas instruções SQL. Isto se refere especificamente a situações nas quais estamos unindo várias tabelas em uma consulta principal e uma ou mais dessas tabelas é uma subconsulta. Neste caso, um CTE é uma ótima alternativa que irá melhorar a legibilidade de seu código.
• Para evitar a criação desnecessária de tabelas e visualizações em seu banco de dados. Os CTEs ajudam a evitar metadados desnecessários. É comum criar tabelas para serem usadas em joinins para que você não tenha que escrever subconsultas. Entretanto, ao invés de gerar uma tabela "real" cujos metadados são armazenados em um banco de dados, você pode usar um CTE como alternativa. Como é um conjunto de resultados nomeado temporariamente, ele não é armazenado em um banco de dados ou usado posteriormente em seu código, e seu código será tão legível quanto o código que cria tabelas reais.
• Para tornar seu código mais fácil de entender e manter. Os CTEs são particularmente úteis em consultas longas. Eles são uma maneira muito eficaz de manter consultas mais complicadas. Cada CTE tem um nome, e nomes intuitivos podem ajudar tremendamente na legibilidade e na manutenção do código. É muito mais fácil descobrir o que está acontecendo no código com nomes intuitivos para pedaços de código, já que você pode entender rapidamente o que é o pedaço de código por seu nome.

Percorremos a sintaxe básica e vários exemplos de como e quando você pode definir e usar os CTEs. Para entender melhor e praticar, eu recomendo um curso de LearnSQL.com.br chamado Consultas Recursivas. Neste curso, ensinamos interativamente como usar os CTEs (recursivos e não recursivos) em seu trabalho diário. Você também pode encontrar mais CTEs na prática em um curso interativo chamado Criando relatórios SQL básicos, no qual você pode aprender como usar CTEs em agregações multiníveis (como a criação de relatórios comerciais). Uma vez que você dominar o CTE, você ficará satisfeito com o quanto seus scripts SQL ficarão melhores!