Corso MariaDB / Riassumere le righe con COUNT, SUM, AVG, MIN e MAX

COUNT, SUM, AVG, MIN e MAX riassumono righe come conteggi, totali, medie e valori estremi. COUNT(*) conta le righe, mentre COUNT(colonna) conta i valori diversi da NULL: definire unità, denominatore e politica per i dati mancanti prima di interpretare un'aggregazione.

Definire che cosa rappresentano una riga e una misura

Numero di tipi di prodotto, unità complessive in magazzino, scorte medie per prodotto e valore totale delle scorte misurano cose diverse. Verificare se ogni prodotto compaia una volta o più volte e se un numero rappresenti unità o denaro. Una sintassi di aggregazione valida può comunque calcolare una misura aziendale sbagliata. Unendo tabelle, la moltiplicazione delle righe del JOIN può anche duplicare gli importi prima dell'aggregazione.

Misura Espressione Unità
Prodotti COUNT(*) Righe
Note con valore non NULL COUNT(note) Valori diversi da NULL
Scorte totali SUM(stock) Unità di prodotto
Scorte medie AVG(stock) Unità per prodotto con quantità nota
Valore delle scorte SUM(price*stock) Denaro

Le definizioni ufficiali sono disponibili nei riferimenti AVG, COUNT e SUM.

Preparare note e scorte con stati distinti

Collegarsi al server di studio come course_user e selezionare shopcourse. La guida al terminale e ai file SQL UTF-8 spiega come salvare il codice ed eseguirlo con SOURCE C:/mariadb-lab/lesson.sql, sostituendo il percorso con quello reale. Eseguire SQL nel client MariaDB, non al prompt del sistema operativo.

Controllare SELECT VERSION(), DATABASE() e SHOW TABLES prima di creare products_22_lab. Il nome appartiene soltanto a questo esercizio. Se esiste già, verificarne definizione e righe; eliminarlo solo dopo aver confermato che sia un oggetto di esercitazione eliminabile. Non sostituire tabelle applicative esistenti.

USE shopcourse;
CREATE TABLE products_22_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p22_price CHECK (price >= 0),
  CONSTRAINT ck_p22_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_22_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

SQL conserva le stringhe coreane originali per verificare UTF-8 in modo coerente. Gli ID da 1 a 6 rappresentano quaderno, penna, tazza, lampada da tavolo, cartellina e cavo. Le categorie significano cancelleria, articoli per la casa ed elettronica. Inserire le stringhe come mostrate, confrontare i risultati tramite gli ID e salvare il file in UTF-8.

Due delle sei note sono NULL e due sono stringhe vuote. I prodotti con scorte zero esistono comunque come righe e rientrano in COUNT(*). La guida alla gestione di NULL spiega perché i valori mancanti possono non essere inclusi in un conteggio basato sui valori.

Confrontare tutte le aggregazioni in un solo risultato

SELECT COUNT(*) AS all_rows,COUNT(note) AS known_notes,
 SUM(stock) AS stock_total,AVG(stock) AS stock_average,
 MIN(price) AS lowest_price,MAX(price) AS highest_price,
 SUM(price*stock) AS inventory_total
FROM products_22_lab;
Output Risultato atteso Interpretazione
all_rows 6 Tutte le righe dei prodotti
known_notes 4 Le stringhe vuote sono valori noti
stock_total 87 20 + 0 + 12 + 5 + 50 + 0
stock_average 14.5 Il numero di decimali visualizzati può variare
lowest_price / highest_price 1500.00 / 25000.00 Prezzi minimo e massimo
inventory_total 366000.00 Somma di prezzo × scorte per ogni riga

AVG(stock) divide la somma delle quantità note per il numero di quantità diverse da NULL. Zero contribuisce sia al denominatore sia alla somma; NULL non rappresenta uno zero confermato. Conservare questa distinzione quando si interpretano le medie.

In un esempio separato, gli input 0, 10 e NULL occupano tre righe ma contengono soltanto due numeri noti. La somma è 10 e la media è 5, non 10 diviso 3. Sostituire prima NULL con zero modifica il significato della misura.

Controllare le aggregazioni quando nessuna riga corrisponde

SELECT COUNT(*),SUM(stock),AVG(stock),COALESCE(SUM(stock),0)
FROM products_22_lab WHERE price < 0;

I quattro risultati sono 0, NULL, NULL e 0. COUNT segnala zero righe, mentre SUM e AVG restituiscono NULL per questo input vuoto. Mostrare zero come alternativa per un totale vuoto può essere appropriato; registrare una media non osservata come uno zero misurato è una decisione diversa.

WHERE price < 0 non seleziona prodotti perché i prezzi dell'esempio sono non negativi. Se cambia la condizione, controllare le righe effettivamente incluse nell'aggregazione senza presumere che un risultato zero e l'assenza di dati abbiano sempre lo stesso significato.

Applicare l'aritmetica per riga prima dell'aggregazione voluta

SUM(price*stock) calcola prima il valore di ogni prodotto e poi somma gli importi. SUM(price)*SUM(stock) moltiplica due totali non abbinati e misura qualcosa di diverso. Esaminare unità e livello di dettaglio del calcolo prima di attribuire un risultato troppo grande alla precisione decimale.

SELECT product_id,price,stock,price*stock AS row_value
FROM products_22_lab ORDER BY product_id;

I valori delle righe, nell'ordine, sono 70000.00, 0.00, 96000.00, 125000.00, 75000.00 e 0.00. La somma è 366000.00, quindi si può riconciliare il risultato aggregato con i dati originali.

MIN(price) e MAX(price) restituiscono prezzi e non selezionano automaticamente i nomi dei prodotti corrispondenti. Più prodotti possono condividere lo stesso estremo: qui gli ID 2 e 5 hanno entrambi il prezzo minimo 1500.00. Se occorre un elenco di prodotti, definire un filtro o un ordinamento. Non associare un nome non aggregato arbitrario a un risultato aggregato.

Riconciliare le righe originali e controllare gli input duplicati

Per pochi dati, leggere ogni chiave primaria e ricontrollare le somme. Verificare che i sei prodotti iniziali non siano stati duplicati, che NULL non sia stato convertito silenziosamente in zero e che un JOIN non abbia moltiplicato le righe prima della somma. Totali elevati richiedono anche un tipo di risultato e un intervallo numerico adatti nell'applicazione.

Prima di ripetere, controllare database selezionato, definizione effettiva della tabella e righe memorizzate, salvando ciò che va conservato. Eliminare solo products_22_lab di questo articolo. DROP TABLE rimuove definizione e righe e non viene annullato da un normale ROLLBACK: tenere la pulizia separata dagli esperimenti sulle transazioni.

DROP TABLE shopcourse.products_22_lab;