Registro de datos analíticos en tablas separadas

Imagina que estás haciendo un informe de ventas de la semana. Los cálculos están listos, los clientes están contentos. Pero al mes te preguntan: "¿Puedes mostrar lo que había en ese informe?" Si no guardaste los datos de antemano, tendrás que reconstruirlos a mano o decir "no puedo". No solo es incómodo, también puede afectar tu reputación.

El registro de datos analíticos resuelve varias tareas importantes:

• Guardar el historial: fijas las métricas clave (por ejemplo, ingresos, número de pedidos) para ciertos periodos.
• Auditoría y diagnóstico: si algo sale mal, siempre puedes revisar qué datos se registraron.
• Comparación de datos: añadiendo marcas de tiempo, puedes analizar cómo cambian los indicadores con el tiempo.
• Reutilización de datos: las métricas guardadas pueden usarse en otras tareas analíticas.

Idea principal: tabla log_analytics

Para registrar datos analíticos creamos una tabla especial que guarda todos los indicadores clave. Cada nuevo resultado es una nueva fila en la tabla. Para entender mejor cómo funciona, vamos a empezar con un escenario básico.

Ejemplo de estructura de tabla

En la tabla log_analytics vamos a guardar datos sobre informes. Aquí tienes la estructura (DDL — Data Definition Language):

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY, -- Identificador único del registro
    report_name TEXT NOT NULL, -- Nombre del informe o métrica
    report_date DATE DEFAULT CURRENT_DATE, -- Fecha a la que corresponde el informe
    category TEXT, -- Categoría de los datos (por ejemplo, región, producto)
    metric_value NUMERIC NOT NULL, -- Valor de la métrica
    created_at TIMESTAMP DEFAULT NOW() -- Fecha y hora del registro
);

• log_id: identificador principal del registro.
• report_name: nombre del informe o métrica, por ejemplo, "Weekly Sales".
• report_date: fecha a la que corresponde la métrica. Por ejemplo, si son ventas del 1 de octubre, aquí será 2023-10-01.
• category: ayuda a agrupar los datos, por ejemplo, por regiones.
• metric_value: valor numérico de la métrica del informe.
• created_at: marca de tiempo del registro.

Ejemplo de inserción de datos en log_analytics

Supón que calculaste los ingresos de octubre para la región "Norte". ¿Cómo guardas ese valor?

INSERT INTO log_analytics (report_name, report_date, category, metric_value)
VALUES ('Monthly Revenue', '2023-10-01', 'North', 15000.75);

Resultado:

Creando un procedimiento para registrar datos

Obvio, no vamos a meter los datos a mano cada semana o mes. Así que vamos a automatizar el proceso con un procedimiento.

Vamos a crear un procedimiento sencillo para registrar datos de ingresos:

CREATE OR REPLACE FUNCTION log_monthly_revenue(category TEXT, revenue NUMERIC)
RETURNS VOID AS $$
BEGIN
    INSERT INTO log_analytics (report_name, report_date, category, metric_value)
    VALUES ('Monthly Revenue', CURRENT_DATE, category, revenue);
END;
$$ LANGUAGE plpgsql;

Ahora el procedimiento log_monthly_revenue recibe dos parámetros:

• category: categoría de los datos, por ejemplo, región.
• revenue: valor de los ingresos

Así llamas a esta función para registrar ingresos:

SELECT log_monthly_revenue('North', 15000.75);

El resultado será igual que si lo insertaras con INSERT.

Ideas extra para la estructura de logs

A veces la métrica clave no es solo una, sino varias a la vez. Veamos cómo guardar indicadores extra como número de pedidos o ticket medio.

Actualizamos la estructura de la tabla:

CREATE TABLE log_analytics_extended (
    log_id SERIAL PRIMARY KEY,
    report_name TEXT NOT NULL,
    report_date DATE DEFAULT CURRENT_DATE,
    category TEXT,
    metric_values JSONB NOT NULL, -- Guardar métricas en formato JSONB
    created_at TIMESTAMP DEFAULT NOW()
);

Aquí lo importante es el uso del tipo JSONB para guardar varias métricas en un solo campo.

Ejemplo de inserción en la tabla extendida

Supón que necesitas guardar tres métricas a la vez: ingresos, número de pedidos y ticket medio. Así sería el query:

INSERT INTO log_analytics_extended (report_name, category, metric_values)
VALUES (
    'Monthly Revenue',
    'North',
    '{"revenue": 15000.75, "orders": 45, "avg_check": 333.35}'::jsonb
);

Resultado:

Ejemplos de uso de logs: análisis de ingresos

Supón que quieres saber el ingreso total de todas las regiones en octubre. Aquí tienes el query:

SELECT SUM((metric_values->>'revenue')::NUMERIC) AS total_revenue
FROM log_analytics_extended
WHERE report_date BETWEEN '2023-10-01' AND '2023-10-31';

Ejemplos de uso de logs: tendencias por región

Analizamos cómo cambian los ingresos por región:

SELECT category, report_date, (metric_values->>'revenue')::NUMERIC AS revenue
FROM log_analytics_extended
ORDER BY category, report_date;

Manejo de errores típicos

Al registrar datos analíticos puedes cometer algunos errores. Vamos a hablar de ellos y cómo evitarlos.

• Error: olvidaste indicar la categoría o la fecha. Se recomienda poner valores por defecto en la tabla, por ejemplo DEFAULT CURRENT_DATE.
• Error: duplicación de registros. Para evitar duplicados, puedes añadir un índice único:

  CREATE UNIQUE INDEX unique_log_entry
  ON log_analytics (report_name, report_date, category);
  

• Error: cálculo de métricas con división por cero. ¡Siempre revisa el divisor! Usa NULLIF:

  SELECT revenue / NULLIF(order_count, 0) AS avg_check FROM orders;
  

Aplicación en proyectos reales

El registro de datos analíticos es útil en muchos ámbitos:

• Retail: seguimiento de ingresos y ventas por categorías de productos.
• Servicios: análisis de carga de servidores o aplicaciones.
• Finanzas: control de transacciones y gastos.

Estos datos te ayudarán no solo a explicar qué pasó, sino a tomar decisiones basadas en lo que ves en los logs. Ahora ya sabes cómo guardar el historial de datos analíticos en PostgreSQL. ¡Genial, te esperan aún más conocimientos útiles!