20th Mar 2023 18 minutos de leitura

6 Exemplos úteis de CTEs no SQL Server

Índice

Sintaxe do CTE no SQL Server
6 Exemplos de CTEs no SQL Server
Quando você deve usar os CTEs?
Os CTEs são a porta para SQL Avançado Utilização!

Como você pode usar os CTEs no SQL Server em sua vida profissional cotidiana como um profissional de dados? Responderemos a esta pergunta dando-lhe seis exemplos.

CTE é a abreviação de Common Table Expression. Este é um recurso relativamente novo no SQL Server que foi disponibilizado com o SQL Server 2005.

Um CTE é um resultado temporário chamado CTE. Este resultado está disponível apenas para a consulta que o executa. Ele não é armazenado, portanto não ocupa espaço em disco. Um CTE é um pouco semelhante a uma tabela temporária e pode ser usado como qualquer outra tabela. Os CTEs são mais freqüentemente usados com uma declaração SELECT, mas também podem ser usados com INSERT, UPDATE e DELETE.

Os CTEs são um dos conceitos mais desafiadores no SQL Server. Para colher seus benefícios, sua abordagem para aprendê-los deve ser cuidadosamente estruturada e não apressada. Nosso curso Consultas Recursivas no MS SQL Server lhe mostrará como escrever um CTE simples no SQL Server para começar. Então você aprenderá a escrever vários CTEs, aninhá-los e usá-los dentro de SELECT, INSERT, UPDATE, e DELETE declarações. Finalmente, você aprenderá sobre a estrutura hierárquica e gráfica dos dados e como usar CTEs recursivos no SQL Server para consultar tais dados. Para garantir que você tenha prática suficiente, há 112 exercícios interativos a serem completados no curso.

Existem, é claro, algumas outras formas de aprender CTEs que você também pode verificar.

Sintaxe do CTE no SQL Server

Geralmente, a sintaxe do CTE no SQL Server é como o exemplo a seguir:

WITH cte AS (  
  SELECT
    ...
)

SELECT
  ...
FROM cte;

Os CTEs devem sempre começar com a palavra-chave WITH. Depois vem o nome do CTE, a palavra-chave AS, e os parênteses. Você define o CTE nesses parênteses. Defini-lo, como você verá em nossos exemplos, significa escrever a declaração SELECT. Você pode encontrar mais detalhes neste artigo, explicando o que é um CTE.

6 Exemplos de CTEs no SQL Server

1: Encontre os números médios mais altos e mais baixos de fluxos diários

Nos primeiros cinco exemplos, estaremos utilizando o mesmo conjunto de dados. Ele mostra alguns dados inventados a partir de uma plataforma de transmissão de música imaginária; vamos chamá-lo Terpsichore.

O conjunto de dados é composto de três tabelas. A primeira é artiste aqui está a consulta da tabela de criação. Esta tabela contém as seguintes colunas:

id - A identificação do artista e a chave primária da tabela.
artist_name - O nome do artista.

id	artist_name
1	Prince
2	Jimi Hendrix
3	Santana

Esta tabela mostra três artistas.

A tabela seguinte é albums. Aqui está a consulta para criá-lo. E aqui estão as colunas que ela contém:

id - O ID do álbum e a chave primária da tabela.
artist_id - O artista (e a chave estrangeira da mesa).
album_title - O título do álbum.
year_released - O ano do lançamento do álbum.

id	artist_id	album_title	year_released
1	2	Are You Experienced	1967
2	2	Axis: Bold as Love	1967
3	1	Dirty Mind	1980
4	2	Electric Ladyland	1968
5	3	Abraxas	1970
6	1	1999	1982
7	3	Santana III	1971
8	3	Santana	1969
9	1	Prince	1979
10	1	Controversy	1981

Há dez álbuns na tabela.

A última tabela é streams. Ele mostra dados de streaming para as canções individuais. Você pode criar a tabela usando esta consulta. E as colunas:

