CTE en PostgreSQL: Qué es y cómo usarlo

Las CTEs, o Expresiones Comunes de Tabla, son una poderosa herramienta de PostgreSQL que a menudo es ignorada. Este artículo examina varias CTEs incluyendo CTEs anidadas y recursivas - y lo que puede hacer con ellas.

Si escribe consultas complejas en SQL, pronto descubrirá que su código se vuelve desordenado y difícil de leer. Las CTE, también conocidas como cláusulas WITH, son principalmente una forma de simplificar las consultas. Sin embargo, también permiten utilizar la recursividad. La recursión, entre otros usos, le permite navegar fácilmente por estructuras jerárquicas.

Las CTEs (o Expresiones de Tabla Comunes) de PostgreSQL son muy similares a las subconsultas; la diferencia es que las CTEs se nombran y definen en la parte superior de su código. Esto le permite dividir una consulta grande en pequeñas secciones.

En este artículo, trabajaré con varios ejemplos de CTEs PostgreSQL. Asumo que usted ya está familiarizado con la escritura de consultas en PostgreSQL. Si no es así, vale la pena descargar nuestraHoja de Consulta PostgreSQL .

Si piensa que las CTEs de PostgreSQL le ayudarán en su trabajo, tal vez quiera ver nuestro curso interactivo Common Table Expressions in PostgreSQL. Este curso está diseñado para aquellos que ya están familiarizados con SQL básico. Obtendrá mucha práctica usando CTEs PostgreSQL, gracias a más de cien ejercicios interactivos.

¡Adentrémonos en las expresiones comunes de tabla en Postgres!

Expresiones de Tabla Comunes en PostgreSQL

Sintaxis de CTE en PostgreSQL

Veamos ahora más de cerca la sintaxis CTE. En su forma más simple, se ve así:

WITH cte_name AS (query_1)
query_2;

• cte_name es el nombre que le asigna a la CTE. Puede referirse a este nombre en su consulta principal o en subconsultas, tal como lo haría con una tabla.
• query_1 es cualquier SELECT válido
• query_2 es una sentencia SQL válida. Puede ser una SELECT, una UPDATE, una INSERT o una DELETE.

Los resultados de query_1 estarán disponibles como si fueran una tabla. El nombre de la tabla será el que haya especificado en cte_name. Puede utilizarla en el resto de su consulta de la misma forma que utiliza otras tablas.

Ejemplo de CTE en PostgreSQL

Veamos un ejemplo. Alpha Sales es un minorista en línea. Quieren saber si su última estrategia de marketing ha sido efectiva, y qué tipo de cliente ha respondido mejor a ella.

He aquí un ejemplo de su tabla order_summary que contiene el valor de cada pedido realizado en abril, mayo y junio de 2024.

Como primer paso para analizar el éxito de su campaña de marketing, los responsables de la empresa quieren comparar las ventas de junio por cliente con la media mensual de ventas por cliente de abril y mayo y calcular la variación porcentual.

Por supuesto, podría conseguirlo utilizando subconsultas, pero el código sería bastante complejo. Quiere ver la media del mes anterior en el informe, pero también utilizarla en el cálculo del porcentaje de cambio.

Utilizando un CTE, la consulta tendría este aspecto:

WITH april_may_sales AS
(SELECT 
   customer_id, 
   SUM(value) / 2 AS prev_avg
 FROM order_summary 
 WHERE EXTRACT (MONTH FROM order_date) in (4,5)
 GROUP BY customer_id;
)
SELECT 
  order_summary.customer_id, 
  prev_avg, 
  SUM(value) AS jun_total, 
  (SUM(value) - prev_avg) * 100 / prev_avg AS percent_change
FROM order_summary  
JOIN april_may_sales
ONapril_may_sales.customer_id = order_summary.customer_id
WHERE EXTRACT (MONTH FROM order_date) = 6
GROUP BY order_summary.customer_id, prev_avg
ORDER BY customer_id;

Esta consulta utiliza la cláusula WITH para crear una tabla virtual llamada april_may_sales. Extrae las ventas totales por cliente para los meses de abril y mayo, divide el resultado por 2 para obtener un promedio mensual y almacena esa información en una columna llamada prev_avg.

Esta tabla se une a la tabla order_summary de la consulta principal para que podamos ver el total de junio junto con la media de abril y mayo.

La consulta produce el siguiente conjunto de resultados:

Definición de nombres de columnas CTE

Opcionalmente, puede definir específicamente los nombres de columna para la tabla CTE utilizando la siguiente sintaxis:

WITH cte_name (column_name_list)
AS (query_1)
query_2;

Aquí, column_name_list es una lista de nombres de columnas separados por comas.

Si cambiamos el ejemplo anterior para utilizar esta sintaxis, obtendremos la siguiente consulta:

WITH april_may_sales (customer_id, prev_avg)
AS (
  SELECT 
 	customer_id, 
      SUM(value) /2 
  FROM order_summary 
  WHERE EXTRACT (MONTH FROM order_date) in (4,5)
  GROUP BY customer_id
)
SELECT 
order_summary.customer_id,
prev_avg, 
SUM(value) AS jun_total, 
(SUM(value) - prev_avg) * 100/prev_avg AS percent_change
FROM order_summary  
JOIN april_may_sales
ON april_may_sales.customer_id = order_summary.customer_id
WHERE EXTRACT (MONTH FROM order_date) = 6
GROUP BY order_summary.customer_id, prev_avg
ORDER BY customer_id;

Esto no supone ninguna diferencia en la salida de la consulta, que sigue siendo la misma que la consulta de ejemplo anterior. Sin embargo, hace que sea más fácil para otra persona entender su consulta.

Consultas anidadas usando CTE PostgreSQL

Puede definir dos o más CTEs usando una palabra clave WITH en PostgreSQL. Simplemente comience usando la palabra clave WITH y luego especifique cada CTE separado por comas. La sintaxis es la siguiente:

WITH 
cte_name_1 AS (query_1),
cte_name_2 AS (query_2)
query_3;

Cada CTE tiene su propio nombre y sentencia select. Cada CTE puede referirse a cualquier CTE previamente definido para recoger cualquier dato que necesite. Observe que no repite la palabra clave WITH: sólo enumera los CTE separados por comas.

Veámoslo en acción. Supongamos que Alpha Sales quiere llevar este análisis un paso más allá. Les gustaría extraer los datos demográficos de los clientes que compraron más en junio que la media de sus compras en abril y mayo.

Para ello, necesita combinar los datos extraídos en la consulta anterior con los datos de su tabla customer tabla. Aquí tiene una muestra de los datos:

Para ello, puede

• Mover la consulta principal anterior al frente como un CTE anidado. Esto crea una tabla virtual que contiene customer_id, la media anterior, el total de junio y el porcentaje de cambio.
• Escriba una nueva consulta principal que una esta tabla con la tabla customer para calcular la edad del cliente y extraer su estado.

La nueva consulta tiene el siguiente aspecto:

WITH april_may_sales AS
  (SELECT 
     customer_id, 
     SUM(value) / 2 AS prev_avg
   FROM order_summary 
   WHERE EXTRACT (MONTH FROM order_date) in (4,5)
   GROUP BY customer_id
),
comparison AS
  (
    SELECT 
      order_summary.customer_id, 
      prev_avg, 
      SUM(value) AS jun_total, 
      (SUM(value) - prev_avg) * 100/prev_avg AS percent_change
    FROM order_summary  
    JOIN april_may_sales
    ON april_may_sales.customer_id = order_summary.customer_id
    WHERE EXTRACT (MONTH FROM order_date) = 6
    GROUP BY order_summary.customer_id, prev_avg
  )
SELECT 
  customer.customer_id,
  name,
  EXTRACT(YEAR from CURRENT_DATE) - 
		EXTRACT(YEAR from date_of_birth) AS age,
  state,
  prev_avg, 
  jun_total,
  percent_change
FROM customer
JOIN comparison 
    ON comparison.customer_id = customer.customer_id
WHERE percent_change > 0;

Como antes, la consulta define el CTE llamado april_may_sales como una tabla virtual que contiene las ventas medias de abril y mayo.

A continuación, define un nuevo CTE denominado comparación, que contiene una comparación de los totales de junio por cliente con el contenido de april_may_sales.

Por último, la consulta principal combina los datos de la tabla virtual comparison con los datos de la tabla customer con los datos de la tabla

El conjunto de resultados tiene este aspecto:

