Una expresión común de tabla (CTE) permite crear un conjunto de resultados temporal dentro de una consulta.

Una CTE le ayuda a mejorar la legibilidad de una consulta compleja dividiéndola en partes más pequeñas y reutilizables.

Esta es la sintaxis básica para crear una expresión común de tabla:

WITH cte_name (column1, column2, ...) AS (
    -- CTE query
    SELECT ...
)
-- Main query using the CTE
SELECT ...
FROM cte_name;

En esta sintaxis:

cláusula WITH: Introduce la expresión común de tabla (CTE). Va seguida del nombre de la ETC y de una lista de nombres de columnas entre paréntesis. La lista de columnas es opcional y sólo es necesaria si desea definir explícitamente las columnas para la CTE.
Nombre del CTE**: Especifique el nombre del CTE. El nombre del CTE existe dentro del ámbito de la consulta. Asegúrese de que el nombre del CTE es único dentro de la consulta.
Lista de columnas (opcional)**: Especifique la lista de nombres de columnas dentro de los paréntesis después del nombre del CTE. Si no se especifica, las columnas heredan implícitamente los nombres de columna de la sentencia SELECT dentro de la CTE.
Palabra clave AS: La palabra clave AS indica el comienzo de la definición del CTE.
Consulta CTE**: Se trata de una consulta que define la CTE y que puede incluir cláusulas JOIN, WHERE GROUP BY y otras construcciones SQL válidas.
Consulta principal**: Una vez definido el CTE, se puede hacer referencia a él en la consulta principal por su nombre. En la consulta principal, puede usar la CTE como si fuera una tabla normal, simplificando la estructura de consultas complejas.

Ejemplos de CTE PostgreSQL¶

Exploremos algunos ejemplos de uso de expresiones comunes de tabla (CTE).

Ejemplo básico de expresión común de tabla PostgreSQL¶

El siguiente ejemplo utiliza una expresión común de tabla (CTE) para seleccionar el título y la duración de las películas de la categoría 'Acción' y devuelve todas las columnas de la CTE:

WITH action_films AS (
  SELECT 
    f.title, 
    f.length 
  FROM 
    film f 
    INNER JOIN film_category fc USING (film_id) 
    INNER JOIN category c USING(category_id) 
  WHERE 
    c.name = 'Action'
) 
SELECT * FROM action_films;

Salida:

          title          | length
-------------------------+--------
 Amadeus Holy            |    113
 American Circus         |    129
 Antitrust Tomatoes      |    168
 Ark Ridgemont           |     68
...

En este ejemplo:

En primer lugar, la consulta CTE combina datos de tres tablas film, film_category y category mediante las cláusulas INNER JOIN.
A continuación, la consulta principal recupera los datos de la CTE action_films mediante una sencilla sentencia SELECT.

Ejemplo de unión de un CTE con una tabla¶

En este ejemplo utilizaremos las tablas rental y staff de la base de datos de ejemplo:

El siguiente ejemplo une un CTE con una tabla para encontrar el recuento de personal y de alquileres de cada una:

WITH cte_rental AS (
  SELECT 
    staff_id, 
    COUNT(rental_id) rental_count 
  FROM 
    rental 
  GROUP BY 
    staff_id
) 
SELECT 
  s.staff_id, 
  first_name, 
  last_name, 
  rental_count 
FROM 
  staff s 
  INNER JOIN cte_rental USING (staff_id);

En este ejemplo:

En primer lugar, el CTE devuelve un conjunto de resultados que incluye el id de personal y los recuentos de alquileres.
A continuación, la consulta principal une la tabla staff con la CTE utilizando la columna staff_id.

Salida:

 staff_id | first_name | last_name | rental_count
----------+------------+-----------+--------------
        1 | Mike       | Hillyer   |         8040
        2 | Jon        | Stephens  |         8004
(2 rows)

Ejemplo de CTEs múltiples¶

El siguiente ejemplo utiliza múltiples CTEs para calcular varias estadísticas relacionadas con películas y clientes:

WITH film_stats AS (
    -- CTE 1: Calculate film statistics
    SELECT
        AVG(rental_rate) AS avg_rental_rate,
        MAX(length) AS max_length,
        MIN(length) AS min_length
    FROM film
),
customer_stats AS (
    -- CTE 2: Calculate customer statistics
    SELECT
        COUNT(DISTINCT customer_id) AS total_customers,
        SUM(amount) AS total_payments
    FROM payment
)
-- Main query using the CTEs
SELECT
    ROUND((SELECT avg_rental_rate FROM film_stats), 2) AS avg_film_rental_rate,
    (SELECT max_length FROM film_stats) AS max_film_length,
    (SELECT min_length FROM film_stats) AS min_film_length,
    (SELECT total_customers FROM customer_stats) AS total_customers,
    (SELECT total_payments FROM customer_stats) AS total_payments;

Salida:

 avg_film_rental_rate | max_film_length | min_film_length | total_customers | total_payments
----------------------+-----------------+-----------------+-----------------+----------------
                 2.98 |             185 |              46 |             599 |       61312.04
(1 row)

En este ejemplo, creamos dos CTEs:
film_stats: Calcula estadísticas relacionadas con las películas, incluyendo la tarifa media de alquiler, la duración máxima y la duración mínima.
Customer_stats: Calcula estadísticas relacionadas con los clientes, incluido el número total de clientes distintos y los pagos totales realizados.

La consulta principal recupera valores específicos de cada CTE para crear un informe resumido.

# Ventajas de PostgreSQL CTE

Las siguientes son algunas ventajas del uso de expresiones comunes de tabla o CTEs:

Mejoran la legibilidad de consultas complejas. Las CTEs se utilizan para organizar consultas complejas de una manera más organizada y legible.
Posibilidad de crear consultas recursivas, que son consultas que se referencian a sí mismas. Las consultas recursivas resultan útiles cuando se desea consultar datos jerárquicos, como organigramas.
Utilización conjunta con funciones de ventana. Puede utilizar las CTE junto con las funciones de ventana para crear un conjunto de resultados inicial y utilizar otra sentencia select para seguir procesando este conjunto de resultados.

# Resumen

Utilice una expresión común de tabla (CTE) para crear un conjunto de resultados temporal dentro de una consulta.
Aproveche las CTEs para simplificar consultas complejas y hacerlas más legibles.