SQLite e Python per la Gestione del Magazzino: 6 Esercizi su SQL, Scorte e Riordino

Cerca:

Generic selectors
Exact matches only
Search in title
Search in content
Post Type Selectors

Un magazzino può registrare ogni entrata e ogni uscita senza che questo basti a chiarire quali acquisti anticipare, quali giacenze trasferire e quali prodotti rischiano di restare fermi. Il problema diventa più evidente quando l’azienda lavora con più sedi: una scorta abbondante a Nord non compensa necessariamente una carenza al Sud, e un prodotto che vende bene nel complesso può avere una domanda molto diversa da un magazzino all’altro.

In questo percorso seguiremo RicambiExpress, un distributore di ricambi automotive, attraverso sei esercizi. Useremo SQLite per conservare e interrogare i dati, Python per coordinare le elaborazioni, Pandas per ricostruire le serie giornaliere e Matplotlib per visualizzare alcuni risultati. Ogni esercizio parte da una domanda aziendale e mostra sia il codice sia l’output ottenuto eseguendolo sul dataset riproducibile descritto qui sotto.

Gli output sono stati rigenerati eseguendo il programma dall’inizio alla fine con il seed e la data di riferimento indicati. I dati sono simulati: servono a esercitarsi e non descrivono un’azienda reale. Per mantenere verificabili i risultati, non modificare il seed, la data di riferimento o la logica di generazione senza rieseguire tutte le celle.

Il caso RicambiExpress

RicambiExpress distribuisce cento tipi di ricambio attraverso quattro magazzini: WH1 nel Nord, WH2 nel Centro, WH3 nel Sud e WH4 nelle Isole. Il catalogo comprende cinque categorie: freni, filtri, lubrificanti, componenti elettrici e accessori.

Il responsabile operations vuole capire dove le giacenze sono inferiori ai punti di riordino. Il direttore finanziario vuole quantificare il valore delle scorte e sapere quali prodotti concentrano il capitale immobilizzato. Il responsabile acquisti, infine, vuole confrontare le soglie presenti nel gestionale con valori stimati a partire dalla domanda osservata.

Nel database distingueremo lo SKU, cioè il codice di un prodotto, dalla posizione, cioè la coppia prodotto-magazzino. Se lo SKU 21 è presente in tre sedi, si tratta di un solo prodotto e di tre posizioni d’inventario. Questa differenza è importante: una query che conta le posizioni non risponde alla stessa domanda di una query che conta gli SKU.

Struttura del database

Il database di RicambiExpress è organizzato in quattro tabelle, ciascuna con una responsabilità specifica:

  • products: contiene l’anagrafica dei prodotti, con informazioni come codice SKU, categoria e costo unitario;
  • warehouses: raccoglie i dati anagrafici dei magazzini;
  • inventory: registra le giacenze per ogni combinazione di prodotto e magazzino;
  • sales: memorizza i movimenti di vendita, associando ciascuna operazione al prodotto e al magazzino interessato.

Le tabelle sono collegate attraverso chiavi primarie e chiavi esterne.

La struttura può essere rappresentata schematicamente in questo modo:

products
--------
product_id (PK)
    |
    | 1
    |
    | N
inventory
---------
product_id (FK)
warehouse_id (FK)
stock_level
    |
    | N
    |
    | 1
warehouses
----------
warehouse_id (PK)


products (1) -------- (N) sales
                         |
                         |
                    warehouse_id (FK)
                         |
                         |
warehouses (1) -------- (N) sales

Come leggere lo schema

  • Un prodotto può essere presente in più magazzini e ogni magazzino può contenere molti prodotti. La tabella inventory realizza questa relazione molti-a-molti, conservando anche la quantità disponibile per ciascuna coppia prodotto-magazzino.
  • Un prodotto può comparire in numerosi movimenti di vendita, mentre ogni movimento fa riferimento a un solo prodotto.
  • Anche un magazzino può essere associato a numerose vendite, mentre ciascun movimento è registrato in un singolo magazzino.

La tabella inventory svolge quindi un ruolo centrale nell’organizzazione del database: non è soltanto un archivio delle quantità disponibili, ma rappresenta la relazione tra prodotti e magazzini. Le tabelle products e warehouses contengono invece le informazioni anagrafiche, evitando di ripetere gli stessi dati in ogni registrazione.

Questa organizzazione permette di interrogare il database per ottenere informazioni che coinvolgono più entità, ad esempio il valore delle scorte per categoria, la distribuzione delle giacenze tra magazzini o i prodotti che non registrano vendite recenti.

Pubblicità

Ambiente e dataset

Gli esempi si possono eseguire in un notebook Jupyter o Google Colab, una cella dopo l’altra, dall’alto verso il basso. In un normale file .py, le chiamate a display() possono essere sostituite da print(df.to_string(index=False)). Le librerie da installare sono:

%pip install pandas numpy matplotlib

sqlite3 fa parte della libreria standard di Python. Il database viene creato in memoria e vive finché resta aperta la connessione conn; per ripartire da zero basta rieseguire la cella di inizializzazione.

import sqlite3
from datetime import date, timedelta

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

SEED = 42
REFERENCE_DATE = date(2026, 11, 16)


