Lección 6.4: Expresiones de Tabla Comunes (CTEs)

Las Expresiones de Tabla Comunes, o CTEs, son una de las características más poderosas y poco utilizadas en SQL. Permiten definir conjuntos de resultados temporales con nombre que pueden ser referenciados dentro de una consulta más grande. En esta lección, exploraremos cómo las CTEs pueden hacer que tu código SQL sea más legible, mantenible y más fácil de depurar.

¿Qué Son las CTEs?

Una Expresión de Tabla Común (CTE) es un conjunto de resultados temporales definido al comienzo de una consulta utilizando la cláusula WITH. Piensa en ello como una subconsulta nombrada que puede ser utilizada múltiples veces dentro de la misma consulta.

Las principales ventajas de las CTEs:

Legibilidad: Los conjuntos de resultados nombrados hacen que las consultas sean más fáciles de entender
Reutilización: Referencia la misma CTE múltiples veces sin redefinirla
Modularidad: Divide consultas complejas en piezas lógicas y manejables
Mantenibilidad: Los cambios en la lógica solo necesitan hacerse en un lugar
Depuración: Prueba cada CTE de manera independiente antes de combinarlas

Sintaxis Básica de CTE

La sintaxis general para una CTE es:

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

Componentes:

WITH: Palabra clave que introduce la CTE
cte_name: El nombre que le das al conjunto de resultados temporales
AS: Palabra clave que introduce la definición de la consulta
(SELECT ...): La consulta que define la CTE
La consulta principal puede luego referenciar la CTE por nombre

Tu Primera CTE

Comencemos con un ejemplo simple que calcula el gasto de los clientes:

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent,
        COUNT(*) AS payment_count,
        AVG(amount) AS avg_payment
    FROM
        payment
    GROUP BY
        customer_id
)
SELECT
    customer_id,
    total_spent,
    payment_count,
    avg_payment
FROM
    customer_spending
WHERE
    total_spent > 100
ORDER BY
    total_spent DESC;

Esta CTE:

Define un conjunto de resultados nombrado llamado customer_spending
Calcula métricas de gasto para cada cliente
Referencia esta CTE en la consulta principal para filtrar a los clientes de alto gasto

El beneficio aquí es la claridad: la intención es obvia: estamos trabajando con datos de gasto de clientes.

CTEs vs Subconsultas

Compararemos la misma lógica utilizando un enfoque de subconsulta tradicional:

Usando una Subconsulta:

SELECT
    customer_id,
    total_spent,
    payment_count,
    avg_payment
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spent,
        COUNT(*) AS payment_count,
        AVG(amount) AS avg_payment
    FROM
        payment
    GROUP BY
        customer_id
) AS spending_data
WHERE
    total_spent > 100
ORDER BY
    total_spent DESC;

Usando una CTE:

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent,
        COUNT(*) AS payment_count,
        AVG(amount) AS avg_payment
    FROM
        payment
    GROUP BY
        customer_id
)
SELECT
    customer_id,
    total_spent,
    payment_count,
    avg_payment
FROM
    customer_spending
WHERE
    total_spent > 100
ORDER BY
    total_spent DESC;

Diferencias Clave:

La CTE se define en la parte superior, lo que hace que la estructura de la consulta sea inmediatamente clara
La CTE tiene un nombre significativo (customer_spending), no solo una subconsulta anónima
La intención de la consulta principal es visible antes de profundizar en las transformaciones de datos
Si necesitas referenciar este conjunto de resultados múltiples veces, solo lo defines una vez con una CTE

Múltiples CTEs en Una Consulta

Puedes definir múltiples CTEs en una sola consulta, cada una referenciando las anteriores:

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent
    FROM
        payment
    GROUP BY
        customer_id
),
high_spenders AS (
    SELECT
        customer_id,
        total_spent
    FROM
        customer_spending
    WHERE
        total_spent > 150
),
customer_details AS (
    SELECT
        hs.customer_id,
        hs.total_spent,
        c.first_name,
        c.last_name,
        c.email
    FROM
        high_spenders hs
    JOIN
        customer c ON hs.customer_id = c.customer_id
)
SELECT
    customer_id,
    CONCAT(first_name, ' ', last_name) AS customer_name,
    email,
    total_spent
FROM
    customer_details
ORDER BY
    total_spent DESC;

En esta consulta:

customer_spending calcula el total gastado por cliente
high_spenders filtra a los clientes con un total gastado > 150
customer_details une a los grandes gastadores con la información del cliente
La consulta principal selecciona y formatea los resultados finales

Esta estructura hace que el flujo lógico sea claro y fácil de seguir.

Reutilización de CTE

Un aspecto poderoso de las CTEs es referenciarse a sí mismas múltiples veces:

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', payment_date) AS month,
        SUM(amount) AS monthly_total
    FROM
        payment
    GROUP BY
        DATE_TRUNC('month', payment_date)
)
SELECT
    m1.month AS current_month,
    m1.monthly_total AS current_sales,
    m2.monthly_total AS previous_month_sales,
    ROUND(((m1.monthly_total - m2.monthly_total) / m2.monthly_total * 100), 2) AS percent_change
FROM
    monthly_sales m1
LEFT JOIN
    monthly_sales m2 ON m1.month = m2.month + INTERVAL '1 month'
