O que é um CTE no SQL Server?

O que é um CTE, e como se escreve um CTE no SQL Server? Junte-se a nós em uma jornada onde veremos todo o uso típico de um CTE no SQL Server.

CTEs (ou Common Table Expressions) são um recurso SQL utilizado para definir um resultado nomeado temporariamente. Você pode pensar nele como uma tabela temporária cuja saída só está disponível quando a consulta principal é executada. Isto é prático porque o resultado dos CTEs não é armazenado em nenhum lugar, mas sempre pode ser referenciado dentro da consulta como qualquer outra tabela. Os CTEs são mais comumente usados na declaração SELECT, mas também podem ser usados nas declarações INSERT, UPDATE, e DELETE.

Os CTEs são uma característica relativamente nova do SQL. Elas foram introduzidas no SQL: padrão de 1999 (SQL 3). Em 2005, elas foram disponibilizadas no SQL Server 2005.

Você pode obter experiência prática com os CTEs no SQL Server em nosso curso interativo Consultas Recursivas no MS SQL Server. Você aprenderá CTE em sintaxe SQL Server, como usar mais de um CTE, como aninhá-los e como fazê-los funcionar em SELECT, INSERT, UPDATE, e DELETE. Há também uma seção que explica a recursividade e como escrever um CTE recursivo.

Cobriremos todos estes tópicos neste artigo. Entretanto, não podemos competir aqui com os 112 exercícios interativos que o curso oferece. Além do curso e deste artigo, há também algumas outras maneiras de aprender CTEs.

Sintaxe do CTE SQL Server

A sintaxe básica do CTE do SQL Server é:

WITH cte AS (  
  SELECT
    ...
)

SELECT
  ...
FROM cte;

Um CTE no SQL Server é iniciado usando a palavra-chave WITH. Em seguida, nós o seguimos com o nome CTE (aqui, cte), a palavra-chave AS, e os parênteses. Os parênteses mantêm a definição de CTE. Em termos simples, é uma declaração regular SELECT, somente na forma de um CTE.

A próxima parte vem depois dos parênteses e é chamada de consulta principal. O CTE não funciona sem ele. Esta consulta principal é, no exemplo genérico, uma declaração SELECT que faz referência ao CTE na cláusula FROM. Como já mencionamos, a consulta principal poderia ser uma declaração INSERT, UPDATE, ou DELETE em vez de SELECT.

Outra forma de escrever um CTE no SQL Server é especificando explicitamente as colunas, o que se parece com isto:

WITH cte (cte_columns) AS (
  SELECT
    ...	
)

SELECT
  ...
FROM cte;

A única diferença é que você define explicitamente as colunas do CTE antes da palavra-chave AS. Isto é útil quando as colunas CTE requerem aliases (por exemplo, quando elas contêm funções); a consulta é mais legível com os aliases atribuídos da forma acima.

Como você verá com os exemplos neste artigo, o principal argumento para usar o CTE no SQL Server é a melhor legibilidade do código. Você também pode dar uma olhada em algumas de suas outras vantagens.

Agora que você conhece a sintaxe básica do CTE, vamos usar cada abordagem em um exemplo. À medida que formos adiante, mostraremos pequenas mudanças na sintaxe, dependendo do uso da consulta.

Exemplos de CTE no SQL Server

Antes de escrever qualquer código, vamos nos familiarizar com o conjunto de dados. A tabela é flight_databaseque contém dados históricos de vôos. Ele possui as seguintes colunas:

• id - A identificação do registro e a chave primária da tabela (PK).
• flight_id - O número do vôo de acordo com as normas da IATA.
• airline - O nome da companhia aérea.
• flight_date - A data do vôo.
• departure_airport - O aeroporto do qual o vôo decolou.
• arrival_airport - O aeroporto de onde o vôo pousou.
• planned_departure - A hora em que o vôo deveria partir.
• actual_departure - A hora da partida efetiva do vôo.
• planned_arrival - A hora em que o vôo estava previsto para chegar.
• actual_arrival - A hora da chegada real do vôo.
• airport_distance - A distância entre os aeroportos de partida e chegada, em quilômetros.

Estes são dados fictícios para o Aeroporto Schiphol de Amsterdam. Todos os horários são GMT+1, o que nos facilita a comparação dos horários de partida e chegada.

Aqui estão algumas filas desta tabela:

A coluna id é única, pois é a chave primária da tabela. A coluna flight_id não é única, pois há dados para os mesmos vôos em datas diferentes.

Você pode criar este conjunto de dados usando o código no link. Se você precisar instalar o SQL Server, aqui estão as instruções de como fazê-lo.

Exemplo 1: CTE padrão no SQL Server

A tarefa aqui é escrever um CTE e encontrar os maiores atrasos de partida e chegada pelo número de vôo da IATA.

Aqui está a consulta:

WITH delay_times AS (
  SELECT 
    flight_id,
    flight_date,
    DATEDIFF(minute, planned_departure, actual_departure) AS departure_delay,
    DATEDIFF(minute, planned_arrival, actual_arrival) AS arrival_delay
  FROM flight_database
)