def setup_db(seed=SEED, reference_date=REFERENCE_DATE):
    rng = np.random.default_rng(seed)
    conn = sqlite3.connect(":memory:")
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")

    conn.executescript("""
        CREATE TABLE products (
            product_id INTEGER PRIMARY KEY,
            category TEXT NOT NULL,
            unit_cost REAL NOT NULL CHECK(unit_cost >= 0),
            lead_time_giorni INTEGER NOT NULL CHECK(lead_time_giorni > 0)
        );
        CREATE TABLE warehouses (
            warehouse_id TEXT PRIMARY KEY,
            region TEXT NOT NULL
        );
        CREATE TABLE inventory (
            product_id INTEGER NOT NULL,
            warehouse_id TEXT NOT NULL,
            stock_level INTEGER NOT NULL CHECK(stock_level >= 0),
            reorder_point INTEGER NOT NULL CHECK(reorder_point >= 0),
            PRIMARY KEY (product_id, warehouse_id),
            FOREIGN KEY (product_id) REFERENCES products(product_id),
            FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
        );
        CREATE TABLE sales (
            sale_id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_id INTEGER NOT NULL,
            warehouse_id TEXT NOT NULL,
            quantity_sold INTEGER NOT NULL CHECK(quantity_sold > 0),
            sale_date TEXT NOT NULL,
            FOREIGN KEY (product_id, warehouse_id)
                REFERENCES inventory(product_id, warehouse_id)
        );
        CREATE INDEX idx_sales_position_date
            ON sales(product_id, warehouse_id, sale_date);
    """)

    categories = ["Freni", "Filtri", "Lubrificanti", "Elettrico", "Accessori"]
    warehouse_data = [
        ("WH1", "Nord"), ("WH2", "Centro"),
        ("WH3", "Sud"), ("WH4", "Isole")
    ]
    conn.executemany("INSERT INTO warehouses VALUES (?, ?)", warehouse_data)

    products = [
        (pid, str(rng.choice(categories)),
         round(float(rng.uniform(2, 500)), 2),
         int(rng.integers(5, 45)))
        for pid in range(1, 101)
    ]
    conn.executemany("INSERT INTO products VALUES (?, ?, ?, ?)", products)

    inventory_rows = []
    for pid in range(1, 101):
        for warehouse_id, _ in warehouse_data:
            if rng.random() < 0.70:
                inventory_rows.append((
                    pid, warehouse_id,
                    int(rng.integers(0, 500)),
                    int(rng.integers(20, 150))
                ))
    conn.executemany("INSERT INTO inventory VALUES (?, ?, ?, ?)", inventory_rows)

    active_positions = conn.execute("""
        SELECT product_id, warehouse_id
        FROM inventory
        ORDER BY product_id, warehouse_id
    """).fetchall()

    sales_rows = []
    for row in active_positions:
        stale = rng.random() < 0.20
        probability = 0.08 if stale else 0.45
        for days_ago in range(365):
            # Le posizioni stale non hanno vendite negli ultimi 90 giorni.
            if stale and days_ago < 90:
                continue
            if rng.random() < probability:
                sales_rows.append((
                    row["product_id"], row["warehouse_id"],
                    int(rng.integers(1, 30)),
                    (reference_date - timedelta(days=days_ago)).isoformat()
                ))

    conn.executemany("""
        INSERT INTO sales (product_id, warehouse_id, quantity_sold, sale_date)
        VALUES (?, ?, ?, ?)
    """, sales_rows)
    conn.commit()
    return conn


conn = setup_db()
for table in ("products", "warehouses", "inventory", "sales"):
    count = conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
    print(f"{table}: {count:,} record")
print("Violazioni FK:", len(conn.execute("PRAGMA foreign_key_check").fetchall()))

L’output iniziale è:

products: 100 record
warehouses: 4 record
inventory: 280 record
sales: 38904 record
Violazioni FK: 0

Le cento anagrafiche prodotto non sono tutte presenti in ogni sede: la probabilità di creare una posizione è del 70%. Per questo il database contiene 280 coppie prodotto-magazzino, anziché 400. Le vendite sono generate su 365 date consecutive che terminano il 16 novembre 2026; alcune posizioni sono intenzionalmente prive di vendite negli ultimi 90 giorni, così da poter verificare l’esercizio sulle giacenze senza rotazione recente.

La connessione usa :memory: perché vogliamo un ambiente pulito a ogni esecuzione. Il context manager with conn: gestisce commit e rollback, ma non chiude da solo la connessione. Per una connessione temporanea da chiudere esplicitamente si può usare contextlib.closing:

from contextlib import closing

with closing(setup_db()) as temp_conn:
    with temp_conn:
        n = temp_conn.execute("SELECT COUNT(*) FROM inventory").fetchone()[0]
        print(n)

Il risultato è 280. In questo esempio closing() chiude la connessione all’uscita dal blocco esterno; with temp_conn: gestisce la transazione. Non usare questo schema sulla connessione conn condivisa dagli esercizi se poi si intende continuare a interrogare quel database.

Pubblicità

Esercizio 1 — Fotografare le scorte e trovare le posizioni sotto soglia

Il responsabile operations riceve un report dei quattro magazzini e vuole sapere quante posizioni risultano sotto il punto di riordino. La prima analisi deve separare il conteggio delle posizioni dal conteggio degli SKU coinvolti; non deve ancora proporre quantità d’acquisto, perché servono anche domanda, tempi di consegna e criticità del componente.

totale = conn.execute(
    "SELECT COUNT(*) FROM inventory"
).fetchone()[0]

magazzini = [
    r[0] for r in conn.execute(
        "SELECT warehouse_id FROM warehouses ORDER BY warehouse_id"
    )
]

sotto = conn.execute("""
    SELECT COUNT(*) AS posizioni,
           COUNT(DISTINCT product_id) AS sku
    FROM inventory
    WHERE stock_level < reorder_point
""").fetchone()

print(f"Posizioni inventario: {totale}")
print("Magazzini:", magazzini)
print(f"Posizioni sotto scorta: {sotto['posizioni']}; "
      f"SKU distinti coinvolti: {sotto['sku']}")

df_rischio = pd.read_sql_query("""
    SELECT warehouse_id,
           COUNT(*) AS posizioni_totali,
           SUM(CASE WHEN stock_level < reorder_point THEN 1 ELSE 0 END)
               AS sotto_scorta
    FROM inventory
    GROUP BY warehouse_id
    ORDER BY sotto_scorta DESC
""", conn)
df_rischio["incidenza_pct"] = (
    100 * df_rischio["sotto_scorta"] / df_rischio["posizioni_totali"]
)
print(df_rischio.to_string(index=False))

fig, ax = plt.subplots(figsize=(8, 4))
ax.barh(df_rischio["warehouse_id"], df_rischio["sotto_scorta"])
ax.set_xlabel("Posizioni sotto scorta")
ax.set_title("Posizioni sotto il punto di riordino per magazzino")
for i, row in df_rischio.iterrows():
    ax.text(row["sotto_scorta"] + 0.3, i,
            f"{row['sotto_scorta']}/{row['posizioni_totali']} "
            f"({row['incidenza_pct']:.1f}%)", va="center")
