Log delle metriche analitiche in tabelle separate

Immagina che stai preparando un report sulle vendite della settimana. Hai fatto tutti i calcoli, i clienti sono contenti. Ma dopo un mese ti chiedono: "Puoi farci vedere cosa c'era in quel report?" Se non hai salvato i dati in anticipo, dovrai ricostruirli a mano o rispondere "non posso". Non è solo scomodo, ma può anche rovinare la tua reputazione.

Il logging dei dati analitici risolve diversi problemi importanti:

• Salvataggio della cronologia: registri le metriche chiave (tipo fatturato, numero di ordini) per periodi specifici.
• Audit e diagnostica: se qualcosa va storto, puoi sempre controllare quali dati sono stati registrati.
• Confronto dei dati: aggiungendo timestamp, puoi analizzare come cambiano le metriche nel tempo.
• Riutilizzo dei dati: le metriche salvate possono essere usate in altre analisi.

Idea base: tabella log_analytics

Per loggare i dati analitici creiamo una tabella speciale che tiene tutti i KPI. Ogni nuovo risultato è una nuova riga nella tabella. Per capire meglio come funziona, partiamo da uno scenario base.

Esempio di struttura della tabella

Nella tabella log_analytics salviamo i dati dei report. Ecco la struttura (DDL — Data Definition Language):

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY, -- Identificatore unico della riga
    report_name TEXT NOT NULL, -- Nome del report o della metrica
    report_date DATE DEFAULT CURRENT_DATE, -- Data a cui si riferisce il report
    category TEXT, -- Categoria dei dati (tipo regione, prodotto)
    metric_value NUMERIC NOT NULL, -- Valore della metrica
    created_at TIMESTAMP DEFAULT NOW() -- Data e ora del logging
);

• log_id: identificatore principale della riga.
• report_name: nome del report o della metrica, tipo "Weekly Sales".
• report_date: data a cui si riferisce la metrica. Per esempio, se sono le vendite del 1 ottobre, qui ci sarà 2023-10-01.
• category: aiuta a raggruppare i dati, tipo per regione.
• metric_value: valore numerico della metrica del report.
• created_at: timestamp del logging.

Esempio di inserimento dati in log_analytics

Supponiamo di aver calcolato il fatturato di ottobre per la regione "Nord". Come salviamo questo valore?

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

Risultato:

Creazione di una procedura per il logging

Ovviamente non possiamo inserire i dati a mano ogni settimana o mese. Quindi automatizziamo il processo con una funzione.

Creiamo una semplice funzione per loggare i dati del fatturato:

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;

Ora la funzione log_monthly_revenue prende due parametri:

• category: categoria dei dati, tipo la regione.
• revenue: valore del fatturato

Ecco come chiamare questa funzione per registrare il fatturato:

SELECT log_monthly_revenue('Nord', 15000.75);

Il risultato sarà lo stesso che con INSERT.

Altre idee per la struttura dei log

A volte la metrica chiave non è una sola, ma più di una. Vediamo come gestire anche altre metriche, tipo numero di ordini o scontrino medio.

Aggiorniamo la struttura della tabella:

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, -- Salvataggio delle metriche in formato JSONB
    created_at TIMESTAMP DEFAULT NOW()
);

Qui la novità è l'uso del tipo JSONB per salvare più metriche in un solo campo.

Esempio di inserimento nella tabella estesa

Supponiamo di voler salvare tre metriche insieme: fatturato, numero di ordini e scontrino medio. Ecco l'esempio:

INSERT INTO log_analytics_extended (report_name, category, metric_values)
VALUES (
    'Monthly Revenue',
    'Nord',
    '{"fatturato": 15000.75, "ordini": 45, "scontrino_medio": 333.35}'::jsonb
);

Risultato:

Esempi di uso dei log: analisi dei ricavi

Supponiamo di voler sapere il fatturato totale di tutte le regioni per ottobre. Ecco la query:

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

Esempi di uso dei log: trend per regione

Analizziamo come cambia il fatturato per regione:

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

Gestione degli errori tipici

Quando si loggano dati analitici, si possono fare alcuni errori. Vediamo quali e come evitarli.

• Errore: dimenticato di indicare categoria o data. Si consiglia di mettere valori di default nella tabella, tipo DEFAULT CURRENT_DATE.
• Errore: duplicazione delle righe. Per evitare duplicati, puoi aggiungere un indice unico:

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

• Errore: calcolo delle metriche con divisione per zero. Controlla sempre il divisore! Usa NULLIF:

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

Applicazione nei progetti reali

Il logging dei dati analitici è utile in tantissimi ambiti:

• Retail: monitoraggio di ricavi e vendite per categoria di prodotto.
• Servizi: analisi del carico di server o applicazioni.
• Finanza: controllo di transazioni e spese.

Questi dati ti aiutano non solo a spiegare cosa è successo, ma anche a prendere decisioni basate su quello che vedi nei log. Ora sai come registrare la cronologia dei dati analitici in PostgreSQL. Grande, ci sono ancora un sacco di cose utili da imparare!