Il caso d’esempio che voglio affrontare in questo articolo è molto comune. Chi di voi ha lavorato su un prodotto per le movimentazioni di magazzino si è sicuramente dovuto scontrare con la necessità di calcolare i totali parziali di un singolo articolo ad una certa data.
Solitamente abbiamo una tabella che registra data ed ora dell’operazione e una specifica quantità movimentata di un dato articolo; positiva se va ad incrementare le nostre scorte, negativa viceversa.
Non vorrei entrare troppo nel merito di come potremmo disegnare in un possibile modello dati una tabella di questo tipo, esula completamente dallo scopo di quest’articolo.
Vorrei piuttosto fornire un paio di spunti su come calcolare il totale della nostra merce con l’avanzare dei giorni.
Il nostro capo ci chiederà: “Sai dirmi il 14/05/2022 quante scorte avevamo dell’articolo 1?”
Bene, dopo aver completato questa breve lettura saprete rispondere.
Tabella dei movimenti
CREATE TABLE Movimenti (
Id BIGINT IDENTITY (1, 1) PRIMARY KEY,
IdArticolo INT NOT NULL,
DataMovimento DATETIME NOT NULL,
Quantita int -- positiva=aggiunta, negativa=rimozione
)
Come sottolineavo prima, ho ridotto all’osso la struttura della tabella proprio per concentrarci il più possibile sullo scopo dell’articolo.
INSERT INTO Movimenti (IdArticolo, DataMovimento, Quantita)
VALUES
(1, '2022-05-14T15:33:00', +1000),
(1, '2022-05-18T10:12:00', -20),
(1, '2022-05-18T13:04:38', -10),
(1, '2022-05-29T19:17:18', -70),
(1, '2022-06-15T10:01:00', +100);
Con quest’ultima istruzione inseriamo cinque righe di test per simulare la movimentazione dell’articolo 1 in momenti differenti. (Se hai bisogno di qualche hint su come fare un’insert a DB leggi questo articolo)
Running total sulla data
Gli strumenti del linguaggio SQL sono stati potenziati nel tempo rendendo il compito in oggetto una mera questione di utilizzo delle funzionalità offerte dall’engine.
Infatti per ottenere il totale dell’articolo a magazzino man mano che si susseguono i vari movimenti di magazzino possiamo utilizzare una funzione di partizione sull’aggregazione somma.
SELECT
IdArticolo,
DataMovimento,
Quantita,
SUM(Quantita) OVER (PARTITION BY IdArticolo ORDER BY DataMovimento ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As QuantitaAllaData
FROM
Movimenti
ORDER BY IdArticolo, DataMovimento;
La clausola UNBOUNDED PRECEDING, come spiegato nella documentazione, si applica solo a partire dalla versione di Sql Server 2012.
Nella colonna “QuantitaAllaData” stiamo chiedendo a SQL di calcolare la somma di tutte le “Quantita” a partire dalla riga zero a quella corrente. Così facendo otteniamo:
Running total sul giorno
A questo punto vediamo che il dato parziale sulla singola utile restituisce effettivamente quello che vogliamo, però potrebbe non essere così utile avere un dettaglio preciso sul singolo movimento.
Possiamo decidere di appiattire prima i risultati sul giorno per poi applicare la funzione di partizionamento vista sopra:
;WITH CTE AS (
SELECT
IdArticolo,
CAST (DataMovimento AS DATE) as Giorno,
SUM(Quantita) Quantita
FROM
Movimenti
GROUP BY IdArticolo, CAST (DataMovimento AS DATE)
)
SELECT
IdArticolo,
Giorno,
Quantita,
SUM(Quantita) OVER (PARTITION BY IdArticolo ORDER BY Giorno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As QuantitaDelGiorno
FROM CTE
ORDER BY IdArticolo, Giorno;
Vediamo nell’immagine sopra che ora i movimenti del 18/05/2022 non si vedono in maniera distinta, vediamo invece un unico parziale corrispondente al “970”, ossia l’ultimo valore della giornata.
Possiamo applicare lo stesso concetto al mese in questo modo:
;WITH CTE AS (
SELECT
IdArticolo,
DATEPART(YEAR, DataMovimento) as Anno,
DATEPART(MONTH, DataMovimento) as Mese,
SUM(Quantita) Quantita
FROM
Movimenti
GROUP BY IdArticolo, DATEPART(YEAR, DataMovimento), DATEPART(MONTH, DataMovimento)
)
SELECT
IdArticolo,
Anno,
Mese,
Quantita,
SUM(Quantita) OVER (PARTITION BY IdArticolo ORDER BY Anno, Mese ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As QuantitaDelMese
FROM CTE
ORDER BY IdArticolo, Anno, Mese;
Risultato:
Abbiamo ottenuto il totale parziale sul mese di Maggio e poi quello di Giugno. Infatti, nonostante la giacenza della merce in Maggio partisse da 1000, a causa dei movimenti del 18 e 29 la “Quantità” totale scende a 900.
Torna nuovamente a 1000 nel mese di Giugno grazie al movimento del 15/06.
Questi due ultimi esempi potrebbero essere ottenuti anche in altri modi, mi piacerebbe sapere se ne conoscete di più performanti.