Corso MariaDB / Totali progressivi e confronto tra righe con LAG e LEAD
SUM come funzione finestra calcola un totale progressivo senza riunire le righe, mentre LAG e LEAD leggono valori della riga precedente e successiva nell'ordine scelto. Specificare un ordine deterministico e l'intervallo di accumulo desiderato, così gli orari uguali non rendono ambiguo il calcolo.
Definire sequenza temporale e unità da accumulare
Un totale progressivo per ordine è diverso da uno per giorno. Questo esempio somma prima quantity × unit_price al momento dell'acquisto per ogni ordine pagato e poi accumula per data, ora e ID ordine. Spedizioni, annullamenti e rimborsi richiedono regole separate: qui la misura comprende soltanto le merci pagate definite dalla query.
La guida alle funzioni di classifica descrive calcoli che conservano le righe; la guida alle differenze tra date distingue una riga precedente da un intervallo di calendario. Senza un offset aggiuntivo, LAG legge una riga indietro e LEAD una riga avanti nell'ordine della finestra.
| Calcolo | Ambito dell'esempio | Comportamento al limite |
|---|---|---|
| SUM con finestra | Dalla prima riga a quella corrente | Il primo totale equivale al proprio importo |
| LAG | Riga precedente nell'ordine | NULL se non esiste una riga precedente |
| LEAD | Riga successiva nell'ordine | NULL se non esiste una riga successiva |
La documentazione MariaDB di LAG e la panoramica delle funzioni finestra spiegano sintassi e ambito dei calcoli.
Preparare una sequenza deterministica di ordini
Collegarsi al server di esercitazione come course_user e selezionare shopcourse. La guida al client e ai file SQL UTF-8 illustra salvataggio ed esecuzione con SOURCE C:/mariadb-lab/lesson.sql;. Sostituire il percorso con quello effettivo ed eseguire SQL nel client MariaDB.
Prima di creare c33_customers, c33_products, c33_orders e c33_items, controllare SELECT VERSION(), DATABASE(); e SHOW TABLES;. Se i nomi esistono già, esaminarne definizioni e dati. Usare soltanto oggetti di prova eliminabili e conservare le tabelle applicative. Il blocco prepara tutte le tabelle necessarie per eseguire l'esempio in modo autonomo.
USE shopcourse;
CREATE TABLE c33_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c33_order_customer FOREIGN KEY (customer_id)
REFERENCES c33_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c33_item_order FOREIGN KEY (order_id)
REFERENCES c33_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c33_item_product FOREIGN KEY (product_id)
REFERENCES c33_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c33_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c33_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c33_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c33_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
Il codice mantiene nomi e città coreani della fonte. I prodotti 1–4 sono quaderno, penna, tazza e lampada. I clienti 1 e 3 appartengono a Seoul, il cliente 2 a Busan e il cliente 4 a Daejeon. Conservare i testi UTF-8 e confrontare gli ID per verificare i risultati.
Gli ordini pagati, in sequenza, sono 101 del 10 gennaio, 102 del 20 gennaio e 103 del 5 febbraio. order_id costituisce un criterio secondario univoco quando gli orari coincidono. Questo uso non significa che gli ID riflettano sempre l'ordine cronologico in ogni applicazione.
Usare un intervallo ROWS esplicito per l'accumulo
WITH order_totals AS (
SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
)
SELECT order_id,goods_total,
SUM(goods_total) OVER(ORDER BY ordered_at,order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
LAG(goods_total) OVER(ORDER BY ordered_at,order_id) AS previous_total,
LEAD(goods_total) OVER(ORDER BY ordered_at,order_id) AS next_total
FROM order_totals ORDER BY ordered_at,order_id;
| Ordine | Importo | Totale progressivo | Precedente | Successivo |
|---|---|---|---|---|
| 101 | 11500.00 | 11500.00 | NULL | 8000.00 |
| 102 | 8000.00 | 19500.00 | 11500.00 | 19500.00 |
| 103 | 19500.00 | 39000.00 | 8000.00 | NULL |
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW include nel totale le righe dalla prima a quella corrente. Se l'obiettivo è accumulare riga per riga, dichiarare l'intervallo invece di affidarsi a uno implicito. ROWS e RANGE possono trattare diversamente valori di ordinamento uguali; il criterio secondario univoco rende deterministico l'ordine di questo esempio.
La query autonoma seguente confronta orari uguali senza modificare tabelle. Gli ordini 501 e 502 hanno lo stesso orario ma importi diversi.
WITH sample AS (
SELECT 501 AS order_id,CAST('2026-01-10 10:00:00' AS DATETIME) AS ordered_at,10 AS amount
UNION ALL SELECT 502,'2026-01-10 10:00:00',20
UNION ALL SELECT 503,'2026-01-11 10:00:00',5
)
SELECT order_id,amount,
SUM(amount) OVER (ORDER BY ordered_at,order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_unique,
SUM(amount) OVER (ORDER BY ordered_at,order_id
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_unique,
SUM(amount) OVER (ORDER BY ordered_at
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_time
FROM sample ORDER BY ordered_at,order_id;
| order_id | amount | rows_unique | range_unique | range_time |
|---|---|---|---|---|
| 501 | 10 | 10 | 10 | 30 |
| 502 | 20 | 30 | 30 | 30 |
| 503 | 5 | 35 | 35 | 35 |
rows_unique e range_unique usano lo stesso ordine univoco orario–ID e differiscono soltanto per ROWS e RANGE: entrambi producono 10, 30, 35. range_time elimina solo l'ID dall'ordinamento RANGE: le due righe con lo stesso orario sono considerate insieme e producono 30, 30, 35. Con ROWS, un orario senza criterio univoco aggiuntivo non stabilisce l'ordine tra righe a pari orario.
Interpretare righe vicine e date senza osservazioni
LAG indica la riga precedente nell'ordine scelto, che non corrisponde necessariamente a ieri. La funzione non crea righe per date senza ordini. Per misurare variazioni giornaliere occorrono un'aggregazione per giorno e una regola esplicita per i giorni privi di dati.
Le differenze rispetto all'importo precedente dell'esempio sono NULL, -3500 e 11500. Il primo risultato è NULL perché manca un predecessore. Sostituirlo con zero definisce una base di confronto: non è una semplice scelta estetica. L'assenza di un'osservazione è diversa da uno zero effettivamente misurato. Tra gli ordini dell'esempio trascorrono 10 e 16 giorni, non un giorno.
Scegliere se filtrare prima o dopo l'accumulo
Filtrare febbraio prima del calcolo finestra fa iniziare il totale da febbraio. Per visualizzare febbraio includendo nel totale anche i mesi precedenti, completare prima il calcolo in una CTE, cioè un'espressione di tabella comune, e applicare il filtro di visualizzazione nella query esterna. Entrambe le query possono essere corrette, ma hanno un punto di partenza diverso.
Se non si aggregano prima i dettagli, il totale procede per riga articolo anziché per ordine. Definire la misura specificando unità delle righe, stati ammessi, chiavi di ordinamento, intervallo e momento del filtro.
La prima query accumula tutti gli ordini pagati e poi mostra febbraio. La seconda sposta lo stesso filtro di data prima del calcolo finestra. Entrambe riutilizzano le tabelle c33 senza modificarne i dati.
WITH order_totals AS (
SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
), running AS (
SELECT order_id,ordered_at,goods_total,
SUM(goods_total) OVER (ORDER BY ordered_at,order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM order_totals
)
SELECT order_id,goods_total,running_total FROM running
WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01'
ORDER BY ordered_at,order_id;
WITH order_totals AS (
SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
)
SELECT order_id,goods_total,
SUM(goods_total) OVER (ORDER BY ordered_at,order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM order_totals
WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01'
ORDER BY ordered_at,order_id;
| Posizione del filtro | order_id | goods_total | running_total |
|---|---|---|---|
| Dopo l'accumulo | 103 | 19500.00 | 39000.00 |
| Prima dell'accumulo | 103 | 19500.00 | 19500.00 |
Entrambi i risultati mostrano l'ordine 103, ma il periodo accumulato cambia. Stabilire se il rapporto deve includere la storia precedente oppure ricominciare dal mese visualizzato.
Verificare primo valore, ultimo valore e parità di orario
Riconciliare a mano i totali progressivi: 11500, 19500 e 39000. Controllare NULL nel primo LAG e nell'ultimo LEAD. In una prova separata, assegnare lo stesso orario a due ordini e verificare che il criterio secondario ne determini la posizione. Dichiarare l'ORDER BY finale senza affidarsi alla sequenza incidentale di un indice.
Prima di ripetere l'esercizio, controllare database selezionato, SHOW CREATE TABLE e contenuto delle quattro tabelle, salvando ciò che va conservato. Eliminare soltanto gli oggetti c33_ di questo articolo, partendo dalle tabelle figlie. DROP TABLE elimina struttura e righe e non viene annullato da un normale ROLLBACK: separare la pulizia dalle prove sulle transazioni.
DROP TABLE shopcourse.c33_items;
DROP TABLE shopcourse.c33_orders;
DROP TABLE shopcourse.c33_products;
DROP TABLE shopcourse.c33_customers;