plt.tight_layout()
plt.show()
plt.close()

Output dei conteggi:

Posizioni inventario: 280
Magazzini: ['WH1', 'WH2', 'WH3', 'WH4']
Posizioni sotto scorta: 44; SKU distinti coinvolti: 39
Magazzino Posizioni totali Sotto scorta Incidenza
WH1 70 16 22,86%
WH3 67 12 17,91%
WH4 73 9 12,33%
WH2 70 7 10,00%
Forse potrebbe interessarti anche:  Da Statistico a Data Scientist: 20 Anni Dentro l'Evoluzione di una Professione

Le 44 posizioni critiche riguardano 39 SKU distinti: alcuni prodotti sono sotto soglia in più di una sede. WH1 ha il maggior numero assoluto di posizioni sotto scorta e anche l’incidenza relativa più alta; WH2 ha invece l’incidenza più bassa. Il confronto percentuale evita di interpretare il solo conteggio grezzo senza considerare la dimensione dell’inventario di ogni magazzino.

La condizione applicata è stretta (stock_level < reorder_point): una posizione esattamente pari alla soglia non viene conteggiata. Il punto di riordino del dataset è generato casualmente a fini didattici e non rappresenta una politica di approvvigionamento validata.

COUNT(*) conta le righe, cioè le posizioni. COUNT(DISTINCT product_id) conta i codici prodotto senza duplicati. sqlite3.Row, impostato nella funzione iniziale, consente di leggere il valore con il nome della colonna, come sotto['posizioni'].

Mini quiz. Se uno SKU è presente in tre magazzini e tutte e tre le posizioni sono sotto soglia, quanto restituiscono COUNT(*) e COUNT(DISTINCT product_id)? Risposta: tre posizioni e un solo SKU.

Esercizio 2 — Valore delle scorte per categoria

Il direttore finanziario vuole un riepilogo per categoria con il numero di SKU, quanti hanno almeno una posizione sotto soglia e il valore economico complessivo delle giacenze. Per l’esercizio, gli accessori con costo unitario inferiore a 5 euro vengono esclusi; il filtro non deve togliere prodotti economici appartenenti ad altre categorie.

query = """
SELECT p.category,
       COUNT(DISTINCT p.product_id) AS sku_totali,
       COUNT(DISTINCT CASE
           WHEN i.stock_level < i.reorder_point THEN p.product_id
       END) AS sku_sotto_scorta,
       ROUND(SUM(i.stock_level * p.unit_cost), 2) AS valore_stock_eur
FROM inventory AS i
JOIN products AS p ON p.product_id = i.product_id
WHERE NOT (p.category = 'Accessori' AND p.unit_cost < 5)
GROUP BY p.category
HAVING COUNT(DISTINCT p.product_id) >= 3
ORDER BY sku_sotto_scorta DESC, valore_stock_eur DESC
"""
df_cat = pd.read_sql_query(query, conn)
print(df_cat.to_string(index=False))

fig, ax = plt.subplots(figsize=(9, 6))
ax.scatter(df_cat["sku_sotto_scorta"], df_cat["valore_stock_eur"], s=140)
for _, row in df_cat.iterrows():
    ax.annotate(row["category"],
                (row["sku_sotto_scorta"], row["valore_stock_eur"]),
                xytext=(6, 6), textcoords="offset points")
ax.set_xlabel("SKU distinti sotto scorta")
ax.set_ylabel("Valore delle giacenze (€)")
ax.set_title("Valore dello stock e SKU sotto soglia per categoria")
plt.tight_layout()
plt.show()
plt.close()
Categoria SKU totali SKU sotto scorta Valore stock (€)
Accessori 20 10 4.118.417,47
Lubrificanti 20 9 3.656.303,05
Elettrico 21 8 4.074.643,56
Freni 22 8 3.624.849,76
Filtri 16 4 2.922.894,10

Il valore è calcolato sommando giacenza × costo unitario per ogni posizione della categoria. Accessori ha il maggior numero di SKU sotto soglia, ma Elettrico ha un valore di stock leggermente inferiore a quello degli Accessori pur avendo due SKU critici in meno. Filtri ha sia il valore totale minore sia il numero più basso di SKU sotto soglia.

Questi numeri non indicano da soli quale categoria debba ricevere più budget. Il valore delle scorte è una fotografia contabile, non una misura della redditività o della rotazione; inoltre il numero di SKU sotto soglia non dice quanto sia grave ogni carenza. Il grafico mostra i due indicatori insieme proprio per aiutare a formulare domande successive, non per produrre automaticamente una priorità d’acquisto.

Il filtro specifico sugli accessori è in WHERE, perché riguarda le righe prima del raggruppamento. HAVING è invece usato per filtrare il risultato dell’aggregazione: la condizione richiede almeno tre SKU per gruppo e non può essere applicata a ogni riga prima che il conteggio sia calcolato. La somma del valore è a livello di posizione: se uno SKU è in tre magazzini, considera le giacenze di tutte e tre le sedi.

Mini quiz. Perché la soglia degli accessori non può essere scritta come unit_cost >= 5 per tutte le righe? Perché eliminerebbe anche prodotti economici di Freni, Filtri o delle altre categorie; la condizione deve essere limitata agli Accessori.

Pubblicità

Esercizio 3 — Posizioni senza vendite recenti e capitale immobilizzato

Il direttore finanziario vuole individuare le posizioni con giacenza positiva che non hanno registrato vendite negli ultimi 90 giorni. L’analisi è a livello prodotto-magazzino: lo stesso ricambio può essere fermo in una sede e avere vendite regolari in un’altra.

La finestra comprende 90 date, inclusa la data di riferimento: dal 19 agosto al 16 novembre 2026. La sottoquery scalare ricava la data dell’ultima vendita storica, mentre NOT EXISTS verifica che non ci siano vendite nella finestra recente.

data_limite_90 = (REFERENCE_DATE - timedelta(days=89)).isoformat()
data_riferimento = REFERENCE_DATE.isoformat()

