Ejemplo: cálculo del ticket medio de pedidos en los últimos 3 meses

En esta lección vamos a ver un ejemplo práctico interesante.

El ticket medio es una métrica que muestra cuánto gasta de media un cliente en una compra. Es una de las métricas clave de negocio, que permite:

• analizar el cambio en el poder adquisitivo de los clientes,
• identificar tendencias en las ventas,
• evaluar la efectividad de campañas de marketing.

Planteamiento del problema

Imagina que tenemos una base de datos con una tabla orders, donde se guardan los pedidos. Nuestro objetivo:

1. Calcular el ticket medio de los pedidos realizados en los últimos tres meses.
2. Automatizar este cálculo usando un procedimiento.
3. Guardar el resultado en una tabla aparte para análisis posterior.

Ampliamos nuestra base de datos: estructura de la tabla orders

Para empezar, vamos a asegurarnos de que tenemos una tabla con los datos necesarios. Así podría verse la estructura de la tabla orders:

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
);

• order_id — identificador único del pedido.
• customer_id — cliente que hizo el pedido.
• order_date — fecha en la que se realizó el pedido.
• total_amount — importe total del pedido.

Por ejemplo, vamos a añadir algunos registros a la tabla para tener con qué trabajar:

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
    (1, '2023-07-15', 100.00),
    (2, '2023-08-10', 200.50),
    (3, '2023-09-01', 150.75),
    (1, '2023-09-20', 300.00),
    (4, '2023-09-25', 250.00),
    (5, '2023-10-05', 450.00);

Cálculo manual del ticket medio

Antes de automatizar el proceso, vamos a escribir una consulta básica que calcule el ticket medio de los últimos 3 meses. Vamos a usar la fecha actual (CURRENT_DATE) y la función AVG() para calcular la media.

SELECT ROUND(AVG(total_amount), 2) AS avg_check
FROM orders
WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

¿Qué pasa aquí?

• AVG(total_amount) — función agregada que calcula el valor medio de total_amount.
• CURRENT_DATE - INTERVAL '3 months' — selecciona los pedidos hechos en los últimos tres meses.
• ROUND(..., 2) — redondea el resultado a dos decimales.

El resultado de la consulta se verá más o menos así:

Automatización con un procedimiento

Ahora nuestro objetivo es crear un procedimiento que haga este cálculo automáticamente y registre el resultado en una tabla aparte. Primero, vamos a crear la tabla para guardar los logs de analítica.

Creación de la tabla log_analytics

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY,
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metric_name VARCHAR(50),
    metric_value NUMERIC(10, 2)
);

• log_date — fecha y hora del registro.
• metric_name — nombre de la métrica (en nuestro caso "averagecheck_3_months").
• metric_value — valor calculado de la métrica.

Creación del procedimiento

Ahora vamos a escribir un procedimiento que:

1. Calcula el ticket medio de los últimos tres meses.
2. Guarda el resultado en la tabla log_analytics.

CREATE OR REPLACE FUNCTION calculate_average_check()
RETURNS VOID AS $$
DECLARE
    avg_check NUMERIC(10, 2);
BEGIN
    -- Paso 1: Calcular el ticket medio
    SELECT ROUND(AVG(total_amount), 2)
    INTO avg_check
    FROM orders
    WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

    -- Paso 2: Registrar el resultado
    INSERT INTO log_analytics (metric_name, metric_value)
    VALUES ('average_check_3_months', avg_check);

    -- Mostrar información para depuración (opcional)
    RAISE NOTICE 'Ticket medio: %', avg_check;
END;
$$ LANGUAGE plpgsql;

Ahora puedes llamar a esta función y automáticamente guardará el resultado en la tabla log_analytics:

SELECT calculate_average_check();

Automatización con el planificador de tareas

En la lección anterior ya instalamos el planificador de tareas. Si trabajas en Linux — era la extensión pg_cron; si usas Windows o macOS — probablemente configuraste la ejecución a través del planificador del sistema (cron o Task Scheduler). Ahora que todo está listo, vamos a conectar nuestro procedimiento al horario.

Si estás en Linux y usas pg_cron asegúrate de que la extensión está activada en la base de datos correcta:

CREATE EXTENSION IF NOT EXISTS pg_cron;

(Recuerda: la instalación de pg_cron y la configuración del parámetro shared_preload_libraries ya se vieron en la clase anterior.)

Ahora puedes programar la ejecución de nuestra función calculate_average_check() — por ejemplo, cada día a medianoche:

SELECT cron.schedule(
    'daily_avg_check',
    '0 0 * * *',
    $$ SELECT calculate_average_check(); $$
);

Explicación:

• 'daily_avg_check' — nombre de la tarea;
• '0 0 * * *' — expresión cron para ejecutarlo a las 00:00 cada día;
• el comando dentro de $$ — el SQL que se ejecutará.

Si estás en Windows o macOS, pg_cron no funciona en estos sistemas (en Windows — nada, en macOS — requiere compilación manual). Pero ya tienes el planificador del sistema configurado — solo falta conectar el archivo SQL.

1. Crea un archivo con la consulta:

   echo "SELECT calculate_average_check();" > /path/to/script.sql
   

2. Usa psql para ejecutar el archivo según el horario:

   • En Linux/macOS:

       0 0 * * * psql -h localhost -U postgres -d your_database -f /path/to/script.sql
     

     (se añade con crontab -e)
   • En Windows Task Scheduler:
     • Indica la ruta a psql.exe.
     • En los argumentos:
       -U postgres -d your_database -f "C:\path\to\script.sql"

Así, independientemente de tu sistema, el procedimiento se ejecutará automáticamente y registrará regularmente el ticket medio en la tabla log_analytics. Si no tienes claro qué método usas, vuelve a la lección anterior — ahí se explica la instalación y configuración del planificador para cada plataforma.

Comprobación y análisis de resultados

Vamos a ver qué hemos conseguido. Consultamos los datos de la tabla log_analytics:

SELECT * FROM log_analytics ORDER BY log_date DESC;

Ejemplo de resultado:

¡Ahora tenemos el registro de todos los cálculos del ticket medio! Estos datos se pueden usar para generar informes o analizar cómo cambia la métrica a lo largo del tiempo.

Errores frecuentes y cómo evitarlos

Trabajar con procedimientos analíticos para calcular el ticket medio puede estar relacionado con varios errores típicos.

Uno de ellos es olvidar tener en cuenta los resultados vacíos. Si en los últimos tres meses no hubo pedidos, la función AVG() devolverá NULL, lo que puede causar problemas al registrar el log. Para evitarlo, puedes usar COALESCE():

SELECT ROUND(COALESCE(AVG(total_amount), 0), 2) AS avg_check

Otro error es tener datos incorrectos en la tabla orders. Por ejemplo, importes negativos o fechas no válidas. Se recomienda revisar los datos regularmente o añadir restricciones a nivel de base de datos (por ejemplo, CHECK (total_amount > 0)).

¡Enhorabuena, ahora tienes un procedimiento completo que calcula automáticamente el ticket medio de los últimos tres meses y guarda el resultado para análisis posterior! Este es solo uno de muchos ejemplos de cómo PostgreSQL y PL/pgSQL pueden ayudarte a automatizar tareas analíticas. En la próxima lección seguiremos viendo escenarios analíticos más complejos. ¡Nos vemos!