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;