query = """
SELECT i.product_id,
       i.warehouse_id,
       p.category,
       i.stock_level,
       p.unit_cost,
       ROUND(i.stock_level * p.unit_cost, 2) AS valore_immobilizzato,
       (SELECT MAX(s.sale_date)
        FROM sales AS s
        WHERE s.product_id = i.product_id
          AND s.warehouse_id = i.warehouse_id) AS ultima_vendita
FROM inventory AS i
JOIN products AS p ON p.product_id = i.product_id
WHERE i.stock_level > 0
  AND NOT EXISTS (
      SELECT 1
      FROM sales AS s
      WHERE s.product_id = i.product_id
        AND s.warehouse_id = i.warehouse_id
        AND s.sale_date BETWEEN ? AND ?
  )
ORDER BY valore_immobilizzato DESC
"""
df_fermi = pd.read_sql_query(
    query, conn, params=(data_limite_90, data_riferimento)
)
totale_fermo = df_fermi["valore_immobilizzato"].sum()
print("Posizioni senza vendite recenti:", len(df_fermi))
print("Capitale immobilizzato nelle posizioni:", f"€ {totale_fermo:,.2f}")
print(df_fermi.head(10).to_string(index=False))

ordinato = df_fermi.sort_values(
    "valore_immobilizzato", ascending=False
).reset_index(drop=True)
ordinato["pct_cum"] = (
    100 * ordinato["valore_immobilizzato"].cumsum() / totale_fermo
)

fig, ax1 = plt.subplots(figsize=(11, 5))
x = np.arange(len(ordinato))
ax1.bar(x, ordinato["valore_immobilizzato"], color="steelblue")
ax1.set_xlabel("Posizioni ordinate per valore immobilizzato")
ax1.set_ylabel("Valore immobilizzato (€)")
ax1.set_title("Concentrazione del capitale senza vendite recenti")
ax2 = ax1.twinx()
ax2.plot(x, ordinato["pct_cum"], color="darkorange")
ax2.axhline(80, color="gray", linestyle="--", linewidth=0.8)
ax2.set_ylabel("Valore cumulato (%)")
ax2.set_ylim(0, 105)
plt.tight_layout()
plt.show()
plt.close()

L’esecuzione restituisce 48 posizioni senza vendite recenti e con giacenza positiva, per un valore complessivo di € 3.066.260,25. Le prime dieci righe, dopo l’ordinamento per valore decrescente, sono:

SKU Magazzino Categoria Giacenza Costo unitario (€) Valore immobilizzato (€) Ultima vendita
21 WH2 Accessori 479 416,67 199.584,93 07/08/2026
89 WH4 Filtri 397 496,20 196.991,40 14/08/2026
7 WH4 Elettrico 466 411,74 191.870,84 10/08/2026
4 WH1 Elettrico 486 393,46 191.221,56 17/08/2026
12 WH2 Freni 368 446,77 164.411,36 25/07/2026
58 WH2 Lubrificanti 356 428,59 152.578,04 05/08/2026
2 WH4 Lubrificanti 432 349,29 150.893,28 10/08/2026
71 WH4 Filtri 481 246,37 118.503,97 27/07/2026
4 WH2 Elettrico 254 393,46 99.938,84 15/08/2026
78 WH3 Filtri 286 336,78 96.319,08 16/08/2026

Le prime quattro posizioni valgono ciascuna circa 190 mila euro, quindi la revisione iniziale può concentrarsi su un numero contenuto di casi. Nel dataset, le prime 21 posizioni della graduatoria bastano a raggiungere o superare l’80% del valore immobilizzato delle 48 posizioni. La linea del grafico rende visibile questa concentrazione; la soglia dell’80% è descrittiva e non è una regola per eliminare automaticamente i prodotti.

NOT EXISTS rende leggibile la domanda “non esiste una vendita per questa coppia prodotto-magazzino nella finestra”. Evita anche la trappola logica che può verificarsi con NOT IN quando la sottoquery restituisce valori NULL. MAX(sale_date) restituisce la data dell’ultima vendita storica, che può essere precedente alla finestra e quindi spiega perché la posizione è stata selezionata.

Una posizione senza vendite recenti non è necessariamente obsoleta. Potrebbe trattarsi di un ricambio raro ma necessario, oppure di un eccesso d’acquisto. Per decidere servirebbero anche informazioni sulla criticità tecnica, sulle sostituzioni compatibili, sugli ordini in corso e sulla possibilità di trasferire le scorte tra sedi.

Mini quiz. Perché la query filtra stock_level > 0? Perché la domanda dell’esercizio riguarda capitale ancora immobilizzato; le posizioni senza giacenza non contribuiscono a quel valore anche se non hanno vendite.

Esercizio 4 — Classificazione ABC del valore delle giacenze

L’azienda vuole dedicare controlli più frequenti ai prodotti che assorbono la quota maggiore del valore di magazzino. Per classificare gli SKU, sommiamo prima le giacenze di ogni prodotto nelle diverse sedi e poi ordiniamo i prodotti per valore decrescente.

In questo esempio la classe A comprende gli SKU il cui valore cumulato non supera l’80% del totale; B comprende quelli che portano il cumulato oltre l’80% e fino al 95%; C contiene gli SKU restanti. Poiché la soglia può cadere all’interno di un prodotto, le percentuali effettive di ciascuna classe non devono coincidere esattamente con 80% e 95%.

query_abc = """
WITH valori AS (
    SELECT p.product_id,
           p.category,
           SUM(i.stock_level * p.unit_cost) AS valore_stock
    FROM inventory AS i
    JOIN products AS p ON p.product_id = i.product_id
    GROUP BY p.product_id, p.category
    HAVING SUM(i.stock_level) > 0
), cumulato AS (
    SELECT product_id,
           category,
           valore_stock,
           SUM(valore_stock) OVER () AS valore_totale,
           SUM(valore_stock) OVER (
               ORDER BY valore_stock DESC, product_id
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS valore_cumulato
    FROM valori
)
SELECT product_id,
       category,
       ROUND(valore_stock, 2) AS valore_stock,
       ROUND(100.0 * valore_cumulato / NULLIF(valore_totale, 0), 2)
           AS pct_cumulata,
       CASE
           WHEN 100.0 * valore_cumulato / NULLIF(valore_totale, 0) <= 80
               THEN 'A'
           WHEN 100.0 * valore_cumulato / NULLIF(valore_totale, 0) <= 95
               THEN 'B'
           ELSE 'C'
       END AS classe_abc
FROM cumulato
ORDER BY valore_stock DESC, product_id
"""
df_abc = pd.read_sql_query(query_abc, conn)
print(df_abc.head(10).to_string(index=False))
print("\nSKU per classe:")
print(df_abc["classe_abc"].value_counts().sort_index().to_string())

