5 Ejemplos Prácticos de la Cláusula WITH de SQL

Estos cinco casos de uso práctico te harán querer usar la cláusula SQL WITH diariamente.

La cláusula WITH puede ayudarle a escribir consultas SQL legibles y a dividir cálculos complejos en pasos lógicos. Se añadió a SQL para simplificar las consultas largas y complicadas. En este artículo, te mostraremos 5 ejemplos prácticos de la cláusula WITH y te explicaremos cómo su uso hace que las consultas SQL sean más legibles.

La cláusula WITH también se denomina Expresión Común de Tabla (CTE). Aprender a trabajar con ella no es un conocimiento básico de SQL, por lo que recomendamos practicar CON a través de un curso interactivo estructurado, como nuestro Consultas recursivas y expresiones de tabla comunes curso. En más de 100 ejercicios, aprenderá la sintaxis básica de las CTEs, así como los conceptos avanzados de las CTEs anidadas y recursivas.

¿Qué es la Cláusula WITH en SQL?

El otro nombre de la cláusula WITH, Expresión Común de Tabla, da una pista de lo que hace. Una cláusula WITH le permite crear una sentencia SELECT que devuelve un resultado temporal; puede nombrar este resultado y referenciarlo en otra consulta. Básicamente, es una subconsulta con nombre, pero puede ser recursiva. A continuación se muestra la diferencia entre una cláusula WITH y una subconsulta.

La CTE no puede ejecutarse sin la consulta principal, por lo que debe ir seguida de una consulta normal. Esta consulta suele ser también una sentencia SELECT, pero puede ser INSERT, UPDATE, o DELETE.

Piense en una CTE como una tabla temporal con una consulta que siempre debe ejecutarse para que la tabla pueda utilizarse. Dado que es un tipo de tabla, puede hacer referencia a la CTE en la cláusula FROM como si fuera una tabla normal.

Sintaxis básica de la cláusula WITH

Lo que explicamos anteriormente puede ser mostrado como un código SQL:

WITH cte AS (
  SELECT …
  FROM table
)

SELECT …
FROM cte;

Veamos las partes clave de la sintaxis. La CTE se inicia siempre, sin excepción, con la cláusula WITH. A continuación viene el nombre del CTE, que es cte en el ejemplo genérico anterior. Después del nombre del CTE viene la palabra clave AS. Lo que sigue entre paréntesis es la definición de CTE. En un ejemplo muy sencillo, se trata simplemente de una sentencia SELECT. Por último, está la consulta principal (como en las subconsultas normales) que hace referencia al CTE.

Esto es sintaxis básica. La revisaremos en los ejemplos. En el camino, también verá cómo esta sintaxis básica puede extenderse escribiendo CTEs múltiples o anidados.

Ejemplos 1 y 2: Información Salarial

Ejemplo de Datos

Utilizaremos la tabla employees en los dos primeros ejemplos. Tiene las siguientes columnas:

• id - ID del empleado.
• first_name - Nombre del empleado.
• last_name - Apellidos del empleado.
• salary - Sueldo del empleado.
• department - Departamento del empleado.

Estos son los datos:

Y aquí tienes un enlace a una consulta que puedes ejecutar para crear la tabla:

Ejemplo 1: Mostrar cómo se compara el salario de cada empleado con la media de la empresa

Para resolver este problema, necesita mostrar todos los datos de la tabla employees. Además, necesita mostrar el salario promedio de la compañía y luego la diferencia con el salario de cada empleado.

Esta es la solución:

WITH avg_total_salary AS (
	SELECT AVG(salary) AS average_company_salary
	FROM employees
)
	
SELECT id,
	 first_name,
	 last_name,
	 salary,
	 department,
	 average_company_salary,
	 salary - average_company_salary  AS salary_difference
FROM employees, avg_total_salary;

Primero, inicie el CTE utilizando la cláusula WITH. El nombre del CTE ('avg_total_salary') viene después. Abra los paréntesis después de AS, y escriba la sentencia normal SELECT. Calcula el salario medio de la empresa.

Para utilizar la salida del CTE, escriba otra sentencia SELECT como consulta principal. Observe que la sentencia CTE y la consulta principal sólo están separadas por paréntesis. La nueva fila sólo está ahí para facilitar la lectura. La consulta principal selecciona todas las columnas de la tabla y del CTE. El CTE y la tabla employees Además, existe la columna calculada salary_difference.

La consulta devuelve esto:

Podemos ver todos los datos de los empleados y cuánto está su salario por encima o por debajo de la media de la empresa. Por ejemplo, el salario de Melissa Allman es 293,48 superior a la media. El salario de Nina Longhetti es 993,20 inferior a la media.

Ejemplo 2: Mostrar los datos de los empleados junto con el salario medio del departamento y de la empresa

Vayamos un poco más lejos del ejemplo anterior. Esta vez, no tiene que calcular la diferencia entre los salarios. Pero necesita mostrar todos los datos analíticos de los empleados y después mostrar el salario medio del departamento y de la empresa.

Puede hacerlo escribiendo dos CTEs en lugar de uno:

WITH avg_total_salary AS (
	SELECT AVG(salary) AS average_company_salary
	FROM employees
),
	
avg_dpt_salary AS (
	SELECT department,
		 AVG(salary) AS average_department_salary
	FROM employees
	GROUP BY department
)
	
SELECT e.id,
	   e.first_name,
	   e.last_name,
	   e.salary,
	   average_department_salary,
	   average_company_salary
FROM employees e JOIN avg_dpt_salary ads ON e.department = ads.department, avg_total_salary;

Recuerde que mencionamos que la sintaxis básica podía ser extendida. He aquí un ejemplo de cómo hacerlo. Sí, puedes escribir múltiples CTEs, uno tras otro, y luego referenciarlos en la consulta principal.

Veamos cómo hacerlo. Todo es habitual con el primer CTE: cláusula WITH, el nombre del CTE, AS, y la definición del CTE entre paréntesis. Este CTE calcula el salario medio de la empresa.

El segundo CTE calcula el salario medio por departamento. Pero aquí hay algo diferente. Fíjese en dos cosas importantes: no hay ninguna otra cláusula WITH y las CTE están separadas por una coma.

Así es como se escriben múltiples CTEs: la cláusula WITH se escribe sólo antes de la primera consulta (¡no debe aparecer antes de ningún otro CTE!), y una coma debe separar los CTEs.

Independientemente del número de CTEs, siempre deben ir seguidas de la consulta principal. Y no hay coma entre el último CTE y la consulta principal.

La consulta principal une la tabla con los dos CTEs y selecciona los datos relevantes. La unión se realiza del mismo modo que con las tablas normales: especifique el tipo de unión y la columna en la que se unirán las tablas.

Este es el resultado:

Ahora tiene todos los datos en un solo lugar: salarios individuales, promedio del departamento y promedio de la empresa. A partir de aquí, puede continuar con el análisis de los datos.

Ejemplos 3 y 4: Ingresos

Datos de ejemplo

En los dos ejemplos siguientes, utilizaremos la tabla revenue. La creamos con la consulta CREATE TABLE que se encuentra aquí. Tiene las siguientes columnas:

• id - El ID del registro de ingresos.
• year - El año de los ingresos.
• quarter - El trimestre del ingreso.
• revenue_amount - El importe del ingreso.

Familiarícese con los datos que se muestran a continuación:

Ejemplo 3: Mostrar cada año con los correspondientes ingresos anuales e ingresos totales

Utilice los datos anteriores para mostrar todos los años disponibles. Junto con cada año, muestre los ingresos anuales de ese año y los ingresos totales de la empresa en todos los años.

Esta tarea es similar a la del Ejemplo 1, pero utilizaremos una función de agregado diferente:

WITH total_revenue AS (
	SELECT SUM(revenue_amount) AS total_company_revenue
	FROM revenue
)
	
SELECT year,
	 SUM (revenue_amount) AS annual_revenue,
	 total_company_revenue
FROM revenue, total_revenue
GROUP BY year, total_company_revenue
ORDER BY year;

El CTE utiliza la función agregada SUM() para calcular los ingresos totales de la empresa.

La consulta principal une el CTE con la tabla revenue. Utilizamos esta sentencia SELECT para mostrar el año, calcular los ingresos anuales de cada año y mostrar los ingresos totales.

Este es el resultado de la consulta:

El resultado muestra que los ingresos anuales en 2019 fueron de 10.489.632,27 euros. En 2020, fueron de 20.919.087,62, y así sucesivamente. Si se suman los cuatro años, los ingresos totales son 80.812.015,89.

Ejemplo 4: Mostrar cada trimestre junto con los ingresos trimestrales más altos y más bajos de ese año y del total

Debe enumerar todos los años y trimestres con los ingresos correspondientes. Hasta aquí, todo correcto. A continuación, debe mostrar los ingresos trimestrales más bajos de ese año y los ingresos trimestrales más bajos de todos los años. A continuación, haga lo mismo con los ingresos más elevados.

Esta es la solución:

WITH yearly_min_max_quarter AS (
	SELECT year,
		   MIN(revenue_amount) AS minimum_quarterly_revenue_annual,
		   MAX(revenue_amount) AS maximum_quarterly_revenue_annual
FROM revenue
GROUP BY year),

min_max_overall AS (
	SELECT MIN(revenue_amount) AS overall_min_revenue,
	       MAX(revenue_amount) AS overall_max_revenue
FROM revenue)

SELECT r.year,
	   quarter,
	   revenue_amount,
	   minimum_quarterly_revenue_annual,
	   overall_min_revenue,
	   maximum_quarterly_revenue_annual, 
	   overall_max_revenue
FROM revenue r 
JOIN yearly_min_max_quarter ymmq 
ON r.year = ymmq.year, min_max_overall
ORDER BY year, quarter ASC;

Esta solución requiere de nuevo dos CTEs. Probablemente ya sepa cómo escribirlo, pero expliquemos cada paso.

El primer CTE encuentra los ingresos trimestrales más bajos y más altos de cada año. Para ello, utilice las funciones MIN() y MAX() y agrupe los datos por año.

A continuación, escriba el segundo CTE sin la cláusula WITH y sepárelo por una coma del primero. Este CTE devuelve los ingresos trimestrales más bajos y más altos de todos los años.

La consulta principal une la tabla revenue con la primera y luego con la segunda CTE. Mostrará los datos de la tabla y del CTE como una sola tabla.

Eche un vistazo al resultado:

Junto con los ingresos de cada trimestre, ahora tiene otra información importante. Sabes que los ingresos más bajos en 2019 fueron 984.157,15, y puedes ver que ocurrió en el tercer trimestre. Los ingresos más bajos en 2020 (2.497.441,68) se produjeron en los tres primeros meses del año. Puede analizar todos los demás años de forma similar observando la columna minimum_quarterly_revenue_annual.

El valor de la columna overall_min_revenue representa los ingresos más bajos de todos los tiempos. Es el mismo en todas las filas y corresponde al tercer trimestre de 2019. Las dos columnas siguientes son similares, pero muestran los ingresos más altos en lugar de los más bajos. Es decir, los ingresos más altos de 2019 fueron 5.417.884,15, que corresponden al cuarto trimestre. El mayor ingreso de todos los tiempos es 12.478.945,47, que se realizó en 2021T1.

Ejemplo 5: Horas trabajadas

Datos del ejemplo

La tabla del último ejemplo se denomina hoja_horas_empleado. En ella se registran las horas de trabajo de los empleados. Sus columnas se explican por sí mismas, así que nos limitaremos a ver los datos:

Ejemplo 5: Mostrar el promedio más bajo y más alto de horas de trabajo

Este ejemplo quiere que primero encuentre el promedio de horas de trabajo por empleado y luego sólo muestre el promedio más bajo y el más alto.

Aquí está el código para resolver este problema:

WITH login_times AS (
SELECT 
id,
employee_id,
start_time,
end_time,
end_time - start_time AS working_hours
FROM employee_timesheet),

avg_login AS (
	SELECT 
employee_id,
AVG(working_hours) AS average_working_hours
FROM login_times
GROUP BY employee_id)

SELECT MIN(average_working_hours) AS min_average_working_hours,
	 MAX(average_working_hours) AS max_average_working_hours
FROM avg_login;

Al principio, esto podría parecer cualquier consulta con dos CTEs. Mírela más de cerca y verá que no es así. Sí, hay dos CTEs. Pero la diferencia es que el segundo CTE hace referencia al primero, lo que no ocurría en los Ejemplos 2 y 4. Esto se denomina CTE anidado.

Esto se llama CTE anidado. El primer CTE se utiliza para obtener la diferencia entre el inicio y el fin de sesión; así se obtienen las horas de trabajo de cada sesión.

Un empleado tiene varias sesiones, por lo que necesitamos hallar la duración media de las sesiones, es decir, la media de horas trabajadas. Para ello se utiliza el segundo CTE. En cuanto a la sintaxis, nada nuevo, salvo que el CTE hace referencia al primer CTE en la cláusula FROM.

Luego, en la consulta principal, hacemos algo llamado agregación multinivel. Tomamos el promedio de horas de trabajo por empleado (agregación de primer nivel) y hallamos el mínimo y el máximo de estos valores (agregación de segundo nivel).

Este es el resultado:

El resultado nos dice que la media más baja de horas de trabajo por empleado es de 5 horas, 57 minutos y 4 segundos. La media más alta es de 13:24:29.

Ventajas de la cláusula WITH de SQL

Los cinco ejemplos que le hemos mostrado fueron cuidadosamente seleccionados para mostrar el uso típico y los beneficios de la cláusula WITH.

El primer beneficio se hace evidente cuando se tienen varios pasos de cálculo, como vimos en los ejemplos anteriores. Utilizando la cláusula WITH, puede organizar el código y dividirlo en partes lógicas.

Cuando los cálculos se vuelven más complicados, la longitud y la complejidad del código también aumentan. El uso de la cláusula WITH es ideal para mantener esto bajo control. Aunque los códigos de los ejemplos anteriores puedan parecer largos, se alargarían considerablemente (y serían menos legibles) si utilizáramos subconsultas en lugar de la cláusula WITH. Y como vio en el último ejemplo, el uso de la cláusula WITH le permite calcular fácilmente agregaciones multinivel.

Otra ventaja es que la cláusula WITH permite escribir consultas recursivas en SQL, lo que abre todo un nuevo mundo de posibilidades.

Aprender la cláusula WITH a veces puede resultar abrumador, por lo que hemos preparado una guía que te ayudará a estructurar tu enfoque. Feliz aprendizaje, y sabemos que la cláusula SQL WITH recompensará con creces tus esfuerzos.