Esempio: calcolo dello scontrino medio sugli ordini degli ultimi 3 mesi

In questa lezione vediamo un esempio pratico interessante.

Lo scontrino medio è una metrica che mostra quanto in media spende un cliente per un acquisto. È una delle metriche business chiave che permette di:

• analizzare il cambiamento del potere d'acquisto dei clienti,
• individuare trend nelle vendite,
• valutare l'efficacia delle campagne di marketing.

Definizione del problema

Immagina di avere un database con la tabella orders, dove sono salvati gli ordini. Il nostro obiettivo:

1. Calcolare lo scontrino medio per gli ordini fatti negli ultimi tre mesi.
2. Automatizzare questo calcolo tramite una procedura.
3. Salvare il risultato in una tabella separata per analisi future.

Espandiamo il nostro database: struttura della tabella orders

Per cominciare assicuriamoci di avere una tabella con i dati necessari. Ecco come potrebbe essere la struttura della tabella 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 — identificatore unico dell'ordine.
• customer_id — cliente che ha fatto l'ordine.
• order_date — data in cui è stato effettuato l'ordine.
• total_amount — importo totale dell'ordine.

Per esempio aggiungiamo qualche record nella tabella, così abbiamo qualcosa su cui lavorare:

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

Calcolo manuale dello scontrino medio

Prima di automatizzare il processo, scriviamo una query base che calcola lo scontrino medio degli ultimi 3 mesi. Useremo la data corrente (CURRENT_DATE) e la funzione AVG() per il calcolo della media.

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

Cosa succede qui:

• AVG(total_amount) — funzione aggregata che calcola il valore medio di total_amount.
• CURRENT_DATE - INTERVAL '3 months' — seleziona gli ordini fatti negli ultimi tre mesi.
• ROUND(..., 2) — arrotonda il risultato a due decimali.

Il risultato della query sarà più o meno così:

Automatizzazione tramite procedura

Ora il nostro obiettivo è creare una procedura che farà questo calcolo in automatico e loggherà il risultato in una tabella separata. Prima creiamo la tabella per salvare i log di analytics.

Creazione della tabella 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 — data e ora della registrazione.
• metric_name — nome della metrica (nel nostro caso "averagecheck_3_months").
• metric_value — valore calcolato della metrica.

Creazione della procedura

Ora scriviamo una procedura che:

1. Calcola lo scontrino medio degli ultimi tre mesi.
2. Salva il risultato nella tabella log_analytics.

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

    -- Passo 2: Log del risultato
    INSERT INTO log_analytics (metric_name, metric_value)
    VALUES ('average_check_3_months', avg_check);

    -- Output per debug (opzionale)
    RAISE NOTICE 'Scontrino medio: %', avg_check;
END;
$$ LANGUAGE plpgsql;

Ora puoi chiamare questa funzione e lei salverà automaticamente il risultato nella tabella log_analytics:

SELECT calculate_average_check();

Automatizzazione tramite scheduler

Nella lezione precedente abbiamo già installato uno scheduler. Se lavori su Linux — era l'estensione pg_cron; se usi Windows o macOS — probabilmente hai configurato l'avvio tramite scheduler di sistema (cron o Task Scheduler). Ora che è tutto pronto, colleghiamo la nostra procedura al piano orario.

Se sei su Linux e usi pg_cron assicurati che l'estensione sia attivata nel database giusto:

CREATE EXTENSION IF NOT EXISTS pg_cron;

(Ricorda: l'installazione di pg_cron e la configurazione del parametro shared_preload_libraries sono già state spiegate nella lezione precedente.)

Ora puoi schedulare l'esecuzione della nostra funzione calculate_average_check() — ad esempio, ogni giorno a mezzanotte:

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

Spiegazione:

• 'daily_avg_check' — nome del task;
• '0 0 * * *' — espressione cron per lanciare alle 00:00 ogni giorno;
• il comando dentro $$ — SQL che verrà eseguito.

Se sei su Windows o macOS, pg_cron su questi sistemi non funziona (su Windows — per niente, su macOS — serve compilazione manuale). Ma hai già configurato lo scheduler di sistema — ti resta solo collegare il file SQL.

1. Crea un file con la query:

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

2. Usa psql per eseguire il file secondo la pianificazione:

   • Su Linux/macOS:

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

     (da aggiungere tramite crontab -e)
   • Su Windows Task Scheduler:
     • Specifica il percorso a psql.exe.
     • Negli argomenti:
       -U postgres -d your_database -f "C:\path\to\script.sql"

Così, indipendentemente dal tuo sistema, la procedura verrà eseguita automaticamente e registrerà regolarmente lo scontrino medio nella tabella log_analytics. Se non sei sicuro di quale metodo usare, torna alla lezione precedente — lì trovi sia installazione che configurazione dello scheduler per le varie piattaforme.

Verifica e analisi dei risultati

Vediamo cosa abbiamo ottenuto. Facciamo una query sulla tabella log_analytics:

SELECT * FROM log_analytics ORDER BY log_date DESC;

Esempio di risultato:

Ora abbiamo il log di tutti i calcoli dello scontrino medio! Questi dati possono essere usati per generare report o analizzare i cambiamenti della metrica nel tempo.

Errori comuni e come evitarli

Lavorare con procedure analitiche per il calcolo dello scontrino medio può portare a diversi errori tipici.

Uno di questi è dimenticare di gestire i risultati vuoti. Se negli ultimi tre mesi non ci sono stati ordini, la funzione AVG() restituirà NULL, il che può causare problemi nel log. Per evitarlo, puoi usare COALESCE():

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

Un altro errore è avere dati non corretti nella tabella orders. Ad esempio, importi negativi o date non valide. Si consiglia di controllare regolarmente i dati o aggiungere vincoli a livello di database (ad esempio, CHECK (total_amount > 0)).

Complimenti, ora hai una procedura completa che calcola automaticamente lo scontrino medio degli ultimi tre mesi e salva il risultato per analisi future. Questo è solo uno dei tanti esempi di come PostgreSQL e PL/pgSQL possono aiutarti ad automatizzare task analitici. Nella prossima lezione continueremo con scenari analitici ancora più avanzati. A presto!