id - O ID do fluxo e a chave primária da tabela.
artist_id - O ID do artista e uma chave estrangeira.
album_id - O ID do álbum e uma chave estrangeira.
song_title - O nome da canção.
date - A data do córrego.
number_of_streams - O número de vezes que a música foi tocada em uma determinada data.
pay_per_stream - O valor (em dólares) que Terpsichore paga aos artistas por cada transmissão.

id	artist_id	album_id	song_title	date	number_of_streams	pay_per_stream
1	1	9	I Wanna Be Your Lover	2023-01-01	597	0.013
2	1	6	Little Red Corvette	2023-01-01	497	0.013
3	1	6	D.M.S.R.	2023-01-01	217	0.013
4	1	3	Uptown	2023-01-01	9748	0.013
5	1	3	Do It All Night	2023-01-01	208	0.013

Há 45 filas nesta tabela. Mostraremos apenas as cinco primeiras, para que você tenha a sensação da lógica da tabela.

Agora, o exemplo! Vamos começar escrevendo apenas um CTE no SQL Server. Faremos isso para calcular a média do maior e menor número de fluxos diários.

Aqui está o código; explicá-lo-emos a seguir:

WITH daily_streaming AS (
  SELECT date,
	   MIN(number_of_streams) AS minimum_streaming,
	   MAX(number_of_streams) AS maximum_streaming
  FROM streams
  GROUP BY date
)

SELECT AVG(minimum_streaming) AS average_minimum_daily_streaming,
	 AVG(maximum_streaming) AS average_maximum__daily_streaming
FROM daily_streaming;

Um CTE é brilhante para quebrar a lógica de qualquer cálculo no SQL Server.

Lembre-se, há várias músicas transmitidas em um dia. O objetivo de nosso CTE é obter o menor e maior número de transmissões a cada dia.

Como mencionado anteriormente, um CTE no SQL Server sempre começa com a palavra-chave WITH, que é seguida pelo nome CTE. Nosso CTE é chamado daily_streaming.

Após a palavra-chave AS vem o parênteses com uma declaração SELECT - ou seja, a definição CTE. Usamo-lo, juntamente com as funções agregadas MIN() e MAX(), para calcular o maior e menor número de fluxos por data.

A próxima declaração SELECT usa dados do CTE, referindo-se a ela na cláusula FROM. Como dissemos, um CTE pode ser usado como qualquer outra tabela. Neste SELECT, usamos a função agregada AVG() para obter a média dos picos e baixos diários dos fluxos.

A saída mostra que a média do ponto mais baixo é de 90 fluxos. A média dos fluxos diários superiores é de 8.367.

average_minimum_daily_streaming	average_maximum__daily_streaming
90	8,367

2: Calcular a média da taxa total paga por canção

Vamos agora praticar o que aprendemos. Escreveremos novamente um único CTE.

O problema que precisa ser resolvido é encontrar a taxa média total que Terpsichore pagou por cada música.

Aqui está a solução:

WITH paid_per_song AS (
  SELECT song_title,
	   SUM(number_of_streams * pay_per_stream) AS total_pay
  FROM streams
  GROUP BY id, song_title, pay_per_stream
)

SELECT song_title,
       AVG(total_pay) AS average_total_pay
FROM paid_per_song
GROUP BY song_title
ORDER BY average_total_pay DESC;

Usamos o CTE do SQL Server para calcular a taxa total paga por canção multiplicando o número de fluxos com o pagamento por fluxo, e depois somando-o usando a função agregada SUM().

Não há mudanças em relação à sintaxe do CTE: primeiro vem COM, depois o nome CTE, e AS vem depois disso.

Usamos então um SELECT que invoca o CTE para calcular o pagamento médio por canção. É simples: use AVG(), faça referência ao CTE em FROM, e agrupe pelo título da música.

A consulta retorna o seguinte resultado:

song_title	average_total_pay
Uptown	47.4803330
I Wanna Be Your Lover	36.8203330
Little Red Corvette	33.8693330
The Wind Cries Mary	23.6138660
Do It All Night	12.4063330
If 6 Was 9	7.7824000
Samba Pa Ti	7.5735000
All Along the Watchtower	5.2032000
Bold as Love	4.7424000
Burning of the Midnight Lamp	3.7333330
D.M.S.R.	3.1633330
Taboo	2.4871000
Jingo	2.1604000
Everything's Coming Our Way	1.5466000
Incident at Neshabur	0.9207000

