Creando informes analíticos con PL/pgSQL

Los informes analíticos son representaciones sistematizadas de datos que ayudan a tomar decisiones. Por ejemplo:

• Los managers quieren ver cuál fue la facturación del mes pasado.
• Los analistas buscan tendencias en el mercado.
• Los desarrolladores monitorizan el rendimiento de la app.

Imagínate que eres un chef que dirige un restaurante enorme. Para entender qué platos se piden más, necesitas un informe. PostgreSQL en este caso es tu base de datos de recetas y pedidos, y PL/pgSQL (procedimientos) es tu ayudante en la cocina que automatiza el proceso de analizar los pedidos.

Bases para crear informes analíticos

Un informe analítico es una herramienta para agregar, filtrar, ordenar y organizar datos con el objetivo de obtener información útil. Normalmente, la estructura del informe incluye las siguientes etapas:

1. Preparación de datos: selección de info de las tablas, filtrado y preprocesamiento.
2. Agregación de datos: cálculo de métricas (ticket medio, suma total de ventas, etc.).
3. Formateo: organizar los datos en un formato fácil de entender.
4. Salida de resultados: mostrar el informe a los usuarios o guardarlo en una tabla.

Cada una de estas etapas se puede hacer usando procedimientos PL/pgSQL.

Creando un procedimiento para un informe analítico

Vamos a ver un ejemplo básico de cómo crear un informe analítico. Supón que tenemos una tabla orders donde se guardan los datos de los pedidos:

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

Nuestra tarea: crear un informe de la suma de ventas para un mes dado. O sea, queremos ver:

• Mes.
• Suma total de ventas para ese mes.

Estructura del procedimiento

Este es el plan de nuestro procedimiento (tranqui, programar en PL/pgSQL no muerde):

1. Recibimos un parámetro de entrada — el mes.
2. Seleccionamos los datos de ese mes de la tabla orders.
3. Calculamos la suma total de ventas.
4. Devolvemos el resultado.

Implementación del procedimiento

Ejemplo de código:

CREATE OR REPLACE FUNCTION monthly_sales_report(p_month DATE)
RETURNS TABLE (
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    -- Seleccionamos los datos del mes indicado y los agregamos
    RETURN QUERY
    SELECT 
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(o.total_amount) AS total_sales
    FROM orders o
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
    GROUP BY 1;
END;
$$ LANGUAGE plpgsql;

1. Parámetro de entrada: p_month — fecha. Lo usamos para filtrar los datos por mes.
2. RETURN QUERY: esta cosa mágica permite devolver datos directamente desde el procedimiento.
3. DATE_TRUNC: se usa para redondear order_date al inicio del mes.
4. SUM: función agregada para sumar todos los pedidos.
5. GROUP BY: agrupamos los datos por mes, ya que los informes son mensuales.

Ahora podemos llamar a nuestra función:

SELECT * FROM monthly_sales_report('2023-08-01');

Y obtendremos algo así:

Esta función es la base. ¡Vamos a complicarlo un poco!

Creando un informe más complejo

Ahora imagina que queremos dividir las ventas por cliente. O sea, nuestro informe debe mostrar:

• Cliente
• Mes
• Suma de pedidos de ese cliente en el mes

Cambiamos el procedimiento

CREATE OR REPLACE FUNCTION customer_monthly_report(p_month DATE)
RETURNS TABLE (
    customer_id INT,
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        o.customer_id,
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(o.total_amount) AS total_sales
    FROM orders o
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
    GROUP BY o.customer_id, DATE_TRUNC('month', o.order_date);
END;
$$ LANGUAGE plpgsql;

Ahora llamamos al procedimiento:

SELECT * FROM customer_monthly_report('2023-08-01');

Y el resultado puede ser así:

Usando tablas temporales

A veces, al crear informes complejos, es útil usar tablas temporales. Por ejemplo, si necesitas procesar datos intermedios.

CREATE OR REPLACE FUNCTION temp_table_example(p_month DATE)
RETURNS VOID AS $$
BEGIN
    -- Creamos una tabla temporal
    CREATE TEMP TABLE temp_sales AS
    SELECT
        customer_id,
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS total_sales
    FROM orders
    WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', p_month)
    GROUP BY customer_id, DATE_TRUNC('month', order_date);

    -- Hacemos cálculos o manipulaciones adicionales con esta tabla
    -- Por ejemplo, mostramos el top-3 de clientes por suma de pedidos
    RAISE NOTICE 'Top-3 clientes del mes %:', p_month;
    FOR record IN
        SELECT customer_id, total_sales
        FROM temp_sales
        ORDER BY total_sales DESC
        LIMIT 3
    LOOP
        RAISE NOTICE 'Cliente: %, Suma: %', record.customer_id, record.total_sales;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

En este caso, la tabla temporal temp_sales se usa para guardar resultados intermedios.

Consejos útiles

1. Optimización: usa índices para acelerar las consultas.
2. Errores de división por cero: siempre revisa el divisor para no "romper" el informe.
3. Formateo de fechas: usa funciones como TO_CHAR para mostrar los datos de forma cómoda.

¡Espero que no te hayas dormido mucho! Se vienen tareas aún más complejas e interesantes, ¡así que no te relajes!

1

Cuestionario/control

Procedimientos para analítica, nivel 59, lección 4

No disponible

Procedimientos para analítica

Procedimientos para analítica

Abrir