O que é uma expressão de tabela comum (CTE) em SQL?

A expressão de tabela comum (CTE) é uma construção poderosa do SQL que ajuda a simplificar uma consulta. As CTEs funcionam como tabelas virtuais (com registros e colunas), criadas durante a execução de uma consulta, usadas pela consulta e eliminadas após a execução da consulta. Os CTEs geralmente atuam como uma ponte para transformar os dados das tabelas de origem no formato esperado pela consulta.

Uma expressão de tabela comum, ou CTE, é um conjunto de resultados nomeado temporário criado a partir de uma instrução SELECT simples que pode ser usada em uma instrução SELECT subsequente. Cada CTE SQL é como uma consulta nomeada, cujo resultado é armazenado em uma tabela virtual (um CTE) para ser referenciado posteriormente na consulta principal.

A melhor maneira de aprender expressões de tabela comuns é praticar. Recomendo o curso interativo da LearnSQL.com.brConsultas Recursivas interativo. Ele contém mais de 100 exercícios que ensinam CTEs, começando com o básico e avançando para tópicos avançados, como expressões recursivas de tabelas comuns.

Os CTEs ajudam a simplificar as consultas

Vamos começar com a sintaxe de uma expressão de tabela comum.

WITH my_cte AS (
  SELECT a,b,c
  FROM T1
)
SELECT a,c
FROM my_cte
WHERE ....

O nome desse CTE é my_ctee a consulta CTE é SELECT a,b,c FROM T1. O CTE começa com a palavra-chave WITH, depois da qual você especifica o nome do seu CTE e, em seguida, o conteúdo da consulta entre parênteses. A consulta principal vem após o parêntese de fechamento e se refere ao CTE. Aqui, a consulta principal (também conhecida como consulta externa) é SELECT a,c FROM my_cte WHERE ….

Há muitos exemplos de nível básico nesse excelente artigo introdutório sobre CTEs. Outros artigos introdutórios incluem "Improving Query Readability with Common Table Expressions" (Melhorando a legibilidade da consulta com expressões comuns de tabela) e "When Should I Use a Common Table Expression (CTE)?" (Quando devo usar uma expressão comum de tabela (CTE)?), que explicam as expressões comuns de tabela.

Aprendendo expressões comuns de tabela SQL por meio de exemplos

Nesta seção, apresentamos alguns exemplos de consultas SQL usando expressões de tabela comuns. Todos os exemplos são baseados em um banco de dados de uma cadeia de lojas de telefones celulares. A tabela salesmostrada abaixo, tem um registro por produto vendido:

No primeiro exemplo, obtemos um relatório com os mesmos registros da tabela sales mas adicionamos uma coluna extra com o preço do item mais caro vendido na mesma filial naquele dia. Para obter o preço do item mais caro, usamos uma expressão de tabela comum como esta:

WITH highest AS (
  SELECT
    branch,
    date,
    MAX(unit_price) AS highest_price
  FROM sales
  GROUP BY branch, date
)
SELECT
  sales.*,
  h.highest_price
FROM sales
JOIN highest h
  ON sales.branch = h.branch
    AND sales.date = h.date

Essa consulta define um CTE SQL chamado highest cujo resultado cria uma tabela virtual. A tabela virtual tem as colunas branch, date e highest_price, que contêm o nome da filial, a data e o preço unitário mais alto vendido naquele dia nessa filial, respectivamente.

Em seguida, é executada a consulta externa, que usa a tabela virtual highest tabela virtual como se fosse uma tabela normal. Por fim, juntamos o resultado do CTE highest com a tabela sales tabela.

O resultado de toda a consulta é mostrado abaixo:

No próximo exemplo, geramos um relatório com a maior receita diária por filial.

WITH daily_revenue AS (
  SELECT
    branch,
    date,
    SUM(unit_price * quantity) AS daily_revenue
  FROM   sales
  WHERE EXTRACT(YEAR FROM date) = 2021
  GROUP BY 1,2
)
SELECT
  branch,
  MAX(daily_revenue) max_daily_revenue
FROM daily_revenue
GROUP BY 1
ORDER BY 2 DESC

Nessa consulta, um CTE chamado daily_revenue tem colunas branch, date e daily_revenue para cada data em 2021. Em seguida, na consulta externa, obtemos o maior valor de receita para cada filial em 2021. O relatório é ordenado por max_daily_revenue em ordem decrescente.

Abaixo estão os resultados dessa consulta.

Uso de CTEs em consultas SQL Avançado

Você pode definir dois ou mais CTEs e usá-los na consulta principal. No próximo exemplo, mostraremos como dividir e organizar uma consulta longa usando CTEs SQL. Ao nomear diferentes partes da consulta, os CTEs facilitam a leitura da consulta.

Suponhamos que queremos um relatório com a receita mensal total em Londres em 2021, mas também queremos a receita de cada filial em Londres no mesmo relatório. Aqui, criamos dois CTEs e depois os unimos na consulta principal.

WITH london1_monthly_revenue AS (
  SELECT
    EXTRACT(MONTH FROM date) as month,
    SUM(unit_price * quantity) AS revenue
  FROM sales
  WHERE EXTRACT(YEAR FROM date) = 2021
    AND branch = 'London-1'
  GROUP BY 1
),
london2_monthly_revenue AS (
  SELECT
    EXTRACT(MONTH FROM date) as month,
    SUM(unit_price * quantity) AS revenue
  FROM sales
  WHERE EXTRACT(YEAR FROM date) = 2021
    AND branch = 'London-2'
  GROUP BY 1
)
SELECT
  l1.month,
  l1.revenue + l2.revenue AS london_revenue,
  l1.revenue AS london1_revenue,
  l2.revenue AS london2_revenue