Vemos que a música 'Uptown' ganhou $47.4803330 no total. A segunda e terceira músicas por rendimentos são 'I Wanna Be Your Lover' e 'Little Red Corvette'. Se você é fã deste artista, então não precisa de SQL para encontrar quem escreveu as três canções.

3: Encontre o Álbum Mais Transmitido de Cada Artista

Neste exercício, você tem que encontrar o álbum mais transmitido por cada artista. Produzir o nome do artista, o título do álbum e o número de transmissões por álbum.

Isto será, novamente, uma consulta com apenas um CTE. Entretanto, é um pouco mais complexo que os dois anteriores - há alguns JOINs e uma função de janela.

WITH album_streaming AS (
  SELECT artist_id,
	   album_id,
	   SUM(number_of_streams) AS streams_by_album,
	   RANK() OVER (PARTITION BY artist_id ORDER BY SUM(number_of_streams) DESC) AS streaming_rank
  FROM streams
  GROUP BY artist_id, album_id
)

SELECT artist_name,
       album_title,
	 streams_by_album
FROM album_streaming alst
JOIN albums al
ON alst.album_id = al.id
JOIN artist ar 
ON al.artist_id = ar.id
WHERE streaming_rank = 1;

Novamente, a sintaxe do CTE no SQL Server é familiar. Portanto, vamos nos concentrar no que este CTE faz. Nós o usamos para classificar os álbuns por artista. Primeiro, selecionamos o artista e os IDs dos álbuns. Em seguida, usamos SUM() para calcular o número de fluxos por álbum.

Agora vem a parte crucial - classificar a produção usando a função de janela RANK(). Depois que a função é invocada, vemos a cláusula OVER() - uma cláusula obrigatória para as funções de janela SQL. Particionamos o conjunto de dados pelo ID do artista e ordenamos os dados dentro de cada partição pelo número de streams, em ordem decrescente.

O que isso significa na prática? Significa que a função de janela classificará os álbuns para um artista, então a classificação é reiniciada quando a função atingir o próximo artista, e assim por diante. O álbum do artista com o maior número de córregos será classificado em primeiro lugar em sua partição.

Se você executar apenas esta declaração SELECT dentro do CTE, você terá esta saída:

artist_id	album_id	streams_by_album	streaming_rank
1	3	13,820	1
1	6	8,546	2
1	9	8,497	3
2	5	7,722	1
2	7	3,667	2
2	8	1,964	3
3	1	11,069	1
3	2	5,871	2
3	4	4,189	3

Como você pode ver, os álbuns do primeiro artista são classificados do primeiro para o terceiro, de acordo com o número de córregos. Quando chegamos ao segundo artista, a classificação recomeça. O mesmo acontece com o terceiro artista.

Agora, vamos ver o que a segunda declaração SELECT faz. Na verdade, não é nada complicado. Ela retorna o nome do artista e do álbum e o número de córregos. O que complica esta pergunta é que temos que juntar três tabelas.

A primeira junta é o CTE album_streaming. Então nos juntamos a ele com albums e depois com o artist mesa. No final, filtramos os dados usando a cláusula WHERE porque só estamos interessados no álbum mais transmitido.

Você terá este resultado:

artist_name	album_title	streams_by_album
Prince	Dirty Mind	13,820
Jimi Hendrix	Are You Experienced	11,069
Santana	Abraxas	7,722

Ele mostra que o álbum mais transmitido do Prince é 'Dirty Mind' com 13.820 transmissões. Para Jimi Hendrix, o álbum mais transmitido é 'Are You Experienced', e para Santana, é 'Abraxas'.

Esta solução utiliza as funções de janela, então aqui está um lembrete de como elas funcionam ao classificar os dados.

4: Calcular a média de transmissões por canção e compará-la com a média de transmissões por data

