¿Qué es una CTE en SQL Server?

¿Qué es una CTE y cómo se escribe una CTE en SQL Server? Acompáñenos en un viaje en el que veremos todos los usos típicos de una CTE en SQL Server.

CTEs (o Expresiones de Tabla Comunes) son una característica SQL usada para definir un resultado temporal con nombre. Puedes pensar en ello como una tabla temporal cuya salida sólo está disponible cuando se ejecuta la consulta principal. Esto es práctico porque el resultado de las CTEs no se almacena en ningún sitio, sino que siempre puede ser referenciado dentro de la consulta como cualquier otra tabla. Las CTEs se utilizan más comúnmente en la sentencia SELECT, pero también se pueden utilizar en las sentencias INSERT, UPDATE, y DELETE.

Las CTEs son una característica relativamente nueva de SQL. Se introdujeron en el estándar SQL: 1999 (SQL 3). En 2005, estuvieron disponibles en SQL Server 2005.

Puede obtener experiencia práctica con CTEs en SQL Server en nuestro curso interactivo Consultas recursivas y expresiones de tabla comunes en MS SQL Server. Aprenderá la sintaxis de las CTE en SQL Server, cómo utilizar más de una CTE, cómo anidarlas y cómo hacerlas funcionar en SELECT, INSERT, UPDATE, y DELETE. También hay una sección que explica la recursividad y cómo escribir una CTE recursiva.

Cubriremos todos estos temas en este artículo. Sin embargo, no podemos competir aquí con los 112 ejercicios interactivos que ofrece el curso. Aparte del curso y de este artículo, existen otras formas de aprender CTEs.

Sintaxis CTE de SQL Server

La sintaxis básica de SQL Server CTE es:

WITH cte AS (  
  SELECT
    ...
)

SELECT
  ...
FROM cte;

Un CTE en SQL Server se inicia utilizando la palabra clave WITH. Luego le seguimos con el nombre del CTE (aquí, cte), la palabra clave AS, y los paréntesis. Los paréntesis contienen la definición de la CTE. En términos simples, es una sentencia SELECT normal, sólo que en forma de CTE.

La parte siguiente viene después de los paréntesis y se denomina consulta principal. El CTE no funciona sin ella. Esta consulta principal es, en el ejemplo genérico, una sentencia SELECT que hace referencia al CTE en la cláusula FROM. Como ya hemos mencionado, la consulta principal podría ser una sentencia INSERT, UPDATE, o DELETE en lugar de SELECT.

Otra forma de escribir una CTE en SQL Server es especificando explícitamente las columnas, que se parece a esto:

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

SELECT
  ...
FROM cte;

La única diferencia es que se definen explícitamente las columnas CTE antes de la palabra clave AS. Esto es útil cuando las columnas CTE requieren alias (por ejemplo, cuando contienen funciones); la consulta es más legible con los alias asignados de la forma anterior.

Como verás con los ejemplos de este artículo, el principal argumento para usar CTE en SQL Server es la mejora de la legibilidad del código. También puedes echar un vistazo a algunas de sus otras ventajas.

Ahora que conoce la sintaxis básica de CTE, utilicemos cada enfoque en un ejemplo. A medida que avancemos, mostraremos ligeros cambios en la sintaxis dependiendo del uso de la consulta.

Ejemplos de CTE en SQL Server

Antes de escribir ningún código, familiaricémonos con el conjunto de datos. La tabla es flight_databaseque contiene datos históricos de vuelos. Tiene las siguientes columnas:

• id - El ID del registro y la clave primaria (PK) de la tabla.
• flight_id - El número de vuelo según las normas IATA.
• airline - El nombre de la compañía aérea.
• flight_date - La fecha del vuelo.
• departure_airport - El aeropuerto de salida del vuelo.
• arrival_airport - El aeropuerto donde aterrizó el vuelo.
• planned_departure - La hora a la que estaba prevista la salida del vuelo.
• actual_departure - Hora de salida real del vuelo.
• planned_arrival - Hora prevista de llegada del vuelo.
• actual_arrival - La hora de llegada real del vuelo.
• airport_distance - La distancia entre los aeropuertos de salida y llegada, en kilómetros.

Estos son datos ficticios para el aeropuerto de Amsterdam Schiphol. Todas las horas están en GMT+1, lo que nos facilita la comparación de las horas de salida y llegada.

He aquí algunas filas de esta tabla:

La columna id es única, ya que es la clave primaria de la tabla. La columna flight_id no es única, ya que hay datos para los mismos vuelos en fechas diferentes.

Puedes crear este conjunto de datos utilizando el código del enlace. Si necesitas instalar SQL Server, aquí tienes las instrucciones para hacerlo.

Ejemplo 1: CTE estándar en SQL Server

La tarea aquí es escribir un CTE y encontrar los retrasos más largos de salida y llegada por número de vuelo IATA.

Esta es la 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;

El CTE comienza escribiendo la palabra clave WITH. El nombre del CTE es delay_times. Tras la palabra clave AS y el paréntesis de apertura, hay una definición de CTE en forma de sentencia SELECT. Calcula la diferencia entre la salida prevista y la salida real utilizando la función DATEDIFF(). Se aplica el mismo método para calcular la diferencia entre la llegada prevista y la real. Ambos resultados se expresan en minutos. Como estas columnas utilizan funciones, cada una tiene un alias.

Tras cerrar el paréntesis, es hora de escribir la consulta principal. Se trata de una sentencia SELECT que hace referencia al CTE delay_times y utiliza dos veces las funciones agregadas MAX() de SQL Server para calcular el mayor retraso de salida y llegada por vuelo.

La salida se lee de la siguiente manera El retraso máximo de salida del vuelo DL 47 fue de 5 minutos. Su retraso máximo de llegada fue 0; siempre llegó a tiempo.

Ejemplo 2: CTE con columnas definidas explícitamente

El siguiente ejemplo es muy similar. La única diferencia es que queremos encontrar los menores retrasos de salida y llegada por vuelo. Además, usaremos columnas definidas explícitamente. Hágalo así

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 se llama nuevamente delay_times. Para definir las columnas del CTE explícitamente, escríbalas entre paréntesis antes de la palabra clave AS.

El CTE en sí no difiere mucho del anterior: vuelve a utilizar la función DATEDIFF() para calcular las diferencias de retardo. El único cambio es que los alias para estas dos (y las otras dos) columnas se definen antes, con el nombre del CTE.

La consulta principal es prácticamente la misma que antes. La diferencia es que ahora utiliza la función MIN(), ya que el objetivo es calcular los retrasos más pequeños.

El resultado muestra que el menor retraso del vuelo DL 47 fue cero. En otras palabras, llegó a tiempo al menos una vez. El vuelo LH 2301 nunca fue puntual. Se retrasó al menos 20 minutos tanto a la salida como a la llegada.

Ejemplo 3: Un CTE anidado en SQL Server

En SQL Server, un CTE anidado se produce cuando hay al menos dos CTE y el segundo CTE hace referencia al primero. Lo necesitaremos en el siguiente ejemplo. La tarea consiste en calcular la duración media del vuelo en minutos y la velocidad media del vuelo en km/h.

Este es el 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;

El primer CTE se escribe como es habitual en SQL Server: WITH La primera CTE se escribe como de costumbre en SQL Server: AS, el nombre de la CTE, , y la sentencia SELECT. Esta consulta calcula la duración del vuelo en minutos. Después de cerrar los paréntesis, se escribe el segundo CTE. Pero tenga cuidado; tiene que haber una coma separando los dos CTEs. Además, cuando empiezas a escribir el segundo CTE, no hay palabra clave WITH: empiezas directamente con el nombre del CTE. Todo lo demás es como siempre. Este segundo CTE hace referencia al primer CTE en la cláusula FROM para calcular la duración media de los vuelos para todas las fechas.

No hay diferencias en la sintaxis de la consulta principal. Esta consulta hace referencia al segundo CTE. Calcula la velocidad media de los vuelos dividiendo la distancia entre los aeropuertos por la duración media de los vuelos. El resultado se convierte en un número decimal. También se divide por 60, por lo que la velocidad media se mostrará en kilómetros por hora.

El resultado muestra que, por ejemplo, la duración media del vuelo LH 987 para llegar al destino es de 54 minutos, con una velocidad media de 407,14 km/h.

Si quiere seguir practicando, aquí tiene otro ejemplo de CTE anidado.

Ejemplo 4: Una CTE en una sentencia UPDATE