FROM london1_monthly_revenue l1, london2_monthly_revenue l2
WHERE l1.month = l2.month

Na consulta acima, definimos dois CTEs, london1_monthly_revenue e london2_monthly_revenuepara obter a receita mensal em 2021 para cada filial em Londres. Por fim, juntamos os dois CTEs usando a coluna do mês e calculamos a receita total de Londres somando as receitas das duas filiais.

O resultado da consulta está abaixo:

No exemplo a seguir, obtemos um relatório para informar a cada filial a data em que o maior tíquete (ou seja, o valor da combinação item-quantidade) foi vendido e o valor desse tíquete. Para fazer isso, precisamos criar um CTE que classifique os tíquetes (a coluna position é a classificação) de cada filial pelo valor do tíquete.

WITH tickets AS (
  SELECT distinct
    branch,
    date,
    unit_price * quantity AS ticket_amount,
    ROW_NUMBER() OVER (
      PARTITION BY branch
      ORDER by unit_price * quantity DESC
    ) AS position
  FROM sales
  ORDER BY 3 DESC
)
SELECT
  branch,
  date,
  ticket_amount
FROM tickets
WHERE position =1

Na consulta acima, criamos um CTE com as colunas branch, date, ticket_amount e position. Em seguida, na consulta externa, filtramos apenas os registros com position = 1 para obter o que desejamos, o maior tíquete por filial.

O resultado da consulta é mostrado abaixo:

CTEs aninhados em consultas SQL

O próximo exemplo mostra um CTE aninhado. A ideia é gerar um relatório com todos os itens com preço acima de US$ 90 e a quantidade desses itens vendidos pela filial de Londres-2.

WITH over_90_items AS (
  SELECT DISTINCT
    item,
    unit_price
  FROM sales
  WHERE unit_price >=90
),
london2_over_90 AS (
  SELECT
    o90.item,
    o90.unit_price,
    coalesce(SUM(s.quantity), 0) as total_sold
  FROM over_90_items o90
  LEFT JOIN sales s
  ON o90.item = s.item AND s.branch = 'London-2'
  GROUP BY o90.item, o90.unit_price
)
SELECT item, unit_price, total_sold
FROM   london2_over_90;

O primeiro CTE é over_90_itemsque seleciona todos os itens com preço maior ou igual a US$ 90. O segundo CTE é london2_over_90que seleciona a quantidade vendida por London-2 para cada item incluído em over_90_items. Essa consulta tem um CTE aninhado - observe o FROM no segundo CTE referindo-se ao primeiro. Usamos LEFT JOIN sales porque London-2 pode não ter vendido todos os itens em over_90_items.

O resultado da consulta é:

Antes de ir para a próxima seção, tenho alguns artigos para sugerir sobre expressões de tabela comuns. Tanto o "SQL CTEs Explained with Examples" quanto o "Where Can I Find Good SQL CTE Exercises?" têm muitos exemplos e exercícios.

Consultas Recursivas e expressões comuns de tabela

Em bancos de dados relacionais, é comum ter tabelas que representam hierarquias de dados, como funcionário-gerente, parte-subparte ou pai-filho. Para percorrer essas hierarquias em qualquer direção (de cima para baixo ou de baixo para cima), os bancos de dados usam uma construção chamada CTEs recursivos.

RECURSIVE é uma palavra reservada para definir um CTE para percorrer uma estrutura de dados recursiva. A forma da consulta recursiva é a seguinte:

WITH RECURSIVE  cte_name AS (
     CTE_query_definition  -- non recursive query term
UNION ALL
     CTE_query_definition  -- recursive query term
)
SELECT * FROM cte_name;

As consultas recursivas estão fora do escopo deste artigo introdutório, mas tenho três outras sugestões para quem quiser saber mais sobre o assunto: "How to Organize SQL Queries with CTEs", "Doit in SQL: Recursive SQL Tree Traversal" e "Get to Know the Power of SQL Consultas Recursivas." Eles explicam detalhadamente as consultas recursivas com muitos exemplos.

As CTEs do SQL são um recurso poderoso

As expressões de tabela comuns são um recurso poderoso da linguagem SQL. Elas nos permitem criar consultas mais legíveis e gerenciar as diferenças de formato entre os dados da tabela e os dados do relatório. Neste artigo, abordamos o que é um CTE e como usá-lo em diferentes tipos de consultas. Também mencionamos que os CTEs podem ser usados em consultas recursivas.

Recomendo o curso Consultas Recursivas em LearnSQL.com.brno qual você aprende interativamente a trabalhar com expressões de tabela comuns em SQL. Você também aprenderá a processar estruturas de dados recursivas, como gráficos e árvores em SQL, usando CTEs recursivos.

Além disso, confira a trilha SQL Avançadoem que você vai além dos conceitos básicos para se tornar um mestre em SQL. Se você precisar se preparar para uma entrevista sobre SQL, há um artigo interessante, "Top 5 SQL CTE Interview Questions", que discute expressões de tabela comuns sob outra perspectiva.

Se você aprendeu a usar CTEs, está um passo à frente no caminho para se tornar um desenvolvedor de SQL. Continue crescendo!