As coisas agora estão ficando mais complicadas. Mas não muito, não se preocupe. Estamos nos baseando no que aprendemos até agora sobre os CTEs no SQL Server.

Aqui, precisamos encontrar o número médio de transmissões por canção. Depois precisamos calcular o número médio de transmissões por data.

A saída deve mostrar ambas as métricas. Além disso, deve mostrar a diferença entre o fluxo médio por música e a média diária (como uma diferença percentual), o título da música e as datas.

Até agora, temos escrito consultas com um CTE. Desta vez, a solução consiste em dois CTEs. Vamos ver como isto funciona:

WITH streams_per_song AS (
  SELECT song_title,
	   AVG(number_of_streams) AS average_streams_per_song
  FROM streams
  GROUP BY song_title
),

streams_per_date AS (
  SELECT date,
	   AVG(number_of_streams) AS average_streams_per_date
  FROM streams
  GROUP BY date
)

SELECT song_title,
	 average_streams_per_song,
	 date,
	 average_streams_per_date,
	 (average_streams_per_song - average_streams_per_date)/CAST(average_streams_per_date AS DECIMAL(10,2))*100 AS diff_from_daily_average
FROM streams_per_song, streams_per_date;

O primeiro CTE é escrito como de costume. Utilizamo-lo para calcular o número médio de fluxos por canção com AVG().

Depois de fechar os parênteses, o primeiro CTE deve ser separado do segundo CTE com uma vírgula.

Em seguida, escrevemos o segundo CTE. Veja! Não há WITH! É isso mesmo. Ao escrever vários CTEs em uma consulta no SQL Server, você escreve WITH somente na frente do primeiro CTE. O segundo (e qualquer CTE subseqüente) começa com o nome do CTE; tudo o resto é o mesmo.

Esta segunda consulta é para calcular o número médio de fluxos por data. Novamente, usamos a função AVG().

O terceiro SELECT usa dados de ambos os CTEs. Ele retorna todas as colunas necessárias. A última coluna é diff_from_daily_average. Calculamos subtraindo os fluxos médios por data dos fluxos médios por canção. A diferença é dividida pelos fluxos médios por data e multiplicada por 100 para obter a porcentagem. Além disso, convertemos o resultado em um tipo de dado decimal usando a função CAST().

Devido ao tamanho da saída, mostraremos apenas as primeiras fileiras:

song_title	average_streams_per_song	date	average_streams_per_date	diff_from_daily_average
All Along the Watchtower	813	2023-01-01	1,031	-21.14
Bold as Love	741	2023-01-01	1,031	-28.13
Burning of the Midnight Lamp	583	2023-01-01	1,031	-43.45
D.M.S.R.	243	2023-01-01	1,031	-76.43
Do It All Night	954	2023-01-01	1,031	-7.47

Os dados mostram que o fluxo médio diário para 1 de janeiro de 2023 é de 1.031. 'All Along the Watchtower' está 21,14% abaixo dessa média. As próximas duas músicas estão 28,13% e 43,45% abaixo da média diária, e assim por diante.

5: Calcule a média mais alta e mais baixa de pagamento do álbum por artista

Vamos explicar o que queremos dizer com isso. Queremos primeiro encontrar a média de pagamento por álbum e data. Depois precisamos encontrar o menor e o maior valor de pagamento por álbum. Depois disso, queremos agregar os dados por artista. Junto com seu nome, precisamos mostrar o valor do salário mais baixo que o artista recebeu por um álbum. Precisamos fazer o mesmo com o pagamento mais alto por um álbum.

A solução no SQL Server contém dois CTEs. No entanto, desta vez é um CTE aninhado. É quando o segundo CTE faz referência ao primeiro CTE. Vamos ver como isto funciona:

WITH pay_per_album AS (
  SELECT album_id,
	   date,
	   AVG(number_of_streams * pay_per_stream) AS average_pay_per_album
  FROM streams
  GROUP BY album_id, date
),
	
min_max_average_pay AS (
  SELECT album_id,
	   MIN(average_pay_per_album) AS lowest_average_pay_by_album,
	   MAX(average_pay_per_album) AS highest_average_pay_by_album
  FROM pay_per_album
  GROUP BY album_id
)

