Creazione di report analitici con PL/pgSQL

I report analitici sono rappresentazioni sistematiche dei dati che aiutano a prendere decisioni. Per esempio:

• I manager vogliono vedere qual è stato il fatturato del mese scorso.
• Gli analisti cercano trend nel mercato.
• Gli sviluppatori monitorano le performance dell’applicazione.

Immagina di essere uno chef che gestisce un ristorante enorme. Per capire quali piatti vengono ordinati più spesso, ti serve un report. PostgreSQL in questo caso è il tuo database di ricette e ordini, mentre PL/pgSQL (le procedure) è il tuo assistente in cucina che automatizza l’analisi degli ordini.

Basi della creazione di report analitici

Un report analitico è uno strumento per aggregare, filtrare, ordinare e organizzare i dati per ottenere informazioni utili. Di solito, la struttura di un report include questi step:

1. Preparazione dei dati: selezione delle info dalle tabelle, filtraggio e pre-elaborazione.
2. Aggregazione dei dati: calcolo delle metriche (scontrino medio, totale delle vendite ecc.).
3. Formattazione: organizzazione dei dati in modo leggibile.
4. Output dei risultati: presentazione del report agli utenti o salvataggio in una tabella.

Ognuno di questi step può essere realizzato usando procedure PL/pgSQL.

Creazione di una procedura per un report analitico

Vediamo un esempio base di creazione di un report analitico. Supponiamo di avere una tabella orders dove sono salvati i dati degli ordini:

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

Il nostro obiettivo: creare un report sul totale delle vendite per un mese specifico. Cioè, vogliamo vedere:

• Mese.
• Totale delle vendite per quel mese.

Struttura della procedura

Ecco il piano della nostra procedurina (tranquillo, programmare in PL/pgSQL non morde):

1. Riceviamo un parametro in input — il mese.
2. Selezioniamo i dati di quel mese dalla tabella orders.
3. Calcoliamo il totale delle vendite.
4. Restituiamo il risultato.

Implementazione della procedura

Esempio di codice:

CREATE OR REPLACE FUNCTION monthly_sales_report(p_month DATE)
RETURNS TABLE (
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    -- Selezioniamo i dati per il mese specificato e li aggreghiamo
    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. Parametro in input: p_month — una data. Lo useremo per filtrare i dati per mese.
2. RETURN QUERY: questa è una cosa magica che ti permette di restituire dati direttamente dalla procedura.
3. DATE_TRUNC: usato per arrotondare order_date all’inizio del mese.
4. SUM: funzione di aggregazione per sommare tutti gli ordini.
5. GROUP BY: raggruppiamo i dati per mese, visto che i report sono mensili.

Ora possiamo chiamare la nostra funzione:

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

E otterremo qualcosa tipo:

Questa funzione è la base. Ora complichiamo un po’!

Creazione di un report più complesso

Ora immagina di voler suddividere le vendite per cliente. Quindi, il nostro report deve mostrare:

• Cliente
• Mese
• Totale degli ordini di quel cliente per il mese

Modifichiamo la procedura

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;

Ora chiamiamo la procedura:

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

E il risultato potrebbe essere così:

Usare tabelle temporanee

A volte, quando crei report complessi, può essere utile usare tabelle temporanee. Per esempio, se devi lavorare su dati intermedi.

CREATE OR REPLACE FUNCTION temp_table_example(p_month DATE)
RETURNS VOID AS $$
BEGIN
    -- Creiamo una tabella temporanea
    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);

    -- Facciamo calcoli aggiuntivi o manipolazioni su questa tabella
    -- Per esempio, mostriamo i top-3 clienti per totale ordini
    RAISE NOTICE 'Top-3 clienti per il mese %:', p_month;
    FOR record IN
        SELECT customer_id, total_sales
        FROM temp_sales
        ORDER BY total_sales DESC
        LIMIT 3
    LOOP
        RAISE NOTICE 'Cliente: %, Totale: %', record.customer_id, record.total_sales;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

In questo caso la tabella temporanea temp_sales viene usata per salvare risultati intermedi.

Consigli utili

1. Ottimizzazione: usa gli indici per velocizzare le query.
2. Errori di divisione per zero: controlla sempre il divisore per non “rompere” il report.
3. Formattazione delle date: usa funzioni tipo TO_CHAR per un output più leggibile.

Spero che non ti sia addormentato troppo! Ci aspettano task ancora più tosti e interessanti, quindi resta sveglio!