5 exemplos práticos de SQL CTE

As expressões de tabela comuns (CTEs) foram introduzidas no SQL para melhorar a legibilidade e a estrutura das consultas SQL, especialmente aquelas que exigem várias etapas para obter o resultado necessário. Neste artigo, analisaremos vários exemplos para mostrar como as CTEs do SQL podem ajudá-lo com cálculos complexos e estruturas de dados hierárquicas.

Expressões de tabela comuns em SQL

As expressões de tabela comuns (CTEs), também chamadas de cláusulas WITH, permitem a criação de subconsultas nomeadas que são referenciadas posteriormente na consulta principal. As CTEs foram introduzidas no SQL para melhorar a legibilidade e a estrutura de uma instrução SQL.

A sintaxe básica do CTE é a seguinte:

WITH subquery_name AS
(SELECT … subquery ...)
SELECT … main query ...

Começamos com a palavra-chave WITH seguida do nome que atribuímos ao CTE (subconsulta). Em seguida, colocamos a palavra-chave AS e incluímos a subconsulta entre parênteses. Depois que o CTE é definido, passamos para a consulta principal, onde podemos fazer referência a esse CTE pelo seu nome.

Se você ainda não conhece os CTEs, talvez seja necessário consultar este artigo que explica com mais detalhes como funcionam os CTEs.

É possível ter vários CTEs em uma consulta, fazer referência a um CTE dentro de outro (ou seja, CTEs aninhados) ou até mesmo fazer referência a um CTE dentro dele mesmo (CTEs recursivos). Isso nos dá uma série de ferramentas e oportunidades.

Exemplos de CTEs SQL

Para mostrar como os CTEs podem ajudá-lo em várias tarefas analíticas, examinarei cinco exemplos práticos.

Começaremos com a tabela orderscom algumas informações básicas, como a data do pedido, o ID do cliente, o nome da loja, o ID do funcionário que registrou o pedido e o valor total do pedido.

Agora, vamos escrever algumas consultas SQL! Você também pode praticar CTEs SQL neste curso interativo Consultas Recursivas curso interativo que aborda todos os tipos de CTEs.

Exemplo 1

Em nosso primeiro exemplo, queremos comparar o valor total de cada pedido com o valor médio do pedido na loja correspondente.

Podemos começar calculando o valor médio dos pedidos de cada loja usando um CTE e adicionando essa coluna à saída da consulta principal:

WITH avg_per_store AS
  (SELECT store, AVG(amount) AS average_order
   FROM orders
   GROUP BY store)
SELECT o.id, o.store, o.amount, avg.average_order AS avg_for_store
FROM orders o
JOIN avg_per_store avg
ON o.store = avg.store;

Como você pode ver, nossa consulta começa com um CTE chamado avg_per_store. Usando esse CTE, criamos uma tabela que lista todas as lojas e o valor médio dos pedidos por loja. Em seguida, na consulta principal, selecionamos para exibir o ID do pedido, o nome da loja, o valor do pedido da tabela original orders e o valor médio do pedido para cada loja (avg_for_store) do CTE definido anteriormente.

Aqui está o resultado:

Com essa tabela, podemos ver como cada pedido se compara ao valor médio do pedido na loja correspondente.

Agora, vamos passar para um exemplo mais complexo.

Exemplo 2

Aqui, compararemos lojas diferentes. Especificamente, queremos ver como o valor médio do pedido de cada loja se compara ao mínimo e ao máximo do valor médio do pedido entre todas as lojas.

Como em nosso primeiro exemplo, começaremos calculando o valor médio dos pedidos de cada loja usando um CTE. Em seguida, definiremos mais dois CTEs:

• Para calcular o mínimo do valor médio do pedido entre todas as lojas.
• Para calcular o máximo do valor médio do pedido entre todas as lojas.

Observe que esses dois CTEs usarão o resultado do primeiro CTE.

Por fim, na consulta principal, juntaremos os três CTEs para obter as informações de que precisamos:

WITH avg_per_store AS (
    SELECT store, AVG(amount) AS average_order
    FROM orders
    GROUP BY store),
    min_order_store AS (
    SELECT MIN (average_order) AS min_avg_order_store
    FROM avg_per_store),
    max_order_store AS (
    SELECT MAX (average_order) AS max_avg_order_store
    FROM avg_per_store)
SELECT avg.store, avg.average_order, min.min_avg_order_store,
max.max_avg_order_store
FROM avg_per_store avg
CROSS JOIN min_order_store min
CROSS JOIN max_order_store max;

Como você pode ver, mesmo com vários CTEs aninhados, a consulta SQL permanece limpa e fácil de acompanhar. Se você fosse usar subconsultas, precisaria aninhar uma subconsulta dentro das outras duas e repeti-la várias vezes na mesma consulta. Aqui, com CTEs, simplesmente definimos todos os três CTEs no início e depois os referenciamos quando necessário.

Aqui está o resultado dessa consulta:

Você pode ver facilmente como cada loja se compara às outras em termos de valor médio do pedido. É claro que, quando você tem apenas três lojas, poderíamos simplesmente compará-las sem adicionar as colunas min_avg_order_store e max_avg_order_store. Entretanto, quando você precisa analisar o desempenho de muitas lojas por meio de métricas diferentes, essa abordagem pode ser muito útil.

Leia este guia para conhecer as práticas recomendadas do SQL CTE.

Exemplo 3

Em nosso próximo exemplo, continuaremos a comparar o desempenho de nossas lojas, mas com algumas métricas diferentes. Digamos que nossa empresa considere os pedidos abaixo de US$ 200 como pequenos e os pedidos iguais ou acima de US$ 200 como grandes. Agora, queremos calcular quantos pedidos grandes e pequenos cada loja teve.

Para realizar essa tarefa usando as cláusulas WITH, precisamos de duas expressões de tabela comuns:

• Para obter o número de pedidos grandes de cada loja.
• Para obter o número de pedidos pequenos de cada loja.

Algumas lojas podem não ter nenhum pedido grande ou pequeno, o que resulta em valores NULL. Precisamos nos certificar de que não perderemos nenhuma loja durante os JOINs. Por esse motivo, prefiro ter outro CTE que simplesmente produza uma lista de todas as lojas. Em seguida, na consulta principal, juntaremos esse CTE com os dois CTEs que contêm as métricas de pedidos grandes e pequenos:

WITH stores AS
   (SELECT store
    FROM orders
    GROUP BY store),
  big AS
  (SELECT store, COUNT(*) AS big_orders
   FROM orders
   WHERE amount >= 200.00
   GROUP BY store),
  small AS
  (SELECT store, COUNT(*) AS small_orders
   FROM orders
   WHERE amount < 200.00
   GROUP BY store)
SELECT s.store, b.big_orders, sm.small_orders
FROM stores s
FULL JOIN big b
ON s.store = b.store
FULL JOIN small sm
ON s.store = sm.store;

Portanto, nessa consulta, nós:

• Definimos o CTE stores para obter uma lista completa de lojas.
• Definimos o CTE big para calcular, para cada loja, o número de pedidos com o valor total igual ou superior a US$ 200.
• Definimos o CTE small para calcular, para cada loja, o número de pedidos abaixo de US$ 200.
• Junte os três CTEs.

Aqui está o resultado:

Agora podemos ver que a loja West tem um desempenho muito bom; todos os seus pedidos estão acima de US$ 200. A loja Center também tem um bom desempenho, com dois pedidos acima de US$ 200 e um pedido abaixo de US$ 200. Apenas metade dos pedidos da loja East é grande, com dois pedidos acima de US$ 200 e dois abaixo de US$ 200.

Exemplo 4

Para os próximos dois exemplos, usaremos a tabela abaixo com algumas informações básicas sobre os funcionários de nossa empresa. Especificamente, temos o ID do funcionário, o primeiro nome, o sobrenome, o ID do superior do funcionário, o departamento e o valor do último bônus.

Agora, vamos calcular o bônus médio por departamento e, em seguida, contar quantos funcionários tiveram bônus acima da média de seu respectivo departamento e quantos tiveram abaixo.

As expressões de tabela comuns podem ser muito úteis com esses cálculos complexos. Teremos três CTEs nessa consulta SQL:

• Para calcular o valor médio do bônus para cada departamento.
• Para calcular, por departamento, o número de funcionários cujos bônus estavam acima da média de seu respectivo departamento.
• Para calcular, por departamento, o número de funcionários cujos bônus estavam abaixo da média de seu respectivo departamento.

Na consulta principal, juntaremos os três CTEs.

WITH avg_bonus_department AS
    (SELECT department, AVG(bonus) AS average_bonus
    FROM employees
    GROUP BY department),
    above_average AS
    (SELECT e.department, count(*) AS employees_above_average
     FROM employees e
     JOIN avg_bonus_department avg
     ON e.department = avg.department
     WHERE bonus > average_bonus
     GROUP BY e.department),
     below_average AS
     (SELECT e.department, count(*) AS employees_below_average
     FROM employees e
     JOIN avg_bonus_department avg
     ON e.department = avg.department
     WHERE bonus < average_bonus
     GROUP BY e.department)
SELECT avg.department, avg.average_bonus, aa.employees_above_average, ba.employees_below_average
FROM avg_bonus_department avg
LEFT JOIN above_average aa
ON avg.department = aa.department
LEFT JOIN below_average ba
ON avg.department = ba.department;

Aqui está o resultado da consulta:

Como há apenas uma pessoa em Finanças, o bônus médio do departamento é exatamente igual ao bônus dessa pessoa. Como resultado, não temos ninguém no departamento financeiro cujo bônus esteja acima ou abaixo da média (refletido como valores NULL no resultado). O mesmo se aplica ao CEO.

Para o departamento de vendas, podemos ver que o bônus médio foi de US$ 828,57, e apenas duas das sete pessoas tiveram bônus acima da média do departamento.

Deixaremos que você interprete os resultados dos departamentos de Marketing e Operações da mesma forma e passaremos a um exemplo ainda mais complexo com uma consulta recursiva.

Exemplo 5

As expressões de tabela comuns podem fazer referência a si mesmas, o que as torna uma ferramenta perfeita para analisar estruturas hierárquicas. Vamos ver com um exemplo.

Usando as informações da tabela employees e da tabela orders podemos desenhar a seguinte estrutura organizacional de nossa empresa. O pessoal da loja é considerado parte da equipe de vendas. Além disso, na tabela orders tabela, podemos ver quais funcionários têm pedidos em quais lojas, portanto, podemos derivar a loja à qual cada vendedor pertence.

Agora, digamos que precisamos descobrir o nível de cada funcionário na estrutura organizacional (ou seja, o nível 1 é o CEO, o nível 2 é para seus subordinados diretos etc.). Podemos adicionar uma coluna que mostre isso com uma consulta recursiva:

WITH RECURSIVE levels AS (
  SELECT
    id,
    first_name,
    last_name,
    superior_id,
    1 AS level
  FROM employees
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
    employees.id,
    employees.first_name,
    employees.last_name,
    employees.superior_id,
    levels.level + 1
  FROM employees, levels
  WHERE employees.superior_id = levels.id
)

SELECT *
FROM levels;

Como você pode ver, o CTE levels nessa consulta faz referência a si mesmo. Ele começa selecionando o registro correspondente ao grande chefe, aquele que não tem um superior (ou seja, superior_id IS NULL). Atribuímos 1 ao nível dessa pessoa e, em seguida, usamos UNION ALL para adicionar outros registros, acrescentando um para cada nível de gerenciamento na estrutura organizacional.

Aqui está o resultado:

O tópico de consultas recursivas é bastante desafiador, portanto, não entrarei em mais detalhes aqui. Mas não deixe de conferir este artigo que explica os CTEs recursivos com exemplos, especialmente se você trabalha com dados hierárquicos.

E veja este artigo para obter mais exemplos de CTEs SQL.

Vamos praticar os CTEs SQL!

Espero que esses exemplos tenham lhe mostrado como os CTEs podem ser úteis para diferentes tarefas analíticas. Eles ajudam a melhorar a legibilidade e a estrutura de suas consultas SQL, ajudam com cálculos aninhados e complexos e são úteis para o processamento eficiente de dados hierárquicos. Saiba mais sobre quando usar CTEs neste artigo.

Se você quiser dominar as expressões de tabela comuns, recomendo começar com este Consultas Recursivas curso. Ele inclui 114 exercícios interativos que abrangem todos os tipos de CTEs, inclusive CTEs simples, CTEs aninhados e CTEs recursivos. Ao final do curso, você saberá como gerenciar consultas SQL com CTEs, como e quando aninhar CTEs e como usar CTEs recursivos para percorrer modelos de dados hierárquicos.

Se você quiser dominar outras ferramentas avançadas para análise de dados com SQL, considere fazer o curso SQL Avançado track! Ele vai além dos CTEs e também abrange as funções de janela e as extensões GROUP BY no SQL. E o siteLearnSQL.com.br oferece muitas maneiras diferentes de praticar esses conceitos avançados de SQL on-line.

Obrigado pela leitura e bom aprendizado!