SELECT artist_name,
	 MIN(lowest_average_pay_by_album) AS lowest_album_pay_by_artist,
	 MAX(highest_average_pay_by_album) AS highest_album_pay_by_artist
FROM min_max_average_pay mmap
JOIN albums al
ON mmap.album_id = al.id
JOIN artist ar
ON al.artist_id = ar.id
GROUP BY artist_name;

Tenha cuidado ao ler a explicação do código! É fácil se perder em todas estas agregações. Para facilitar o acompanhamento, copiei cada parte da consulta e a segui com uma explicação. Vamos começar com o primeiro CTE:

WITH pay_per_album AS (
  SELECT album_id,
	   date,
	   AVG(number_of_streams * pay_per_stream) AS average_pay_per_album
  FROM streams
  GROUP BY album_id, date
),

O primeiro CTE calcula o pagamento médio por álbum e a data. Isto é feito multiplicando o número de faixas pelo pagamento por faixa e usando AVG().

min_max_average_pay AS (
  SELECT album_id,
	   MIN(average_pay_per_album) AS lowest_average_pay_by_album,
	   MAX(average_pay_per_album) AS highest_average_pay_by_album
  FROM pay_per_album
  GROUP BY album_id
)

Ao escrever o segundo CTE no SQL Server, a sintaxe é a mesma do exemplo anterior - sem WITH adicional, comece com o nome do CTE, e separe os CTEs com uma vírgula. A única diferença é que desta vez o segundo CTE faz referência ao primeiro CTE, não ao conjunto de dados original.

Este CTE aninhado usa as funções MIN() e MAX() para encontrar a média mais baixa e mais alta de pagamento de cada álbum para todas as datas. O primeiro CTE é referenciado no FROM.

SELECT artist_name,
	 MIN(lowest_average_pay_by_album) AS lowest_album_pay_by_artist,
	 MAX(highest_average_pay_by_album) AS highest_album_pay_by_artist
FROM min_max_average_pay mmap
JOIN albums al
ON mmap.album_id = al.id
JOIN artist ar
ON al.artist_id = ar.id
GROUP BY artist_name;

Finalmente, há o SELECT que une o segundo CTE com o albums e artist tabelas. Aplicamos novamente as funções MIN() e MAX() no segundo resultado dos CTEs. Isto é para retornar apenas os valores dos álbuns mais baixos e mais altos pagos por cada artista.

Eis o que obtemos após a execução da consulta:

artist_name	lowest_album_pay_by_artist	highest_album_pay_by_artist
Jimi Hendrix	1.59	63.19
Prince	4.64	97.31
Santana	0.91	10.22

6: Encontre o caminho mais longo entre Rotterdam e Amsterdã

Esta é uma variação do problema do caminho mais curto na teoria dos gráficos, exceto que procuraremos o caminho mais longo.

Um gráfico é um tipo de estrutura de dados que consiste em nós ou pontos que estão conectados com bordas. Como eles estão conectados, é possível encontrar um caminho de um nó a outro, mesmo que não estejam diretamente conectados.

Pense nisso como um mapa de estradas. Este é exatamente o exemplo que vamos usar aqui. Abaixo está a tabela cities_distancemostrando as cidades e a distância entre elas. Use esta consulta para criar a tabela. Ela contém estas colunas:

city_from - A cidade de origem.
city_to - A cidade de chegada.
distância - A distância entre as duas cidades, em quilômetros.

Aqui estão os dados:

city_from	city_to	distance
Rotterdam	Amsterdam	78.20
Rotterdam	Gouda	24.10
Amsterdam	Gouda	72.50
Gouda	Leiden	34.10
Amsterdam	Leiden	50.00
Rotterdam	Leiden	35.40
Gouda	Utrecht	44.00
Utrecht	Amsterdam	52.40
Leiden	Gouda	34.10

Precisamos encontrar o caminho mais longo de Rotterdam a Amsterdã. O caminho deve incluir o nome de todas as cidades ao longo do caminho, separados por '/'. Além disso, devemos mostrar a extensão do caminho mais longo.