CTE PostgreSQL en Lenguaje de Manipulación de Datos

Veamos ahora las sentencias de manipulación de datos como INSERT, UPDATE, y DELETE.

Una de las limitaciones de las CTEs es que no puede usarlas directamente en lugar de un valor en una sentencia UPDATE de la misma forma que puede hacerlo con una subconsulta.

Supongamos que desea actualizar el saldo de la tabla customer sumando el valor de todos los pedidos de junio. Con subconsultas ordinarias, puede hacer algo como esto:

UPDATE customer 
SET balance = balance + 
(select SUM(value) FROM order_summary 
WHERE customer.customer_id = order_summary.customer_id
   AND EXTRACT (MONTH from order_date) = 6);

No puede hacerlo con una CTE. Lo que puede hacer, sin embargo, es utilizar la siguiente sintaxis:

WITH cte_name AS (select_statement)
UPDATE tablename 
SET column_name_1 = column_name_2
FROM cte_name 
WHERE join_clause;

• cte_name es el nombre que usará para referirse a la 'tabla' creada por la CTE.
• select_statement es la sentencia que utilizará para rellenar la CTE.
• column_name_1 es el nombre de la columna de la tabla principal que desea actualizar.
• column_name_2 es el nombre de la columna en su CTE que utilizará para establecer el nuevo valor.
• join_clause especifica la condición que utilizará para unir las dos tablas.

La siguiente consulta suma el total de pedidos de junio de la tabla order_summary al saldo de la tabla customer de la tabla:

WITH june_total AS
(SELECT 
   customer_id, 
   SUM(value) AS jun_tot
 FROM order_summary WHERE EXTRACT(MONTH FROM order_date) = 6
 GROUP BY customer_id
)
UPDATE customer
SET balance = balance + jun_tot
FROM june_total 
WHERE customer.customer_id = june_total.customer_id;

Primero, la cláusula WITH crea una pseudo-tabla llamada june_total. Contiene los totales por customer_id de los pedidos en los que el mes de order_date es 6.

A continuación, la columna jun_tot de esta tabla se utiliza para aumentar el saldo donde customer_id coincide entre las dos tablas.

La tabla customer contiene ahora los siguientes datos:

También puede utilizar CTEs para insertar o eliminar filas de la misma manera.

Consultas recursivas y expresiones de tabla comunes

Las consultas recursivas son una característica de las CTEs. Estas consultas permiten realizar un bucle a partir de una única consulta base para llevar a cabo repetidamente una tarea específica. Son especialmente útiles para consultar datos jerárquicos, como estructuras organizativas y listas de materiales.

Este artículo no trata de las consultas recursivas en su totalidad. Nos limitaremos a ver la sintaxis y un ejemplo sencillo. Para más detalles, eche un vistazo a Qué es una CTE recursiva en SQL, que ofrece una explicación completa y varios ejemplos.

La sintaxis de las consultas recursivas en PostgreSQL es:

WITH RECURSIVE cte_name AS 
(query_1 UNION query_2)
query_3;

• La palabra clave RECURSIVE indica que esta es una consulta recursiva.
• query_1 es la consulta base, o punto de partida. Por ejemplo, suponga que está trabajando con una estructura organizativa. En ese caso, query_1 podría ser una consulta que seleccionara el gerente de más alto nivel de un archivo de empleados.
• query_2 es la consulta recursiva. Esta consulta se repetirá hasta que no haya más filas que cumplan los criterios especificados en WHERE. Puede hacer referencia a la última fila añadida al conjunto de resultados para recoger datos. Esto podría utilizarse para encontrar a todos los empleados que dependen de un directivo.
• UNION combina los resultados. Si utiliza UNION ALL, se conservarán los duplicados; de lo contrario, se omitirán.
• query_3 se utiliza para devolver el conjunto de resultados final. Puede hacer referencia a la tabla virtual creada por la CTE.

Pensemos en un ejemplo de tabla employee en la que los registros de los empleados tienen un campo que identifica al responsable del que dependen. ¿Qué ocurre realmente si se utiliza una consulta recursiva para navegar por esta jerarquía?

Los resultados de la consulta base se añaden a la tabla virtual. La consulta base extrae el registro de empleado del director general. A continuación, el motor de la base de datos utiliza esta fila para buscar todas las filas que coincidan con los criterios de la parte recursiva de la consulta. Se trata de todos los empleados que dependen directamente del director general.

