Ejemplos de consultas complejas con múltiples CTE

¡En esta lección vamos a hacer magia de verdad con las consultas! Vamos a crear varios ejemplos usando varios CTE para mostrar cómo se pueden integrar entre sí para construir consultas complejas y de varios pasos. Estos ejemplos te van a servir en la vida real, sobre todo en tareas analíticas complicadas.

Ejemplo 1: Análisis del rendimiento de los estudiantes

Imagina que tenemos una base de datos de una universidad con tres tablas:

Tabla students:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name TEXT NOT NULL
);

Tabla grades:

CREATE TABLE grades (
    grade_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT NOT NULL,
    grade NUMERIC(3, 1) NOT NULL
);

Tabla courses:

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT NOT NULL
);

Tarea: obtener una lista de estudiantes con una nota media superior a 85, mostrando su nota media y los nombres de los cursos que están cursando.

Consulta:

WITH high_achievers AS (
    -- Encontramos estudiantes con nota media alta
    SELECT 
        student_id, 
        AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 85
),
student_courses AS (
    -- Encontramos los cursos en los que está inscrito cada estudiante
    SELECT 
        s.student_id, 
        c.course_name
    FROM grades g
    JOIN courses c ON g.course_id = c.course_id
    JOIN students s ON g.student_id = s.student_id
)
-- Unimos los resultados
SELECT 
    s.student_name, 
    ha.avg_grade, 
    sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN students s ON s.student_id = ha.student_id;

Explicación:

1. En el primer CTE (high_achievers) calculamos la nota media de cada estudiante y elegimos a los que tienen notas superiores a 85.
2. En el segundo CTE (student_courses) relacionamos a los estudiantes con sus cursos.
3. En la consulta principal juntamos los datos de ambos CTE para obtener la lista de estudiantes, su nota media y los cursos que están cursando.

Ejemplo 2: Informe de ventas para una tienda online

Imagina que trabajamos con una tienda online y tenemos las siguientes tablas:

Tabla orders (pedidos):

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

Tabla customers (clientes):

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL
);

Tabla order_items (productos en pedidos):

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

Tarea: crear un informe que muestre para cada cliente:

• El número total de pedidos.
• La suma total de todos los pedidos del último mes.
• La lista de todos los productos únicos que ha comprado.

Consulta:

WITH recent_orders AS (
    -- Seleccionamos los pedidos del último mes
    SELECT 
        order_id, 
        customer_id, 
        total_amount 
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
),
customer_summary AS (
    -- Contamos el número total de pedidos y la suma para cada cliente
    SELECT 
        ro.customer_id,
        COUNT(ro.order_id) AS total_orders,
        SUM(ro.total_amount) AS total_spent
    FROM recent_orders ro
    GROUP BY ro.customer_id
),
customer_products AS (
    -- Seleccionamos los productos únicos que ha comprado cada cliente
    SELECT DISTINCT
        ro.customer_id,
        oi.product_id
    FROM recent_orders ro
    JOIN order_items oi ON ro.order_id = oi.order_id
)
-- Unimos los resultados
SELECT 
    c.customer_name,
    cs.total_orders,
    cs.total_spent,
    ARRAY_AGG(cp.product_id) AS purchased_products
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
JOIN customer_products cp ON cp.customer_id = c.customer_id
GROUP BY c.customer_name, cs.total_orders, cs.total_spent;

Explicación:

1. En el primer CTE (recent_orders) seleccionamos los pedidos del último mes.
2. En el segundo CTE (customer_summary) calculamos el número total de pedidos y la suma total para cada cliente.
3. En el tercer CTE (customer_products) obtenemos los productos únicos que ha comprado cada cliente.
4. En la consulta final juntamos los datos y usamos ARRAY_AGG() para crear la lista de productos únicos.

Ejemplo 3: Análisis de la jerarquía de empleados

Tenemos una tabla de empleados:

Tabla employees:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name TEXT NOT NULL,
    manager_id INT NULL
);

Tarea: construir la jerarquía de empleados empezando por el director general. Indicar el nivel de cada empleado en la jerarquía.

Consulta:

WITH RECURSIVE employee_hierarchy AS (
    -- Empezamos con los empleados que no tienen manager (director general)
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Añadimos todos los subordinados del nivel actual
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- Mostramos la jerarquía
SELECT 
    employee_id, 
    employee_name, 
    manager_id, 
    level
FROM employee_hierarchy
ORDER BY level, employee_id;

1. En la consulta recursiva empezamos con el director general (empleados sin manager).
2. En cada paso añadimos los subordinados del nivel actual, aumentando el nivel (level) en uno.
3. En la consulta principal seleccionamos toda la jerarquía, ordenada por nivel e id de empleado.

Consejos útiles y errores típicos

• Demasiados CTE: no los uses donde puedas arreglártelas con una subconsulta. Los CTE a veces pueden bajar el rendimiento por la materialización de datos.
• Nombres de CTE: pon nombres claros y cortos a tus CTE para que las consultas sean legibles.
• Orden de ejecución: recuerda que los CTE se ejecutan estrictamente en el orden en que los declaras.
• Agrupación de datos: usa GROUP BY solo donde sea necesario para evitar operaciones de más.

Todos estos ejemplos muestran cómo los CTE se pueden usar para dividir tareas complejas en pasos, mejorando la legibilidad y el mantenimiento de las consultas. ¡Ahora tienes las herramientas para resolver tareas analíticas complejas usando PostgreSQL!