Quando dizemos o caminho mais longo, queremos excluir os caminhos circulares (onde é possível fazer voltas sem fim e construir a distância). Queremos que este caminho mais longo passe por qualquer cidade em particular apenas uma vez.

Para resolver este problema, usaremos um CTE recursivo. É uma consulta que se refere a si mesma até chegar ao final dos dados. Esta característica é ideal para consulta de dados gráficos, onde vários caminhos podem levar ao mesmo objetivo.

Vamos ver como este CTE recursivo funciona:

WITH longest_path AS (
  SELECT cd.city_to,
         CAST((cd.city_from + '/' + cd.city_to) AS VARCHAR(100)) AS path,
        cd.distance AS distance
   FROM cities_distance cd
   WHERE cd.city_from = 'Rotterdam'

UNION ALL

  SELECT cd.city_to,
         CAST((lp.path + '/' + cd.city_to) AS VARCHAR(100)) AS path,
         CAST((lp.distance + cd.distance) AS DECIMAL(10,2))
  FROM longest_path lp 
  INNER JOIN cities_distance cd
  ON cd.city_from = lp.city_to
  WHERE lp.city_to <> 'Amsterdam'
  AND lp.path NOT LIKE '%/' + cd.city_to + '/%'
)

SELECT TOP 1 lp.path,
	 lp.distance
FROM longest_path lp
WHERE lp.city_to = 'Amsterdam'
ORDER BY lp.distance DESC;

A sintaxe parece a mesma de antes - a consulta recursiva também começa com WITH no SQL Server.

Como de costume, há uma declaração SELECT entre parênteses. Há duas, para ser mais preciso. Vamos ver o que a primeira faz.

WITH longest_path AS (
  SELECT cd.city_to,
         CAST((cd.city_from + '/' + cd.city_to) AS VARCHAR(100)) AS path,
         cd.distance AS distance
  FROM cities_distance cd
  WHERE cd.city_from = 'Rotterdam'

A primeira SELECT em recursividade é chamada de membro âncora. É usado para selecionar o ponto de partida da recursividade. O ponto de partida será Rotterdam, que obtemos filtrando esta cidade em WHERE. A coluna city_to é usada para mostrar todos os destinos finais que podem ser alcançados diretamente de Roterdã. A coluna path listará todas as cidades de origem e destino. A duração dessa rota é mostrada na coluna distance.

Depois vem UNION ALL, que conectará os resultados da âncora e do membro recursivo, ou seja, o segundo SELECT. A união destas duas consultas é necessária para que a recursividade funcione.

Nota: Em alguns outros dialetos SQL, também é possível usar UNION. Entretanto, o SQL Server permite apenas UNION ALL.

Agora chegamos ao membro recursivo. Ele faz referência ao próprio CTE em FROM e o junta com a tabela cities_distance. Para que as consultas sejam sindicalizadas, ambas devem ter o mesmo número de colunas do mesmo tipo de dados. As duas primeiras colunas são as mesmas que no membro-âncora. A coluna longest_path soma todas as distâncias para chegar a todas as cidades a partir de Rotterdam.

SELECT cd.city_to,
       CAST((lp.path + '/' + cd.city_to) AS VARCHAR(100)) AS path,
       CAST((lp.distance + cd.distance) AS DECIMAL(10,2))
FROM longest_path lp 
INNER JOIN cities_distance cd
ON cd.city_from = lp.city_to
WHERE lp.city_to <> 'Amsterdam'
AND lp.path NOT LIKE '%/' + cd.city_to + '/%'
)

Também acrescentamos duas condições em WHERE. A primeira exclui todas as relações onde Amsterdã é o destino final; estamos procurando o caminho mais longo, não o mais curto, para Amsterdã. A segunda condição garante que qualquer nova cidade acrescentada ao caminho ainda não esteja incluída no caminho. Caso contrário, a consulta entrará em repetição interminável. Isto se refere ao que dissemos acima: o caminho mais longo não deve visitar a mesma cidade mais de uma vez.

Para entender melhor do que estamos falando, aqui está o resultado do CTE recursivo:

city_to	path	distance
Amsterdam	Rotterdam/Amsterdam	78.20
Gouda	Rotterdam/Gouda	24.10
Leiden	Rotterdam/Leiden	35.40
Gouda	Rotterdam/Leiden/Gouda	69.50
Utrecht	Rotterdam/Leiden/Gouda/Utrecht	113.50
Amsterdam	Rotterdam/Leiden/Gouda/Utrecht/Amsterdam	165.90
Leiden	Rotterdam/Gouda/Leiden	58.2
Utrecht	Rotterdam/Gouda/Utrecht	68.1
Amsterdam	Rotterdam/Gouda/Utrecht/Amsterdam	120.5

Você pode ver que existem três caminhos de Rotterdam a Amsterdã. Um é direto, com a distância de 78,20 km. A segunda e a terceira passam por outras cidades e percorrem 165,90 e 120,50 km, respectivamente.

Não é este o resultado final! Nossa solução também tem uma declaração SELECT que faz referência ao CTE:

SELECT TOP 1 lp.path,
	 lp.distance
FROM longest_path lp
WHERE lp.city_to = 'Amsterdam'
ORDER BY lp.distance DESC;

Este SELECT retorna o caminho e a distância. Usamos o comando TOP 1 combinado com ORDER BY para retornar o caminho mais longo de Rotterdam a Amsterdã. Classificamos os dados da maior para a menor distância, portanto a primeira linha também será o caminho mais longo.

Aqui está o resultado final:

path	distance
Rotterdam/Leiden/Gouda/Utrecht/Amsterdam	165.90

Esta saída mostra que o caminho mais longo de Rotterdã a Amsterdã é 165,9 km e passa por Leiden, Gouda, e Utrecht.

Se você não consegue se fartar deste tópico, aqui estão mais alguns exemplos de CTE.

Quando você deve usar os CTEs?

Como você viu em nossos exemplos, um CTE tem muitas finalidades no SQL Server.

Um deles é que geralmente melhora a legibilidade do código. Todas as soluções acima (exceto a recorrência) poderiam ter sido escritas com subconsultas. Mas isso tornaria o código muito mais longo e menos claro.

Quando você pensa nisso, as subconsultas são geralmente escritas contrariamente à lógica do problema que você está tentando resolver. Primeiro você tem a consulta principal, que utiliza a saída da subconsulta. Portanto, a subconsulta é geralmente o primeiro passo do cálculo, mesmo que não esteja posicionada dessa forma no código.

Os CTEs, por outro lado, podem ser escritos para seguir a lógica do problema. É possível escrever vários CTEs separados e fundir suas saídas nas declarações SELECT. Você também pode fazer referência a uma saída do CTE com a segunda consulta (ou terceira, quarta...), sendo que a última declaração SELECT é outro nível de cálculo.

Um dos exemplos também mostrou que você poderia classificar os dados no SQL Server usando uma função de janela e um CTE.

E se você quiser escrever consultas recursivas no SQL Server, você não pode fazer isso sem CTEs. Um CTE pode ser não recursivo, mas não existem consultas recursivas sem CTE. Junto com os gráficos, a recursividade é extremamente útil na consulta de estruturas hierárquicas, tais como organização de dados e árvores genealógicas.

Você pode encontrar mais informações sobre quando usar um CTE aqui.

Os CTEs são a porta para SQL Avançado Utilização!

Os CTEs são um dos conceitos mais avançados do SQL Server. Se você quiser abrir a porta para as habilidades avançadas do SQL Server, os CTEs são uma obrigação.

Conforme suas consultas no SQL Server se tornam mais complexas, você logo perceberá que aprender os CTEs foi uma das melhores decisões que você já tomou. Eles também são um trampolim para consultas recorrentes, permitindo que você consulte tipos incomuns de estruturas de dados no SQL Server, tais como hierarquias e gráficos.

Este artigo é apenas uma prévia do conhecimento que você pode encontrar em Consultas Recursivas no curso MS SQL Server. Portanto, não pare aqui. Há muito mais a aprender!

Tags: