Corso MariaDB / Classifiche con ROW_NUMBER, RANK e DENSE_RANK

Le funzioni finestra aggiungono calcoli a un insieme di righe conservando le righe di partenza. ROW_NUMBER assegna un numero diverso a ogni riga, mentre RANK e DENSE_RANK possono assegnare la stessa posizione a valori uguali: la scelta dipende da come il rapporto deve trattare gli ex aequo.

Distinguere numero di riga e posizione condivisa

Due clienti con lo stesso totale di acquisti possono richiedere numeri di visualizzazione diversi, ma la stessa posizione in classifica. Calcolare prima un totale per cliente e poi classificare quelle righe. Cambiare soltanto il nome della funzione non corregge un insieme iniziale composto da righe di dettaglio anziché da clienti. La guida a WITH e alle CTE, ossia le espressioni di tabella comuni, spiega come separare aggregazione e classifica.

Funzione Valori uguali Sequenza di esempio
ROW_NUMBER Un numero diverso per ogni riga 1, 2, 3, 4
RANK Stessa posizione, con salti successivi 1, 1, 3, 3
DENSE_RANK Stessa posizione, senza salti 1, 1, 2, 2

Una funzione finestra, o window function, usa OVER per definire l'insieme e l'ordine del calcolo. La documentazione MariaDB di ROW_NUMBER confronta le tre funzioni; la panoramica delle funzioni finestra ne descrive il funzionamento rispetto alle aggregazioni che raggruppano le righe.

Preparare clienti con totali volutamente uguali

Collegarsi al server di esercitazione come course_user e selezionare shopcourse. La guida al client e ai file SQL UTF-8 illustra come salvare il codice e avviarlo con SOURCE C:/mariadb-lab/lesson.sql;, sostituendo il percorso con quello reale. Eseguire SQL nel client MariaDB.

Prima di creare c32_customers, c32_products, c32_orders e c32_items, controllare SELECT VERSION(), DATABASE(); e SHOW TABLES;. Se i nomi esistono, esaminarne prima definizioni e dati. Usare soltanto oggetti di prova eliminabili e non sostituire tabelle applicative. Il blocco seguente prepara l'intero esempio.

USE shopcourse;
CREATE TABLE c32_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 c32_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 c32_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_c32_order_customer FOREIGN KEY (customer_id)
  REFERENCES c32_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_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_c32_item_order FOREIGN KEY (order_id)
  REFERENCES c32_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c32_item_product FOREIGN KEY (product_id)
  REFERENCES c32_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c32_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c32_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c32_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 c32_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 conserva 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 in UTF-8 e confrontare gli ID per riprodurre i risultati.

I clienti 1 e 2 hanno entrambi merci pagate per 19500.00. Il cliente 3 ha solo un ordine annullato e il cliente 4 nessun ordine: il rapporto attribuisce a entrambi un totale pagato pari a zero. Includere chi non ha acquistato ed escludere gli importi annullati sono scelte esplicite del rapporto.

Aggregare i totali e confrontare le tre funzioni