colori = {"A": "crimson", "B": "darkorange", "C": "seagreen"}
fig, ax1 = plt.subplots(figsize=(12, 5))
x = np.arange(len(df_abc))
ax1.bar(x, df_abc["valore_stock"],
        color=df_abc["classe_abc"].map(colori))
ax1.set_xlabel("SKU ordinati per valore decrescente")
ax1.set_ylabel("Valore delle giacenze (€)")
ax1.set_title("Classificazione ABC delle scorte")
ax2 = ax1.twinx()
ax2.plot(x, df_abc["pct_cumulata"], color="black", linewidth=1.5)
ax2.axhline(80, linestyle="--", linewidth=0.8)
ax2.axhline(95, linestyle="--", linewidth=0.8)
ax2.set_ylabel("Valore cumulato (%)")
ax2.set_ylim(0, 105)
plt.tight_layout()
plt.show()
plt.close()

Il valore complessivo considerato è € 18.397.107,94. I primi dieci SKU sono:

Forse potrebbe interessarti anche:  Python: Richiedere l’input all’utente
SKU Categoria Valore stock (€) Cumulato (%) Classe
16 Accessori 683.153,84 3,71 A
66 Elettrico 557.565,55 6,74 A
87 Accessori 547.267,35 9,72 A
4 Elettrico 480.021,20 12,33 A
40 Freni 459.869,36 14,83 A
12 Freni 459.279,56 17,32 A
89 Filtri 452.534,40 19,78 A
79 Freni 421.577,64 22,08 A
21 Accessori 400.836,54 24,25 A
58 Lubrificanti 377.159,20 26,30 A

La distribuzione risultante è:

A    50
B    22
C    27

Sono classificati 99 SKU: uno dei cento prodotti non ha giacenze positive in nessuna sede ed è escluso dalla CTE tramite HAVING SUM(i.stock_level) > 0. Il risultato mostra anche perché la regola ABC non va letta come una legge universale: qui la classe A comprende 50 prodotti perché si applica la soglia al valore cumulato prodotto per prodotto, senza dividere uno SKU tra classi.

La prima CTE calcola il valore per SKU, sommando le giacenze dei magazzini. La seconda calcola sia il totale generale sia la somma progressiva. La chiave product_id usata come criterio secondario rende deterministico l’ordinamento in caso di valori uguali. La finestra ROWS ... CURRENT ROW fa sì che ogni riga includa il proprio valore e quelli precedenti.

La classe A suggerisce controlli più frequenti sul valore, ma non necessariamente maggiore criticità di servizio. Un componente di classe C potrebbe essere indispensabile per completare una riparazione. In un’analisi aziendale, l’ABC economico può essere affiancato a una classificazione per variabilità della domanda o importanza operativa.

Mini quiz. SUM(valore_stock) OVER () calcola il totale ripetuto su ogni riga e serve da denominatore; la somma con ORDER BY calcola il cumulato e serve da numeratore della percentuale progressiva.

Pubblicità

Esercizio 5 — Scorta di sicurezza e punto di riordino

Il responsabile acquisti vuole confrontare i punti di riordino registrati nel gestionale con una stima basata sugli ultimi 180 giorni per i prodotti della categoria Freni. Per ogni posizione prodotto-magazzino calcoliamo la domanda giornaliera media e la sua deviazione standard, includendo anche i giorni senza vendite.

Nel modello adottato, con domanda giornaliera indipendente e stazionaria e lead time deterministico, la scorta di sicurezza è:

[math]SS = Z \cdot \sigma_d \cdot \sqrt{LT}[/math]

e il punto di riordino è:

[math]ROP = \bar d \cdot LT + SS[/math]

[math]Z[/math] è il fattore associato al livello di servizio desiderato, [math]\sigma_d[/math] è la deviazione standard della domanda giornaliera e [math]LT[/math] è il tempo di approvvigionamento in giorni. Con [math]Z = 1{,}65[/math], l’approssimazione normale corrisponde a circa il 95% di probabilità di non esaurimento durante il lead time, a condizione che le ipotesi del modello siano ragionevoli. Non equivale a garantire che il 95% delle unità richieste sarà sempre servito.

Z = 1.65
calendar_180 = pd.date_range(
    end=pd.Timestamp(REFERENCE_DATE), periods=180, freq="D"
)
start_180 = calendar_180.min().strftime("%Y-%m-%d")

stock_freni = pd.read_sql_query("""
    SELECT i.product_id, i.warehouse_id, i.stock_level,
           i.reorder_point, p.lead_time_giorni, p.category
    FROM inventory AS i
    JOIN products AS p ON p.product_id = i.product_id
    WHERE p.category = 'Freni'
""", conn)

sales_180 = pd.read_sql_query("""
    SELECT product_id, warehouse_id, sale_date,
           SUM(quantity_sold) AS qty
    FROM sales
    WHERE sale_date BETWEEN ? AND ?
    GROUP BY product_id, warehouse_id, sale_date
""", conn, params=(start_180, REFERENCE_DATE.isoformat()))
sales_180["sale_date"] = pd.to_datetime(sales_180["sale_date"])

# Completiamo il calendario per ogni posizione e assegniamo zero
# alle giornate senza transazioni.
positions = stock_freni[["product_id", "warehouse_id"]].copy()
positions["_key"] = 1
days = pd.DataFrame({"sale_date": calendar_180, "_key": 1})
panel_180 = positions.merge(days, on="_key").drop(columns="_key")
panel_180 = panel_180.merge(
    sales_180,
    on=["product_id", "warehouse_id", "sale_date"],
    how="left"
)
panel_180["qty"] = panel_180["qty"].fillna(0).astype(int)

stats = panel_180.groupby(["product_id", "warehouse_id"])["qty"].agg(
    domanda_media="mean",
    dev_std=lambda s: s.std(ddof=0)
).reset_index()

rop = stock_freni.merge(stats, on=["product_id", "warehouse_id"])
rop["scorta_sicurezza"] = (
    Z * rop["dev_std"] * np.sqrt(rop["lead_time_giorni"])
)
rop["rop_calcolato"] = np.ceil(
    rop["domanda_media"] * rop["lead_time_giorni"]
    + rop["scorta_sicurezza"]
).astype(int)
rop["delta"] = rop["rop_calcolato"] - rop["reorder_point"]
rop = rop.sort_values("delta", ascending=False).reset_index(drop=True)
print(rop.head(10).to_string(index=False))
print("\nConfronto soglie:")
print(f"Posizioni Freni: {len(rop)}; delta positivo: {(rop.delta > 0).sum()}; "
      f"delta negativo: {(rop.delta < 0).sum()}; invariato: {(rop.delta == 0).sum()}")

plot_rop = rop.head(10).copy().reset_index(drop=True)
x = np.arange(len(plot_rop))
width = 0.38
fig, ax = plt.subplots(figsize=(12, 5))
ax.bar(x - width/2, plot_rop["reorder_point"], width, label="ROP attuale")
ax.bar(x + width/2, plot_rop["rop_calcolato"], width, label="ROP stimato")
ax.set_xticks(x)
ax.set_xticklabels([
    f"{r.product_id}-{r.warehouse_id}" for r in plot_rop.itertuples()
], rotation=45, ha="right")
ax.set_ylabel("Unità")
ax.set_title("Confronto tra punto di riordino attuale e stimato")
ax.legend()
plt.tight_layout()
plt.show()
plt.close()

Nel dataset ci sono 59 posizioni della categoria Freni: per 48 il punto di riordino stimato è più alto di quello attuale, per 11 è più basso e nessuna posizione resta invariata. Le dieci maggiori differenze positive sono:

SKU Magazzino Stock ROP attuale Lead time Domanda media/giorno Dev. standard Scorta sicurezza ROP stimato Delta
12 WH4 246 43 43 7,800 10,035 108,578 444 +401
90 WH2 462 22 40 7,317 9,171 95,709 389 +367
69 WH3 387 73 43 7,700 9,429 102,016 434 +361
12 WH1 414 61 43 6,417 9,013 97,524 374 +313
11 WH4 257 108 35 9,078 10,366 101,192 419 +311
11 WH2 354 27 35 6,939 9,538 93,103 336 +309
96 WH3 171 81 42 6,844 9,479 101,362 389 +308
11 WH1 227 35 35 6,961 9,804 95,701 340 +305
90 WH4 194 36 40 6,117 9,151 95,499 341 +305
69 WH4 59 94 43 6,889 9,293 100,538 397 +303

Le differenze elevate sono il segnale per approfondire, non ordini di acquisto già pronti. Per esempio, nella prima riga il modello stima un ROP di 444 unità contro 43 nel gestionale, ma la giacenza fisica è 246. Prima di cambiare la soglia occorrerebbe controllare se i dati di vendita sono affidabili, se il lead time di 43 giorni è corretto, se esistono ordini in transito e se i 180 giorni osservati rappresentano la domanda futura.

Il calendario completo evita un errore frequente: se si calcolasse la media solo sulle date in cui è avvenuta una vendita, i giorni senza transazioni sparirebbero e la media giornaliera risulterebbe riferita ai soli giorni attivi. La deviazione standard usa ddof=0, cioè tratta i 180 giorni osservati come l’intera popolazione della finestra considerata. Il punto stimato viene arrotondato per eccesso con ceil, perché una soglia frazionaria di unità non è utilizzabile come quantità intera.

La formula presuppone indipendenza e stabilità della domanda e un lead time non variabile. Trend, stagionalità, promozioni, domanda intermittente e variabilità dei fornitori richiedono metodi più adatti e una verifica retrospettiva. Anche la quantità venduta può essere una misura incompleta della domanda se si sono verificati stockout: le vendite osservate non registrano necessariamente le richieste non soddisfatte.

Mini quiz. Perché la deviazione standard della domanda durante il lead time cresce con [math]\sqrt{LT}[/math] e non con [math]LT[/math]? Se le domande giornaliere sono indipendenti e hanno varianza [math]\sigma^2[/math], la varianza della somma su L giorni è [math]L\sigma^2[/math]; la deviazione standard è quindi [math]\sigma\sqrt{L}[/math].

Pubblicità

Esercizio 6 — Pipeline giornaliera per copertura e alert

Dopo le analisi, il direttore operations vuole un report giornaliero. La pipeline calcola le vendite degli ultimi 90 giorni per posizione, ricostruisce le giornate senza transazioni, misura la domanda media e i giorni di copertura, separa le posizioni senza vendite recenti da quelle con copertura inferiore a 14 giorni e prepara un CSV.

La copertura è definita come giacenza divisa per domanda media giornaliera. Quando la domanda media è zero, il valore non viene calcolato: una divisione per zero non produce un numero utile e una posizione senza vendite non deve essere confusa con una posizione a copertura illimitata.

calendar_90 = pd.date_range(
    end=pd.Timestamp(REFERENCE_DATE), periods=90, freq="D"
)
start_90 = calendar_90.min().strftime("%Y-%m-%d")

base = pd.read_sql_query("""
    SELECT i.product_id, i.warehouse_id, p.category,
           i.stock_level, p.unit_cost, i.reorder_point
    FROM inventory AS i
    JOIN products AS p ON p.product_id = i.product_id
""", conn)

sales_90 = pd.read_sql_query("""
    SELECT product_id, warehouse_id, sale_date,
           SUM(quantity_sold) AS qty
    FROM sales
    WHERE sale_date BETWEEN ? AND ?
    GROUP BY product_id, warehouse_id, sale_date
""", conn, params=(start_90, REFERENCE_DATE.isoformat()))
sales_90["sale_date"] = pd.to_datetime(sales_90["sale_date"])

