MariaDB-Kurs / ROW_NUMBER, RANK und DENSE_RANK vergleichen
MariaDB-Fensterfunktionen ergänzen Berechnungen über eine Zeilenmenge, ohne deren Zeilen zu einer einzigen Ausgabezeile zusammenzufassen. ROW_NUMBER weist jeder Zeile eine andere Nummer zu. RANK und DENSE_RANK erlauben gemeinsame Rangplätze bei gleichen Sortierwerten, unterscheiden sich aber durch die anschließend entstehenden Lücken.
Zeilennummer und gemeinsamen Rang unterscheiden
Zwei Kunden mit gleichem Einkaufswert können für die Anzeige unterschiedliche Zeilennummern und trotzdem denselben Betragsrang benötigen. Berechnen Sie zunächst einen Gesamtbetrag pro Kunde und ordnen Sie danach diese Zeilen ein. Nur die Funktion auszutauschen, korrigiert weder eine Eingabe aus Bestellpositionen noch eine falsche Gleichstandsregel. Trennen Sie die Schritte mit WITH und gemeinsamen Tabellenausdrücken (CTEs).
| Funktion | Gleichstände | Beispielreihenfolge |
|---|---|---|
| ROW_NUMBER | Andere Nummer je Zeile | 1, 2, 3, 4 |
| RANK | Gemeinsame Ränge mit Lücken | 1, 1, 3, 3 |
| DENSE_RANK | Gemeinsame Ränge ohne Lücken | 1, 1, 2, 2 |
Kunden mit gleichen Beträgen vorbereiten
Verbinden Sie sich als course_user mit dem Lernserver und wählen Sie shopcourse. Die Anleitung zu Client und UTF-8-SQL-Dateien erklärt das Speichern und Ausführen mit SOURCE C:/mariadb-lab/lesson.sql;. Ersetzen Sie den Pfad durch die tatsächliche Datei und führen Sie SQL im MariaDB-Client aus.
Prüfen Sie mit SELECT VERSION(), DATABASE(); und SHOW TABLES; die Umgebung vor dem Erstellen von c32_customers, c32_products, c32_orders und c32_items. Diese Namen gehören ausschließlich zur Übung. Untersuchen Sie vorhandene Definitionen und Daten, bevor Sie über deren Entfernung entscheiden; erhalten Sie Anwendungstabellen. Prüfen Sie, ob Ihre Version die verwendete Fensterfunktionssyntax unterstützt.
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);
Die koreanischen Originalnamen und Städte bleiben erhalten. Die Produkte 1 bis 4 sind Notizbuch, Kugelschreiber, Becher und Lampe. Die Kunden 1 und 3 teilen die Stadt Seoul (서울); die Kunden 2 und 4 wohnen in Busan und Daejeon. Behalten Sie die UTF-8-Zeichenfolgen für reproduzierbare Ergebnisse bei.
Kunde 1 hat Warenwerte von 11500.00 und 8000.00 in zwei bezahlten Bestellungen, Kunde 2 hat 19500.00 in einer Bestellung. Beide erreichen somit 19500.00. Kunde 3 hat nur eine stornierte Bestellung, Kunde 4 keine. Dieser Bericht schließt beide mit bezahltem Warenwert null ein. Kunden ohne Kauf zu erhalten und stornierte Beträge auszuschließen, sind bewusste Entscheidungen dieser Abfrage.
Pro Kunde aggregieren und drei Funktionen vergleichen
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;
Die LEFT JOINs erhalten Kunden ohne Bestellungen oder Positionen. CASE summiert Beträge nur bei bezahlter Bestellung; COALESCE ersetzt einen fehlenden Gesamtbetrag durch null. GROUP BY erzeugt vor den Fensterfunktionen eine Zeile pro Kunde. Der Betrag verwendet Positionspreise und enthält keine Versandkosten.
| Kunde | Betrag | 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 sortiert nach absteigendem Betrag und verwendet customer_id zur eindeutigen Auflösung von Gleichständen. RANK und DENSE_RANK sortieren nur nach dem Betrag, damit gleiche Summen denselben Rang behalten. Eine zusätzliche eindeutige ID in deren Fensterdefinitionen würde unterschiedliche Kombinationen schaffen und die gewünschten Gleichstände aufheben. Nach zwei gemeinsam Erstplatzierten geht RANK mit 3 weiter, DENSE_RANK dagegen mit 2.
Fensterreihenfolge, Anzeige und Partitionen trennen
ORDER BY innerhalb von OVER bestimmt die Berechnungsreihenfolge. Das ORDER BY des abschließenden SELECT bestimmt die Anzeigereihenfolge. Geben Sie beide an, wenn beide festgelegt sein sollen. Die Sortierung eines Fensters garantiert allein keine Darstellungsreihenfolge.
PARTITION BY teilt die Zeilen in Gruppen, innerhalb derer die Berechnung neu beginnt. Für Kundennummern pro Stadt muss die aggregierte Stufe city bereitstellen. Anders als GROUP BY reduziert die Fensterpartition die Kunden nicht zu einer einzelnen Zeile pro Stadt.
Die folgende vollständige Abfrage verwendet dieselben Tabellen und erhält eine Zeile pro Kunde. Sie nummeriert innerhalb jeder Stadt und zeigt das Ergebnis nach Kundenkennung an.
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 |
Die Kunden 1 und 3 teilen die Seoul-Partition und erhalten 1 und 2. Die Kunden 2 und 4 beginnen jeweils eine andere Partition bei 1. Das SELECT zeigt auch die gespeicherte koreanische Stadt, die in der Erklärungstabelle zugunsten der Nummern fehlt. Entfernen Sie nur PARTITION BY city, um eine globale Nummerierung zu vergleichen. Stellen Sie danach die Stadtpartition wieder her.
Die Rangfolge in einer äußeren Abfrage filtern
WHERE desselben SELECT kann die gerade berechnete Spalte row_no nicht direkt verwenden. Berechnen Sie die Rangfolge in einer weiteren CTE oder abgeleiteten Tabelle und filtern Sie außerhalb. „Zwei Zeilen“ und „alle gemeinsam Erstplatzierten“ sind unterschiedliche Anforderungen. Wählen Sie die berechnete Spalte, die diese Bedingung ausdrückt.
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;
Das Ergebnis enthält die Kunden 1 und 2 mit Zeilennummern 1 und 2 und Rang 1 für beide. Ändern Sie nur die äußere Bedingung auf row_no <= 1: Es bleibt Kunde 1. Mit amount_rank = 1 bleiben beide gemeinsam Erstplatzierten. Stellen Sie nach dem Vergleich row_no <= 2 wieder her.
Die ursprünglichen Positionen zu ordnen, würde Positionen statt Kunden nummerieren. Halten Sie die Trennung zwischen Kundenaggregation und Rangbildung dieser Gesamtbeträge sichtbar.
Gleichstände und Ausgangsmenge des Berichts prüfen
Verwenden Sie absichtliche Gleichstände und beobachten Sie auch den folgenden Rang. Ändern Sie im ersten Beispiel nur die Auflösung des Gleichstands von ROW_NUMBER von customer_id ASC auf customer_id DESC. Die Kunden mit gleichen Beträgen tauschen ihre Nummern, während die ausschließlich betragsbasierten Ränge gleich bleiben. Stellen Sie ASC wieder her und prüfen Sie das ursprüngliche Ergebnis. Eine Berichtsnummer ersetzt keine dauerhafte Kundenkennung.
Prüfen Sie außerdem, dass stornierte Bestellungen keinen Betrag beitragen und Kunden ohne Kauf bei entsprechender Berichtsanforderung enthalten bleiben. Die offiziellen Referenzen zu ROW_NUMBER, RANK und DENSE_RANK erläutern Nummerierung und Gleichstände.
Prüfen Sie vor einer Wiederholung die ausgewählte Datenbank, Definitionen und gespeicherte Zeilen. Sichern Sie benötigte Inhalte. Sind die folgenden Tabellen ausschließlich entbehrliche Übungsobjekte, entfernen Sie zuerst die abhängigen und danach die referenzierten Tabellen. DROP TABLE entfernt Struktur und Daten und wird durch ein gewöhnliches ROLLBACK nicht rückgängig gemacht. Trennen Sie die Bereinigung von Transaktionsversuchen.
DROP TABLE shopcourse.c32_items;
DROP TABLE shopcourse.c32_orders;
DROP TABLE shopcourse.c32_products;
DROP TABLE shopcourse.c32_customers;