En este ejemplo, le mostraremos cómo funcionan las CTEs en la sentencia UPDATE. De la forma en que se muestra a continuación, también podría utilizar la sentencia INSERT.

La tarea consiste en actualizar la sentencia flight_database. Para ser más precisos, actualizaremos su columna airport_distance. Actualmente contiene datos en kilómetros, pero deben cambiarse a millas.

A continuación se explica cómo hacerlo:

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 siempre, comience con la palabra clave WITH. El CTE distance_in_miles se utiliza para convertir kilómetros en millas. Es sencillo; multiplique los valores por 0,621371.

La consulta principal es ahora UPDATE en lugar de SELECT. Nada difícil; sólo siga la sintaxis de la sentencia. Actualice la columna airport_distance con los valores de la columna airport_distance_miles que aparece en el CTE. Une la tabla y el CTE, y ya está; la tabla está actualizada.

Aquí están varios valores antes de la actualización:

Y aquí están las mismas filas con los valores de distancia actualizados:

Ejemplo 5: Una CTE Recursiva en SQL Server

Nuestro ejemplo final es escribir una CTE recursiva en SQL Server. Este es un CTE que se referencia a si mismo. Se utiliza con más frecuencia cuando se consultan datos jerárquicos (como la organización de una empresa) o gráficos en los que algunas o todas las partes están relacionadas (piense en un mapa de carreteras con las distancias entre las ciudades). El ejemplo que le mostraremos es un poco más sencillo. Lo principal es que entiendas la recursividad y cómo traducirla a un CTE.

Digamos que el aeropuerto tiene un cierto número de franjas horarias de vuelo. También hay un precio por franja horaria. Cada año, el aeropuerto aumentará el número de franjas horarias en 150; el precio de las franjas horarias seguirá siendo el mismo. Queremos mostrar el número de franjas horarias, el precio por franja horaria y los ingresos totales por franjas horarias para el año en curso y los próximos cuatro años.

Esta es la CTE recursiva para hacerlo:

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;

Una vez más, la consulta recursiva comienza con la palabra clave WITH. El primer SELECT del CTE se denomina miembro ancla. Establecemos los valores iniciales del ID, el número de franjas horarias y el precio por franja horaria. Además, multiplicamos estos dos valores para obtener los ingresos por franja horaria.

A continuación viene UNION ALL, que conecta el miembro ancla con el segundo SELECT (que se denomina miembro recursivo). UNION ALL dicta que el número de columnas y sus tipos de datos tienen que ser los mismos en ambas sentencias SELECT del CTE.

El miembro recursivo hace referencia al propio CTE. Con cada recursión, el ID se incrementará en uno y el número de ranuras en 150. El precio por ranura sigue siendo el mismo. El precio por franja no varía. Los ingresos por franja horaria son el número de franjas horarias multiplicado por el precio por franja horaria.

También utilizamos la cláusula WHERE para detener la recursión una vez que el ID sea igual a cuatro. La recursión lo convertirá en cinco, y lo que queremos es una proyección a cinco años (actual + próximos cuatro años).

Después de eso, es lo mismo que con cualquier CTE. La consulta principal es SELECT.

Y esta es la salida:

Tenemos más ejemplos de CTE recursivas en nuestro blog. En este artículo nos hemos ceñido a la sintaxis de SQL Server, pero también puedes aprender cómo hacer recursividad en PostgreSQL y Oracle.

¡Las CTEs de SQL Server te hacen mejor!

Literalmente, lo hacen. La mayoría de las cosas que hacen los CTEs en SQL Server pueden hacerse con una subconsulta. Pero imagínese cómo se verían los códigos anteriores - ¡no es bonito! Uno de los usos típicos de las CTEs en SQL Server es ayudarle a organizar consultas largas. Las CTEs hacen las consultas más legibles nombrando partes de la consulta. De esta manera, usted puede fácilmente desglosar cada parte de un cálculo complejo y hacer el cálculo lógico.

Saber esto hará que tú y tu código T-SQL sean mejores. También hay una característica SQL que no es posible sin los CTEs: las consultas recursivas. Son indispensables cuando se consultan datos jerárquicos y gráficos. Puedes aprender todos los detalles en nuestro curso Consultas recursivas y expresiones de tabla comunes en MS SQL Server.

Y si tienes una entrevista para un puesto de SQL, asegúrate de repasar nuestros cinco ejemplos de CTE antes de la entrevista.