SELECT 
  flight_id,
  MAX(departure_delay) AS max_departure_delay,
  MAX(arrival_delay) AS max_arrival_delay
FROM delay_times
GROUP BY flight_id;

O CTE começa escrevendo a palavra-chave WITH. O nome do CTE é delay_times. Após a palavra-chave AS e o parêntese de abertura, há uma definição do CTE na forma de uma declaração em SELECT. Ele calcula a diferença entre a partida planejada e a partida real usando a função DATEDIFF(). A mesma abordagem é aplicada ao calcular a diferença entre a chegada planejada e a real. Ambos os resultados são em minutos. Como estas colunas utilizam funções, cada uma delas tem um pseudônimo.

Após fechar o parêntese, é hora de escrever a consulta principal. É uma declaração SELECT que faz referência ao CTE delay_times e usa duas vezes as funções agregadas do SQL Server MAX() para calcular o maior atraso de partida e chegada por vôo.

A saída lê da seguinte forma. O atraso máximo do voo DL 47 na partida foi de 5 minutos. Seu atraso máximo de chegada foi de 0; sempre chegou na hora certa.

Exemplo 2: CTE com Colunas Definidas Explicitamente

O exemplo a seguir é muito semelhante. A única diferença é que queremos encontrar os menores atrasos de partida e chegada por vôo. Além disso, vamos utilizar colunas explicitamente definidas. Faça-o assim:

WITH delay_times (flight_id, flight_date, departure_delay, arrival_delay) AS (
  SELECT 
    flight_id,
    flight_date,
    DATEDIFF(minute, planned_departure, actual_departure),
    DATEDIFF(minute, planned_arrival, actual_arrival)
  FROM flight_database
)

SELECT 
  flight_id,
  MIN(departure_delay) AS min_departure_delay,
  MIN(arrival_delay) AS min_arrival_delay
FROM delay_times
GROUP BY flight_id;

Este CTE é novamente chamado delay_times. Para definir as colunas do CTE explicitamente, escreva-as entre parênteses antes da palavra-chave AS.

O CTE em si não é muito diferente do anterior: ele usa novamente a função DATEDIFF() para calcular as diferenças de atraso. A única mudança é que os pseudônimos para estas duas (e as outras duas) colunas são definidos mais cedo, com o nome do CTE.

A consulta principal é quase a mesma de antes. A diferença é que agora ela usa a função MIN(), uma vez que o objetivo é calcular os menores atrasos.

O resultado mostra que o atraso mais curto do voo do DL 47 foi zero. Em outras palavras, foi pontual pelo menos uma vez. O vôo LH 2301 nunca foi pontual. O atraso foi de pelo menos 20 minutos tanto na partida quanto na chegada.

Exemplo 3: Um CTE aninhado no SQL Server

No SQL Server, um CTE aninhado ocorre quando há pelo menos dois CTEs e o segundo CTE faz referência ao primeiro. Vamos precisar disto no exemplo a seguir. A tarefa é calcular a duração média de vôo em minutos e a velocidade média de vôo em km/h.

Aqui está o código:

WITH flight_duration AS (
  SELECT 
    flight_id,
    DATEDIFF(MINUTE, actual_departure, actual_arrival) AS minutes_of_flight,
    airport_distance
  FROM flight_database
),

average_flight_duration AS (
  SELECT 
    flight_id,
    AVG(minutes_of_flight) AS average_flight_duration,
    airport_distance
  FROM flight_duration
  GROUP BY flight_id, airport_distance
)

SELECT 
  flight_id,
  average_flight_duration,
  airport_distance/(CAST(average_flight_duration AS DECIMAL(10,2))/60) AS average_flight_speed
FROM average_flight_duration;

O primeiro CTE é escrito como de costume no SQL Server: WITH o nome do CTE, AS, e a declaração SELECT. Esta consulta calcula a duração do vôo em minutos. Após fechar os parênteses, escreve-se o segundo CTE. Mas tenha cuidado; tem que haver uma vírgula separando os dois CTE. Além disso, quando você começa a escrever o segundo CTE, não há nenhuma palavra-chave WITH: você começa diretamente com o nome do CTE. Tudo o resto é como de costume. Este segundo CTE faz referência ao primeiro CTE na cláusula FROM para calcular a duração média do vôo por vôo para todas as datas.

Não há diferenças na sintaxe da consulta principal. Esta consulta faz referência ao segundo CTE. Ela calcula a velocidade média de vôo dividindo a distância entre os aeroportos pela duração média do vôo. O resultado é convertido em um número decimal. Também é dividido por 60, de modo que a velocidade média será mostrada em quilômetros por hora.

A saída mostra que, por exemplo, o tempo médio do vôo LH 987 para chegar ao destino é de 54 minutos, com a velocidade média de 407,14 km/h.

Se você quiser mais prática, aqui está outro exemplo de um CTE aninhado.

Exemplo 4: Um CTE em uma declaração de UPDATE

Neste exemplo, mostraremos a você como funcionam os CTEs na declaração UPDATE. Da forma como é mostrado abaixo, você também poderia usar a declaração INSERT.

A tarefa é atualizar o flight_database. Para ser mais preciso, atualizaremos sua coluna airport_distance. Ela contém atualmente dados em quilômetros, mas deve ser mudada para milhas.

Veja como fazer isso:

WITH distance_in_miles AS (
  SELECT 
    flight_id,
    airport_distance * 0.621371 AS airport_distance_miles
  FROM flight_database
)

UPDATE flight_database
SET airport_distance = airport_distance_miles
FROM distance_in_miles dim 
JOIN flight_database fd ON dim.flight_id = fd.flight_id;

Como sempre, comece com a palavra-chave WITH. O CTE distance_in_miles é usado para converter os quilômetros em milhas. É simples; multiplique os valores por 0,621371.

A consulta principal agora é UPDATE ao invés de SELECT. Nada difícil; basta seguir a sintaxe da declaração. Atualize a coluna airport_distance com os valores da coluna airport_distance_miles que aparece no CTE. Junte a tabela e o CTE, e pronto; a tabela é atualizada.

Aqui estão vários valores antes da atualização:

E aqui estão as mesmas linhas com os valores de distância atualizados:

Exemplo 5: Um CTE Recursivo no SQL Server

Nosso exemplo final é escrever um CTE recursivo no SQL Server. Este é um CTE que se refere a si mesmo. É mais freqüentemente usado ao consultar dados hierárquicos (como a organização da empresa) ou gráficos onde algumas ou todas as partes estão relacionadas (pense em um mapa rodoviário com as distâncias entre as cidades). O exemplo que vamos mostrar é um pouco mais fácil do que isso. O ponto principal é que você entende a recorrência e como traduzi-la em um CTE.

Digamos que o aeroporto tem um certo número de slots de vôo. Há também um preço por faixa horária. A cada ano, o aeroporto aumentará o número de slots em 150; o preço dos slots permanecerá o mesmo. Queremos mostrar o número de slots, o preço por slot, e a receita total de slots para o ano atual e os próximos quatro anos.

Aqui está o CTE recursivo para fazer isso:

WITH airport_slots AS (
  SELECT 
    1 AS id,
    400000 AS number_of_slots,
    20574421.00 AS price_per_slot,
    CAST(20574421.00 * 400000 AS DECIMAL) AS slot_revenue
		   
  UNION ALL

  SELECT 
    id + 1,
    number_of_slots + 150,
    price_per_slot,
    CAST(price_per_slot * (number_of_slots + 150) AS DECIMAL)
  FROM airport_slots
  WHERE id <= 4
)

SELECT *
FROM airport_slots;

Mais uma vez, a consulta recursiva começa com a palavra-chave WITH. O primeiro SELECT no CTE é chamado de membro âncora. Definimos os valores iniciais do ID, número de slots, e preço por slot. Além disso, multiplicamos estes dois valores para obter a receita dos slots.

Depois vem UNION ALL, que liga o membro âncora com o segundo SELECT (que é chamado de membro recursivo). UNION ALL dita que o número de colunas e seus tipos de dados devem ser os mesmos em ambas as declarações SELECT no CTE.

O membro recursivo faz referência ao próprio CTE. A cada repetição, o ID será incrementado por um e o número de slots por 150. O preço por ranhura permanece o mesmo. A receita por slot é o aumento do número de slots multiplicado pelo preço por slot.

Também usamos a cláusula WHERE para interromper a recorrência uma vez que o ID seja igual a quatro. A recursividade a transformará em cinco, e uma projeção de cinco anos é o que queremos (atual + próximos quatro anos).

Depois disso, é o mesmo que com qualquer CTE. Há o SELECT como a principal pergunta.

E esta é a saída:

Temos exemplos mais recorrentes de CTE em nosso blog. Nós nos agarramos à sintaxe do SQL Server neste artigo, mas você também pode aprender a fazer recursividade no PostgreSQL e Oracle.

Os CTEs do SQL Server fazem você melhorar!

Literalmente, eles fazem. A maioria das coisas que os CTEs fazem no SQL Server podem ser feitas com uma subconsulta. Mas imagine como seriam os códigos acima - não são bonitos! Uma das utilizações típicas dos CTEs no SQL Server é ajudar você a organizar longas consultas. Os CTEs tornam as consultas mais legíveis ao nomear partes da consulta. Dessa forma, você pode facilmente decompor cada parte de um cálculo complexo e fazer o cálculo lógico.

Sabendo disso, você e seu código T-SQL ficarão melhores. Há também uma característica SQL que não é possível sem os CTEs: consultas recursivas. Elas são indispensáveis ao consultar dados hierárquicos e gráficos. Você pode aprender todos os detalhes em nosso Consultas Recursivas no curso MS SQL Server.

E se você estiver entrevistando para uma posição SQL, certifique-se de passar por nossos cinco exemplos de CTE antes da entrevista!