positions = base[["product_id", "warehouse_id"]].copy()
positions["_key"] = 1
days = pd.DataFrame({"sale_date": calendar_90, "_key": 1})
panel = positions.merge(days, on="_key").drop(columns="_key")
panel = panel.merge(
    sales_90,
    on=["product_id", "warehouse_id", "sale_date"],
    how="left"
)
panel["qty"] = panel["qty"].fillna(0).astype(int)
panel = panel.sort_values(["product_id", "warehouse_id", "sale_date"])
panel["ma7"] = panel.groupby(
    ["product_id", "warehouse_id"]
)["qty"].transform(
    lambda s: s.rolling(7, min_periods=1).mean()
)

agg = panel.groupby(["product_id", "warehouse_id"]).agg(
    domanda_media_90g=("qty", "mean"),
    vendite_90g=("qty", "sum"),
    ma7_ultima=("ma7", "last")
).reset_index()
report = base.merge(agg, on=["product_id", "warehouse_id"], how="left")
report["giorni_copertura"] = np.where(
    report["domanda_media_90g"] > 0,
    report["stock_level"] / report["domanda_media_90g"],
    np.nan
)
report["stato"] = np.select(
    [report["vendite_90g"].eq(0), report["giorni_copertura"].lt(14)],
    ["Nessuna vendita negli ultimi 90 giorni",
     "Copertura inferiore a 14 giorni"],
    default="Copertura adeguata"
)
report["valore_stock"] = (
    report["stock_level"] * report["unit_cost"]
).round(2)
critici = report[report["stato"] != "Copertura adeguata"].copy()
critici = critici.sort_values(
    ["stato", "giorni_copertura"], na_position="last"
)

from pathlib import Path
OUT_DIR = Path.cwd()
report_path = OUT_DIR / "sku_critici.csv"
critici.to_csv(report_path, index=False)
print("Posizioni da verificare:", len(critici))
print("Report salvato in:", report_path)
print("\nComposizione per stato:")
print(critici["stato"].value_counts().to_string())
print("\nPrime 15 posizioni critiche:")
print(critici[[
    "product_id", "warehouse_id", "category", "stock_level",
    "domanda_media_90g", "giorni_copertura", "stato"
]].head(15).to_string(index=False))

# Le cinque posizioni con copertura più bassa, tra quelle con domanda positiva.
top5 = report[report["domanda_media_90g"] > 0].nsmallest(
    5, "giorni_copertura"
)
print("\nCinque coperture più basse:")
print(top5[[
    "product_id", "warehouse_id", "stock_level",
    "domanda_media_90g", "giorni_copertura"
]].to_string(index=False))

if not top5.empty:
    fig, axes = plt.subplots(
        len(top5), 1, figsize=(11, 2.7 * len(top5)),
        sharex=True, squeeze=False
    )
    for ax, row in zip(axes[:, 0], top5.itertuples()):
        serie = panel[
            (panel["product_id"] == row.product_id)
            & (panel["warehouse_id"] == row.warehouse_id)
        ]
        ax.bar(serie["sale_date"], serie["qty"],
               width=0.8, label="Vendite giornaliere")
        ax.plot(serie["sale_date"], serie["ma7"],
                color="darkorange", label="Media mobile 7 giorni")
        ax.set_title(
            f"SKU {row.product_id} — {row.warehouse_id} — "
            f"copertura {row.giorni_copertura:.2f} giorni"
        )
        ax.set_ylabel("Unità")
        ax.legend(loc="upper left")
    axes[-1, 0].set_xlabel("Data")
    fig.suptitle("Cinque posizioni con copertura più bassa", y=1.002)
    plt.tight_layout()
    plt.show()
    plt.close()

L’esecuzione segnala 85 posizioni su 280. Di queste, 37 hanno domanda positiva e copertura inferiore a 14 giorni; 48 non hanno registrato vendite negli ultimi 90 giorni. Nel dataset simulato tutte le 48 posizioni senza vendite hanno una giacenza positiva, ma in dati reali la pipeline dovrebbe controllare e riportare separatamente anche le posizioni a stock zero.

Forse potrebbe interessarti anche:  Weak Signals Detection con Python: anomalie multivariate, topic emergenti e priorità decisionali

Le prime 15 righe sono ordinate per stato e, all’interno delle posizioni con copertura, per copertura crescente:

SKU Magazzino Categoria Stock Domanda media/giorno Copertura (giorni) Stato
99 WH2 Filtri 1 7,822 0,13 Copertura inferiore a 14 giorni
37 WH3 Accessori 2 7,133 0,28 Copertura inferiore a 14 giorni
33 WH1 Lubrificanti 7 6,000 1,17 Copertura inferiore a 14 giorni
36 WH3 Accessori 7 6,000 1,17 Copertura inferiore a 14 giorni
2 WH1 Lubrificanti 8 5,933 1,35 Copertura inferiore a 14 giorni
83 WH3 Freni 10 6,622 1,51 Copertura inferiore a 14 giorni
81 WH1 Filtri 14 6,478 2,16 Copertura inferiore a 14 giorni
45 WH4 Elettrico 20 8,444 2,37 Copertura inferiore a 14 giorni
29 WH2 Lubrificanti 16 5,967 2,68 Copertura inferiore a 14 giorni
94 WH3 Lubrificanti 17 6,100 2,79 Copertura inferiore a 14 giorni
70 WH3 Lubrificanti 30 7,956 3,77 Copertura inferiore a 14 giorni
79 WH1 Freni 23 6,033 3,81 Copertura inferiore a 14 giorni
75 WH1 Accessori 30 6,778 4,43 Copertura inferiore a 14 giorni
62 WH4 Elettrico 37 8,078 4,58 Copertura inferiore a 14 giorni
26 WH3 Accessori 43 8,678 4,96 Copertura inferiore a 14 giorni

La copertura più bassa è quella dello SKU 99 nel magazzino WH2: una sola unità disponibile a fronte di una domanda media di circa 7,82 unità al giorno. Il valore teorico è 0,13 giorni, quindi la giacenza è nettamente insufficiente se la domanda futura assomiglia alla media della finestra. La lettura resta indicativa: medie giornaliere frazionarie non significano che vengano vendute frazioni di ricambio, ma riassumono il ritmo osservato nel tempo.

Le cinque posizioni usate nel grafico sono:

SKU Magazzino Stock Domanda media/giorno Copertura (giorni)
99 WH2 1 7,822 0,13
37 WH3 2 7,133 0,28
33 WH1 7 6,000 1,17
36 WH3 7 6,000 1,17
2 WH1 8 5,933 1,35

Il grafico mostra le quantità vendute giorno per giorno e la media mobile di sette osservazioni consecutive. Poiché il calendario è completo e i giorni vuoti hanno valore zero, la finestra corrisponde a sette giorni di calendario. La media mobile attenua oscillazioni ravvicinate, ma non è una previsione: non incorpora trend, stagionalità o eventi futuri.

ma7_ultima viene calcolata e conservata nell’aggregato per rendere disponibile un indicatore di domanda recente; la classificazione degli alert in questo esempio usa invece la media dei 90 giorni. Per un processo operativo si potrebbe confrontare la media mobile con la media di periodo, ma sarebbe una regola distinta da definire e validare con chi gestisce il riordino.

Il CSV sku_critici.csv viene salvato nella directory corrente. In Colab, dopo aver controllato il percorso, si può scaricare con from google.colab import files e files.download(str(report_path)); quella funzione non è disponibile in un normale notebook locale. La directory /content è specifica di Colab e non va data per scontata negli altri ambienti.

Mini quiz. Perché non calcoliamo una media mobile sulle sole date con vendite? Perché i giorni senza transazioni sparirebbero e la media descriverebbe solo i giorni attivi, non il ritmo medio sul calendario.

SQL e Pandas: dove conviene fare i calcoli

Gli esercizi mostrano che SQL e Pandas risolvono problemi collegati, ma non identici. SQL è comodo per selezionare, collegare e aggregare tabelle mantenendo le elaborazioni vicino ai dati. Pandas è particolarmente pratico per costruire calendari completi, trattare serie temporali e preparare tabelle per grafici o successive elaborazioni statistiche.

Una finestra SQL definita con ROWS BETWEEN 6 PRECEDING AND CURRENT ROW considera sette righe, non necessariamente sette giorni. Se le date mancanti non vengono ricostruite, le sette righe possono coprire un periodo più lungo. In Pandas abbiamo prima completato il calendario, quindi rolling(7) opera su sette osservazioni giornaliere consecutive. La scelta dello strumento dipende dalla forma dei dati e da dove conviene eseguire l’operazione, non da una regola per cui uno dei due sarebbe sempre migliore.

SQLite è adatto a esercizi, prototipi e database leggeri, ma non è la scelta automatica per qualsiasi applicazione aziendale. Un database client-server può essere più indicato quando servono accessi concorrenti, gestione centralizzata o integrazione con più applicazioni. Anche Pandas ha limiti: i DataFrame e le copie intermedie occupano memoria, quindi la dimensione dei dati e il tipo di elaborazione vanno considerati prima di caricare tutto in RAM.

Risposte ai mini quiz

Pubblicità

Esercizio 1. COUNT(*) conta tutte le posizioni; COUNT(DISTINCT product_id) conta gli SKU distinti. Tre magazzini con lo stesso prodotto sotto soglia danno tre posizioni ma un solo SKU.

Esercizio 2. La condizione riferita al numero di prodotti per categoria dipende da un’aggregazione, quindi si applica dopo GROUP BY con HAVING. Il filtro sul costo degli Accessori riguarda invece le righe e si applica in WHERE.

Esercizio 3. NOT EXISTS verifica l’assenza di vendite nella finestra per la specifica coppia prodotto-magazzino. È anche più diretto di NOT IN in presenza di possibili valori NULL nella sottoquery.

Esercizio 4. SUM(...) OVER () produce il totale generale da usare come denominatore. SUM(...) OVER (ORDER BY ... ROWS ...) produce il cumulato da usare come numeratore per la percentuale progressiva.

Esercizio 5. Con domande giornaliere indipendenti, la varianza della somma su L giorni è [math]L\sigma^2[/math]; la deviazione standard è pertanto [math]\sigma\sqrt{L}[/math]. La scorta di sicurezza è proporzionale alla deviazione standard, non alla varianza.

Esercizio 6. Se si eliminano dal calendario i giorni senza vendite, la media non misura la domanda giornaliera sull’intero periodo. Occorre reinserire i giorni mancanti con quantità zero prima di calcolare medie e finestre mobili.

Dalla query a un processo utile

RicambiExpress è un caso didattico, ma il percorso mette in evidenza un passaggio che vale anche fuori dall’esercizio: un indicatore acquista significato solo quando si chiarisce a quale domanda aziendale risponde e quali informazioni lascia fuori. Una posizione sotto soglia non equivale automaticamente a un ordine urgente; una scorta di alto valore non è per forza eccessiva; l’assenza di vendite recenti non dimostra che il prodotto sia inutile.

Per portare una pipeline simile in produzione servirebbero controlli sulla qualità dei dati, gestione degli errori, conservazione dello storico delle esecuzioni, informazioni sugli ordini in transito e verifica dei tempi reali dei fornitori. Sarebbe inoltre necessario confrontare le segnalazioni con ciò che accade nei periodi successivi, misurando se le soglie riducono gli stockout senza far crescere troppo il capitale immobilizzato.

SQLite e Python permettono di costruire un prototipo comprensibile e ripetibile. La parte più importante non è avere una query in più, ma tenere insieme quantità, domanda, variabilità, valore e contesto operativo prima di suggerire una decisione.

📚 SQLite, Python e Pandas per l’analisi e la gestione dei dati

Dalla gestione dei database relazionali all’elaborazione delle serie temporali, fino all’automazione dei processi di analisi: ecco altre guide pratiche per approfondire gli strumenti utilizzati negli esercizi.

👉Python per Data Engineering: 3 esercizi reali su Excel, API e automazione aziendale

👉SQLite e Python: guida pratica con 3 esercizi su database, query e transazioni

👉SQLite e Python: guida pratica dal JOIN ai Trigger

👉Pandas oltre le basi: Merge insidiosi e analisi di serie storiche per il Business

👉Statistiche essenziali con Pandas Series: guida pratica ed esercizi

👉Guida pratica: 6 esercizi Python con Pandas e SQLite per l’analisi dati

Pubblicità