WHERE
    m1.month IS NOT NULL
ORDER BY
    m1.month;

Aquí, referenciamos monthly_sales dos veces—una como m1 y otra como m2. Esto requeriría dos subconsultas separadas si no estuviéramos usando una CTE.

CTE con Funciones de Ventana

Las CTEs funcionan maravillosamente con funciones de ventana:

WITH ranked_rentals AS (
    SELECT
        customer_id,
        rental_date,
        return_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY rental_date DESC
        ) AS rental_rank
    FROM
        rental
),
most_recent_rental AS (
    SELECT
        customer_id,
        rental_date,
        return_date
    FROM
        ranked_rentals
    WHERE
        rental_rank = 1
)
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    mrr.rental_date AS last_rental_date,
    DATEDIFF(CURDATE(), mrr.rental_date) AS days_since_rental
FROM
    customer c
LEFT JOIN
    most_recent_rental mrr ON c.customer_id = mrr.customer_id
ORDER BY
    days_since_rental DESC
LIMIT 20;

Esta consulta:

Usa ROW_NUMBER() para identificar el alquiler más reciente de cada cliente
Filtra para obtener solo el alquiler más reciente por cliente
Une con la tabla de clientes para mostrar los nombres de los clientes y calcular los días desde el alquiler

La estructura modular facilita la comprensión y modificación.

Ejemplo Práctico: Análisis de Cohortes

Las CTEs son excelentes para consultas analíticas complejas como el análisis de cohortes:

WITH customer_first_rental AS (
    SELECT
        customer_id,
        MIN(rental_date) AS first_rental_date,
        DATE_TRUNC('month', MIN(rental_date)) AS cohort_month
    FROM
        rental
    GROUP BY
        customer_id
),
customer_rental_history AS (
    SELECT
        cfr.customer_id,
        cfr.cohort_month,
        DATE_TRUNC('month', r.rental_date) AS rental_month,
        COUNT(*) AS rentals_in_month
    FROM
        customer_first_rental cfr
    JOIN
        rental r ON cfr.customer_id = r.customer_id
    GROUP BY
        cfr.customer_id,
        cfr.cohort_month,
        DATE_TRUNC('month', r.rental_date)
)
SELECT
    cohort_month,
    rental_month,
    COUNT(DISTINCT customer_id) AS customers,
    SUM(rentals_in_month) AS total_rentals
FROM
    customer_rental_history
GROUP BY
    cohort_month,
    rental_month
ORDER BY
    cohort_month,
    rental_month;

Este análisis complejo se vuelve manejable a través de las CTEs:

La primera CTE identifica la cohorte de cada cliente (mes del primer alquiler)
La segunda CTE construye un historial de todos los alquileres con información de la cohorte
La consulta final agrega para mostrar el rendimiento de la cohorte a lo largo del tiempo

Resumen de Beneficios

Aspecto	CTE	Subconsulta
Legibilidad	Muy legible con conjuntos de resultados nombrados	Puede volverse difícil de leer (estructuras anidadas)
Reutilización	Fácil de referenciar múltiples veces	Debe redefinirse para cada uso
Depuración	Se puede probar cada CTE de manera independiente	Difícil aislar lógica específica
Organización	Estructura lógica, de arriba hacia abajo	Lineal pero a veces desordenada
Rendimiento	Igual o mejor (dependiente del optimizador)	Puede ser menos eficiente con anidamientos profundos

Conclusiones Clave

CTEs son conjuntos de resultados temporales nombrados definidos con la cláusula WITH
Legibilidad: Las CTEs nombradas hacen que las consultas sean auto-documentadas
Múltiples CTEs: Encadena CTEs, cada una construyendo sobre la anterior
Reutilización: Referencia la misma CTE múltiples veces sin redefinirla
Sin Penalización de Rendimiento: Las CTEs no crean almacenamiento intermedio; son herramientas de optimización de consultas
Funciona con Todo: Las CTEs pueden incluir uniones, agregaciones, funciones de ventana y más
Modularidad: Divide consultas complejas en piezas lógicas que son más fáciles de entender y mantener

Las CTEs transforman consultas complejas de estructuras anidadas incomprensibles en código claro, legible y mantenible. Son una herramienta esencial en el kit de herramientas de cualquier analista de datos.

En la próxima lección, exploraremos las CTEs recursivas, una característica poderosa para trabajar con datos jerárquicos.

Práctica

Lecciones

Entorno de Pruebas

Prueba

Lección 6.4: Expresiones de Tabla Comunes (CTEs)

¿Qué Son las CTEs?

Sintaxis Básica de CTE

Tu Primera CTE

CTEs vs Subconsultas

Múltiples CTEs en Una Consulta

Reutilización de CTE

CTE con Funciones de Ventana

Ejemplo Práctico: Análisis de Cohortes

Resumen de Beneficios

Conclusiones Clave

Iniciar sesión

Registrarse

Lección 6.4: Expresiones de Tabla Comunes (CTEs)

¿Qué Son las CTEs?

Sintaxis Básica de CTE

Tu Primera CTE

CTEs vs Subconsultas

Múltiples CTEs en Una Consulta

Reutilización de CTE

CTE con Funciones de Ventana

Ejemplo Práctico: Análisis de Cohortes

Resumen de Beneficios

Conclusiones Clave