WITH customer_totals AS (
 SELECT c.customer_id,c.customer_name,
 COALESCE(SUM(CASE WHEN o.status='paid'
  THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
 FROM c32_customers c LEFT JOIN c32_orders o
  ON o.customer_id=c.customer_id
 LEFT JOIN c32_items i ON i.order_id=o.order_id
 GROUP BY c.customer_id,c.customer_name
)
SELECT customer_id,customer_name,goods_total,
 ROW_NUMBER() OVER(ORDER BY goods_total DESC,customer_id ASC) AS row_no,
 RANK() OVER(ORDER BY goods_total DESC) AS amount_rank,
 DENSE_RANK() OVER(ORDER BY goods_total DESC) AS dense_amount_rank
FROM customer_totals ORDER BY goods_total DESC,customer_id ASC;
Cliente Totale row_no amount_rank dense_amount_rank
1 19500.00 1 1 1
2 19500.00 2 1 1
3 0.00 3 3 2
4 0.00 4 3 2

ROW_NUMBER usa customer_id come criterio univoco per risolvere la parità, rendendo riproducibili i numeri assegnati. Le altre due funzioni ordinano soltanto per importo, così totali uguali conservano la stessa posizione. Aggiungere l'ID univoco anche a RANK e DENSE_RANK renderebbe diverse le combinazioni di ordinamento ed eliminerebbe gli ex aequo desiderati.

RANK passa a 3 dopo due clienti al primo posto. DENSE_RANK passa invece al secondo valore distinto, cioè 2. Sono due significati diversi della classifica: scegliere quello previsto dal rapporto, senza affidarsi soltanto all'aspetto della sequenza.

Separare ordine di calcolo, visualizzazione e partizioni

ORDER BY dentro OVER stabilisce l'ordine usato dalla funzione, mentre ORDER BY della SELECT finale stabilisce quello delle righe visualizzate. Specificare entrambi quando entrambi sono necessari. L'ordine della finestra, da solo, non garantisce l'ordine finale del risultato.

PARTITION BY suddivide le righe in gruppi nei quali il calcolo ricomincia. Una numerazione per città richiede city nell'insieme aggregato e PARTITION BY city nella finestra. Diversamente da GROUP BY, la partizione non riduce da sola i clienti a un'unica riga per città.

La query completa seguente riutilizza le tabelle c32, ricomincia la numerazione in ogni città e visualizza il risultato per ID cliente.

WITH customer_totals AS (
SELECT c.customer_id, c.customer_name, c.city,
       COALESCE(SUM(CASE WHEN o.status='paid'
         THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c
LEFT JOIN c32_orders o ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name,c.city
)
SELECT customer_id,city,goods_total,
       ROW_NUMBER() OVER (PARTITION BY city
         ORDER BY goods_total DESC,customer_id ASC) AS regional_row_no
FROM customer_totals ORDER BY customer_id;
customer_id goods_total regional_row_no
1 19500.00 1
2 19500.00 1
3 0.00 2
4 0.00 1

I clienti 1 e 3 condividono la partizione di Seoul e ricevono 1 e 2. I clienti 2 e 4 iniziano ciascuno un'altra partizione da 1. Rimuovere soltanto PARTITION BY city per confrontare la sequenza globale 1, 2, 3, 4, quindi ripristinare la partizione per città.

Filtrare la classifica con una query esterna

La WHERE della stessa SELECT non può usare direttamente row_no appena calcolato. Formare prima il risultato con una CTE o una tabella derivata, poi applicare il filtro nella query esterna. Richiedere due righe è diverso dal richiedere tutti i clienti al primo posto: scegliere consapevolmente quale colonna filtrare.

Applicare la classifica direttamente alle righe articolo numererebbe i dettagli anziché i clienti. Mantenere distinti aggregazione per cliente e calcolo finestra rende esplicita l'unità rappresentata da ogni riga.

WITH customer_totals AS (
SELECT c.customer_id, c.customer_name, c.city,
       COALESCE(SUM(CASE WHEN o.status='paid'
         THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c
LEFT JOIN c32_orders o ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name,c.city
), ranked AS (
 SELECT customer_id,customer_name,goods_total,
        ROW_NUMBER() OVER (ORDER BY goods_total DESC,customer_id) AS row_no,
        RANK() OVER (ORDER BY goods_total DESC) AS amount_rank
 FROM customer_totals
)
SELECT customer_id,goods_total,row_no,amount_rank
FROM ranked WHERE row_no <= 2 ORDER BY row_no;

Il risultato contiene i clienti 1 e 2 con row_no pari a 1 e 2 e amount_rank pari a 1 per entrambi. Cambiare soltanto la condizione esterna in row_no <= 1 per ottenere una riga, oppure in amount_rank = 1 per includere entrambi gli ex aequo al primo posto. Ripristinare row_no <= 2 dopo il confronto.

Controllare parità e clienti ammessi al rapporto

Usare valori volutamente uguali e osservare la posizione successiva. Invertire soltanto il criterio customer_id di ROW_NUMBER per confrontare l'ordine degli ex aequo senza cambiare la classifica basata sull'importo, poi ripristinare il verso iniziale. Verificare anche la gestione degli ordini annullati e dei clienti senza acquisti. Una posizione calcolata è un risultato del rapporto, non un sostituto dell'ID stabile del cliente.

Prima di ripetere l'esercizio, controllare database selezionato, SHOW CREATE TABLE e righe delle quattro tabelle, salvando ciò che va conservato. Eliminare soltanto gli oggetti c32_ di questo articolo, partendo dalle tabelle figlie. DROP TABLE elimina struttura e dati e non viene annullato da un normale ROLLBACK: separare la pulizia dalle prove sulle transazioni.

DROP TABLE shopcourse.c32_items;
DROP TABLE shopcourse.c32_orders;
DROP TABLE shopcourse.c32_products;
DROP TABLE shopcourse.c32_customers;