Para cada uno de estos registros, el motor encontrará a todos los empleados que dependen de esa persona. Esto se repite hasta que no haya más empleados que cumplan la condición.

Veamos un ejemplo sencillo. Una empresa de consultoría informática tiene varios proyectos en curso, y su política es programar reuniones semanales de progreso para cada proyecto. Una tabla denominada projects contiene los detalles de los nuevos proyectos. Un ejemplo de esta tabla es el siguiente

La empresa desea crear detalles de las reuniones programadas en una tabla denominada meetingsEsta información se utilizará para enviar recordatorios y reservar un lugar cada semana. La columna meet_day contiene el día de la semana en que se programarán las reuniones. Se almacena como un número de día dentro de la semana, donde 0 representa el domingo.

Podrías conseguirlo con la siguiente consulta recursiva:

WITH RECURSIVE date_list
   (proj_name, meet_date, end_date, meet_day, meet_time)
AS (
    SELECT proj_name, start_date, end_date, meet_day, meet_time
	FROM projects
    UNION ALL
    SELECT 
proj_name, 
meet_date + 1,
	end_date, 
meet_day, 
meet_time
    FROM date_list
    WHERE meet_date + 1 <= end_date
	
)
INSERT INTO meetings
SELECT proj_name, meet_date, meet_time
FROM date_list 
WHERE meet_day = EXTRACT (DOW from meet_date)
ORDER BY proj_name, meet_date;

Una vez ejecutada la consulta, la tabla meetings contiene los siguientes datos:

Desglosemos la consulta y veamos lo que realmente hace.

En primer lugar, define las columnas que se incluirán en el CTE date_list:

WITH RECURSIVE date_list
   (proj_name, meet_date, end_date, meet_day, meet_time)

A continuación, establece los datos de base para la recursión, que son los contenidos de la tabla de proyectos:

AS (
    SELECT proj_name, start_date, end_date, meet_day, meet_time
	from projects

A continuación, especifica qué datos deben incluirse en cada recursión, con una condición que garantiza que la recursión finaliza cuando se completa:

    UNION ALL
    SELECT proj_name, 
	meet_date + 1,
	end_date, meet_day, meet_time
	FROM date_list
    WHERE meet_date + 1 <= end_date

Por último, la consulta principal inserta los resultados contenidos en la tabla virtual en la tabla meetings.

¿Te parece útil? Puede aprender más sobre consultas recursivas y practicar algunos ejemplos del mundo real si toma nuestro curso en línea CTE en PostgreSQL.

Aprenda más sobre CTEs en PostgreSQL

Aunque las CTEs en PostgreSQL pueden no mejorar el desempeño de sus consultas, ciertamente hacen que las consultas complejas sean más fáciles de escribir y de entender. Al dividir una consulta larga en partes componentes, usted puede organizar sus pensamientos y mantener su codificación simple. Los CTEs también facilitan el trabajo con estructuras jerárquicas usando la cláusula RECURSIVE.

Este artículo utiliza específicamente la sintaxis y ejemplos de PostgreSQL, pero las CTEs funcionan de manera muy similar para otros dialectos SQL como MS SQL Server.

Si quieres sentirte cómodo usando CTEs, el curso Common Table Expressions in PostgreSQL de LearnSQL tiene más de 100 ejercicios prácticos que te ayudarán a entender realmente esta herramienta.

Si desea practicar un poco más, pruebe estos 11 ejercicios gratuitos de expresiones comunes de tablas SQL. Cada ejercicio te ofrece el tipo de reto al que te enfrentarías en el mundo real, y se incluyen soluciones y explicaciones. Y si te estás preparando para una entrevista, aquí tienes algunos ejemplos de preguntas y respuestas de CTE para entrevistas.

Espero que este artículo le haya dado una buena idea de lo que PostgreSQL CTE puede hacer por usted. Si desea aprender algunos otros conceptos avanzados de PostgreSQL, este artículo es un buen lugar para comenzar.

¡Ahora depende de usted! Recuerde, la práctica hace al maestro, así que revise nuestra pista de aprendizaje SQL avanzado Practice para más práctica de características avanzadas de SQL.