MySQL ROW_NUMBER() erklärt (MySQL 8.0): Ranking, Top‑N‑Abfragen und Deduplizierung

1. Einführung

MySQL Version 8.0 hat viele neue Funktionen eingeführt, und eine der bemerkenswertesten ist die Unterstützung von Fensterfunktionen. In diesem Artikel konzentrieren wir uns auf eine der am häufigsten verwendeten Funktionen: ROW_NUMBER().

Die ROW_NUMBER()‑Funktion bietet leistungsstarke Möglichkeiten für Datenanalyse und Reporting und ermöglicht es, Daten basierend auf bestimmten Bedingungen einfach zu sortieren und zu ranken. Dieser Artikel erklärt alles von der Grundverwendung und praktischen Beispielen bis zu alternativen Ansätzen für ältere MySQL‑Versionen.

Zielgruppe

• Anfänger bis Fortgeschrittene Nutzer mit grundlegenden SQL‑Kenntnissen
• Ingenieure und Datenanalysten, die Daten mit MySQL verarbeiten und analysieren
• Jeder, der einen Umstieg auf die neueste MySQL‑Version in Betracht zieht

Vorteile von ROW_NUMBER()

Diese Funktion ermöglicht es, jeder Zeile basierend auf bestimmten Bedingungen eine eindeutige Nummer zuzuweisen. Zum Beispiel können Sie leicht Abfragen wie „eine Rangliste in absteigender Reihenfolge der Verkäufe erstellen“ oder „duplizierte Daten extrahieren und organisieren“ in kompakter Form schreiben.

In älteren Versionen musste man oft komplexe Abfragen mit benutzerdefinierten Variablen schreiben. Mit ROW_NUMBER() wird Ihr SQL einfacher und lesbarer.

In diesem Artikel verwenden wir konkrete Abfragebeispiele und erklären sie auf anfängerfreundliche Weise. Im nächsten Abschnitt werfen wir einen genaueren Blick auf die Grundsyntax und das Verhalten dieser Funktion.

2. Was ist die ROW_NUMBER()‑Funktion?

Die ROW_NUMBER()‑Funktion, neu hinzugefügt in MySQL 8.0, ist eine Art Fensterfunktion, die Zeilen fortlaufende Nummern zuweist. Sie kann Zeilen nach einer bestimmten Reihenfolge und/oder innerhalb jeder Gruppe nummerieren, was für Datenanalyse und Reporting äußerst nützlich ist. Hier erklären wir die Grundsyntax im Detail mit praktischen Beispielen.

Grundsyntax von ROW_NUMBER()

Zunächst ist das Grundformat von ROW_NUMBER() wie folgt.

SELECT
    column_name,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column) AS row_num
FROM
    table_name;

Bedeutung jedes Elements

• ROW_NUMBER() : Weist jeder Zeile eine fortlaufende Nummer zu.
• OVER : Schlüsselwort, das verwendet wird, um das Fenster für eine Fensterfunktion zu definieren.
• PARTITION BY : Gruppiert Daten nach der angegebenen Spalte. Optional. Wird es weggelassen, wird die Nummerierung über alle Zeilen hinweg angewendet.
• ORDER BY : Definiert die Reihenfolge, die zur Vergabe der Nummern verwendet wird, d.h. das Sortierkriterium.

Einfaches Beispiel

Angenommen, Sie haben eine Tabelle namens „sales“ mit den folgenden Daten.

Um innerhalb jeder Abteilung in absteigender Reihenfolge der Verkäufe fortlaufende Nummern zuzuweisen, verwenden Sie die folgende Abfrage.

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS row_num
FROM
    sales;

Ergebnis

Aus diesem Ergebnis können Sie sehen, dass Ranglisten nach Verkäufen innerhalb jeder Abteilung angezeigt werden.

Wie man PARTITION BY verwendet

Im obigen Beispiel werden die Daten nach der Spalte „department“ gruppiert. Dadurch wird für jede Abteilung eine separate Sequenz zugewiesen.

Wenn Sie PARTITION BY weglassen, wird die Nummerierung über alle Zeilen hinweg als eine Sequenz zugewiesen.

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Ergebnis

Eigenschaften und Besonderheiten von ROW_NUMBER()

• Eindeutige Nummerierung : Auch wenn Werte gleich sind, sind die zugewiesenen Nummern eindeutig.
• Umgang mit NULLs : Wenn ORDER BY NULL‑Werte enthält, erscheinen sie zuerst bei aufsteigender und zuletzt bei absteigender Sortierung.
• Performance‑Auswirkungen : Bei großen Datensätzen kann ORDER BY teuer sein, daher ist eine geeignete Indexierung wichtig.

3. Praktische Anwendungsfälle

Hier sind praktische Szenarien, die die MySQL‑Funktion ROW_NUMBER() nutzen. Diese Funktion ist in vielen realen Anwendungsfällen nützlich, z. B. beim Ranking von Daten und beim Umgang mit Duplikaten.

3-1. Ranking innerhalb jeder Gruppe

For example, consider the case where you want to “rank employees by sales within each department” using sales data. Use the following dataset as an example.

Beispielabfrage: Umsatzranking nach Abteilung

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Ergebnis:

Auf diese Weise erhält jede Abteilung ihre eigene Sequenz in absteigender Reihenfolge des Umsatzes, was das Erstellen von Rankings erleichtert.

3-2. Extrahieren der Top‑N‑Zeilen

Als Nächstes betrachten wir einen Fall, in dem Sie “die Top‑3‑Mitarbeiter nach Umsatz innerhalb jeder Abteilung extrahieren” möchten.

Beispielabfrage: Top‑N‑Zeilen extrahieren

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT
    employee,
    department,
    sale
FROM
    RankedSales
WHERE
    rank <= 3;

Ergebnis:

Dieses Beispiel ruft nur die Top‑3‑Zeilen nach Umsatz innerhalb jeder Abteilung ab. Wie Sie sehen können, ist ROW_NUMBER() nicht nur für das Ranking geeignet, sondern auch zum Filtern von Top‑Ergebnissen.

3-3. Finden und Entfernen von Duplikaten

Datenbanken enthalten manchmal doppelte Datensätze. In solchen Fällen können Sie diese ebenfalls leicht mit ROW_NUMBER() behandeln.

Beispielabfrage: Duplikate erkennen

SELECT *
FROM (
    SELECT
        employee,
        sale,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
    FROM
        sales
) tmp
WHERE rank > 1;

Diese Abfrage erkennt Duplikate, wenn mehrere Datensätze für denselben Mitarbeiternamen existieren.

Beispielabfrage: Duplikate löschen

DELETE FROM sales
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
        FROM
            sales
    ) tmp
    WHERE rank > 1
);

Zusammenfassung

ROW_NUMBER() ist in einer Vielzahl von Szenarien nützlich, wie zum Beispiel:

1. Ranking innerhalb jeder Gruppe
2. Extrahieren von Top‑N‑Zeilen
3. Erkennen und Löschen von Duplikaten

Damit wird die komplexe Datenverarbeitung und -analyse einfacher und effizienter.

4. Vergleich mit anderen Fensterfunktionen

In MySQL 8.0 gibt es neben ROW_NUMBER() Fensterfunktionen wie RANK() und DENSE_RANK(), die für Ranking‑ und Positionsberechnungen verwendet werden können. Obwohl sie ähnliche Rollen haben, unterscheiden sich ihr Verhalten und ihre Ergebnisse. Hier vergleichen wir jede Funktion und erklären, wann sie zu verwenden sind.

4-1. RANK()-Funktion

Die RANK()‑Funktion weist Ränge zu, gibt gleichen Werten denselben Rang und überspringt die nächste Rangnummer.

Grundsyntax

SELECT
    column_name,
    RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS rank
FROM
    table_name;

Beispiel

Verwenden Sie die folgenden Daten, um Umsatzränge zu berechnen.

Beispielabfrage: Verwendung von RANK()

SELECT
    employee,
    sale,
    RANK() OVER (ORDER BY sale DESC) AS rank
FROM
    sales;

Ergebnis:

Wichtige Punkte:

• A und B mit dem gleichen Umsatz (800) erhalten beide den Rang „1“.
• Der nächste Rang „2“ wird übersprungen, sodass C den Rang „3“ erhält.

4-2. DENSE_RANK()-Funktion

Die DENSE_RANK()‑Funktion weist ebenfalls denselben Rang für gleiche Werte zu, überspringt jedoch nicht die nächste Rangnummer.

Grundsyntax

SELECT
    column_name,
    DENSE_RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS dense_rank
FROM
    table_name;

Beispiel

Verwenden Sie dieselben Daten wie oben, um die DENSE_RANK()‑Funktion auszuprobieren.

Beispielabfrage: Verwendung von DENSE_RANK()

SELECT
    employee,
    sale,
    DENSE_RANK() OVER (ORDER BY sale DESC) AS dense_rank
FROM
    sales;

Ergebnis:

Wichtige Punkte:

• A und B mit dem gleichen Umsatzbetrag (800) werden beide als Rang „1“ behandelt.
• Im Gegensatz zu RANK() beginnt der nächste Rang bei „2“, sodass die Rangkontinuität erhalten bleibt.

4-3. Wie sich ROW_NUMBER() unterscheidet

Die ROW_NUMBER()-Funktion unterscheidet sich von den anderen beiden dadurch, dass sie eine eindeutige Nummer zuweist, auch wenn die Werte gleich sind.

Beispiel

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Ergebnis:

Wichtige Punkte:

• Selbst wenn Werte gleich sind, erhält jede Zeile eine eindeutige Nummer, sodass es keine doppelten Ränge gibt.
• Dies ist nützlich, wenn Sie strenge Ordnungssteuerung oder Eindeutigkeit pro Zeile benötigen.

4-4. Kurze Zusammenfassung der Anwendungsfälle

Zusammenfassung

ROW_NUMBER(), RANK() und DENSE_RANK() sollten je nach Szenario angemessen verwendet werden.

1. ROW_NUMBER() ist am besten geeignet, wenn Sie eindeutige Nummern pro Zeile benötigen.
2. RANK() ist nützlich, wenn Sie möchten, dass Gleichstände einen Rang teilen und Sie Ranglücken betonen wollen.
3. DENSE_RANK() ist geeignet, wenn Sie kontinuierliche Ränge ohne Lücken wünschen.

5. Alternativen für MySQL-Versionen unter 8.0

In Versionen vor MySQL 8.0 werden ROW_NUMBER() und andere Fensterfunktionen nicht unterstützt. Sie können jedoch ähnliches Verhalten mit benutzerdefinierten Variablen erreichen. Dieser Abschnitt erklärt praktische Alternativen für MySQL-Versionen unter 8.0.

5-1. Sequenzielle Nummerierung mit benutzerdefinierten Variablen

In MySQL 5.7 und früher können Sie benutzerdefinierte Variablen verwenden, um sequenzielle Nummern pro Zeile zuzuweisen. Schauen wir uns das folgende Beispiel an.

Beispiel: Umsatzrangierung nach Abteilung

Beispieldaten:

Abfrage:

SET @row_num = 0;
SET @dept = '';

SELECT
    employee,
    department,
    sale,
    @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
    @dept := department
FROM
    (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales;

Ergebnis:

5-2. Extrahieren der Top-N-Zeilen

Um die Top-N-Zeilen abzurufen, können Sie benutzerdefinierte Variablen auf ähnliche Weise verwenden.

Abfrage:

SET @row_num = 0;
SET @dept = '';

SELECT *
FROM (
    SELECT
        employee,
        department,
        sale,
        @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
        @dept := department
    FROM
        (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales
) AS ranked_sales
WHERE rank <= 3;

Ergebnis:

Diese Abfrage weist Ränge nach Abteilung zu und extrahiert dann nur Zeilen innerhalb der Top 3.

5-3. Erkennen und Löschen von Duplikaten

Sie können auch doppelte Daten mit benutzerdefinierten Variablen handhaben.

Abfragebeispiel: Duplikate erkennen

SET @row_num = 0;
SET @id_check = '';

SELECT *
FROM (
    SELECT
        id,
        name,
        @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
        @id_check := name
    FROM
        (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
) AS tmp
WHERE rank > 1;

Abfragebeispiel: Duplikate löschen

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
            @id_check := name
        FROM
            (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
    ) AS tmp
    WHERE rank > 1
);

5-4. Warnhinweise beim Verwenden benutzerdefinierter Variablen

1. Sitzungsabhängigkeit

• Benutzerdefinierte Variablen sind nur in der aktuellen Sitzung gültig. Sie können nicht über verschiedene Abfragen oder Sitzungen hinweg wiederverwendet werden.

1. Abhängigkeit von der Verarbeitungsreihenfolge

• Benutzerdefinierte Variablen hängen von der Ausführungsreihenfolge ab, daher ist die korrekte Einstellung von ORDER BY entscheidend.

1. SQL-Lesbarkeit und Wartbarkeit

• Abfragen können komplex werden, daher wird in MySQL 8.0 und höher die Verwendung von Fensterfunktionen empfohlen.

Zusammenfassung

In MySQL-Versionen unter 8.0 können Sie benutzerdefinierte Variablen verwenden, um sequentielle Nummerierung und Ranking anstelle von Fensterfunktionen zu implementieren. Da Abfragen jedoch tendenziell komplexer werden, ist es am besten, nach Möglichkeit auf eine neuere Version zu migrieren.

6. Vorsichtsmaßnahmen und bewährte Verfahren

MySQLs ROW_NUMBER()‑Funktion und variablenbasierte Alternativen sind sehr praktisch, aber es gibt wichtige Punkte, die Sie beachten sollten, um sie genau und effizient auszuführen. Dieser Abschnitt erklärt praktische Vorsichtsmaßnahmen und bewährte Verfahren zur Leistungsoptimierung.

6-1. Leistungsüberlegungen

1. ORDER BY‑Kosten

ROW_NUMBER() wird immer zusammen mit ORDER BY verwendet. Da es eine Sortierung erfordert, kann die Verarbeitungszeit bei großen Datensätzen erheblich steigen.

Gegenmaßnahmen:

• Indizes verwenden: Fügen Sie den in ORDER BY verwendeten Spalten Indizes hinzu, um die Sortierung zu beschleunigen.
• LIMIT verwenden: Rufen Sie nur die tatsächlich benötigte Zeilenanzahl ab, um die zu verarbeitende Datenmenge zu reduzieren.

Beispiel:

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales
LIMIT 1000;

2. Erhöhter Speicherverbrauch und Festplatten‑I/O

Fensterfunktionen werden mithilfe temporärer Tabellen und Speicher verarbeitet. Mit wachsendem Datenvolumen können Speicherverbrauch und Festplatten‑I/O zunehmen.

Gegenmaßnahmen:

• Abfragen aufteilen: Zerlegen Sie die Verarbeitung in kleinere Abfragen und extrahieren Sie Daten schrittweise, um die Last zu reduzieren.
• Temporäre Tabellen verwenden: Speichern Sie extrahierte Daten in einer temporären Tabelle und führen Sie die Aggregation von dort aus, um die Arbeitslast zu verteilen.

6-2. Tipps zur Abfrageoptimierung

1. Ausführungsplan prüfen

In MySQL können Sie EXPLAIN verwenden, um den Ausführungsplan der Abfrage zu prüfen. Dies hilft Ihnen zu überprüfen, ob Indizes korrekt verwendet werden.

Beispiel:

EXPLAIN
SELECT
    employee,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Beispielausgabe:

Wenn Sie Using index sehen, bedeutet das, dass der Index angemessen verwendet wird.

2. Indizes optimieren

Stellen Sie sicher, dass Sie Indizes zu den in ORDER BY und WHERE verwendeten Spalten hinzufügen. Achten Sie besonders auf das Folgende.

• Einspaltige Indizes: Gut für einfache Sortierbedingungen
• Mehrspaltige Indizes: Effektiv, wenn mehrere Spalten in Bedingungen vorkommen

Beispiel:

CREATE INDEX idx_department_sale ON sales(department, sale DESC);

3. Batch‑Verarbeitung verwenden

Anstatt einen riesigen Datensatz auf einmal zu verarbeiten, können Sie die Last reduzieren, indem Sie die Daten in Batches verarbeiten.

Beispiel:

SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 0;
SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 1000;

6-3. Datenkonsistenz wahren

1. Updates und Neuberechnung

Wenn Zeilen eingefügt oder gelöscht werden, kann die Nummerierung sich ändern. Erstellen Sie einen Mechanismus, um die Nummern bei Bedarf neu zu berechnen.

Beispiel:

CREATE VIEW ranked_sales AS
SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Die Verwendung einer Sicht hilft Ihnen, die Rankings basierend auf den neuesten Daten aktuell zu halten.

6-4. Beispiel für eine bewährte Abfrage

Im Folgenden ein Beispiel für bewährte Praktiken, die Leistung und Wartbarkeit berücksichtigen.

Beispiel: Top‑N‑Zeilen extrahieren

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT *
FROM RankedSales
WHERE rank <= 3;

Diese Struktur verwendet einen Common Table Expression (CTE), um die Lesbarkeit und Wiederverwendbarkeit zu verbessern.

Zusammenfassung

Wenn Sie ROW_NUMBER() oder dessen Alternativen verwenden, beachten Sie diese Punkte:

1. Verbessern Sie die Geschwindigkeit durch Indexoptimierung.
2. Identifizieren Sie Engpässe, indem Sie den Ausführungsplan prüfen.
3. Planen Sie Datenaktualisierungen und erhalten Sie die Konsistenz.
4. Verwenden Sie Batch-Verarbeitung und CTEs, um die Last zu verteilen.

Die Anwendung dieser bewährten Methoden ermöglicht eine effiziente Verarbeitung für groß angelegte Datenanalyse und Berichterstellung.

7. Conclusion

In diesem Artikel haben wir uns auf die MySQL-ROW_NUMBER()-Funktion konzentriert, alles von der Grundnutzung und praktischen Beispielen bis zu Alternativen für ältere Versionen, einschließlich Einschränkungen und bewährten Methoden, erklärt. In diesem Abschnitt fassen wir die wichtigsten Punkte zusammen und geben praktische Erkenntnisse.

7-1. Why ROW_NUMBER() is useful

Die ROW_NUMBER()-Funktion ist besonders praktisch für Datenanalyse und Berichterstellung in den folgenden Bereichen:

1. Sequenzielle Nummerierung innerhalb von Gruppen: Einfach Verkaufsrankings nach Abteilung oder kategoriebezogene Rankings erstellen.
2. Extrahieren der Top N Zeilen: Daten effizient nach bestimmten Bedingungen filtern und extrahieren.
3. Erkennen und Löschen von Duplikaten: Nützlich für Datenbereinigung und -organisation.

Da es komplexe Abfragen vereinfacht, verbessert es die Lesbarkeit und Wartbarkeit von SQL erheblich.

7-2. Comparison with other window functions

Im Vergleich zu Fensterfunktionen wie RANK() und DENSE_RANK() unterscheidet sich ROW_NUMBER() dadurch, dass es selbst bei identischen Werten eine eindeutige Nummer zuweist.

Die richtige Funktion wählen:
Die Auswahl der besten Funktion für Ihren Zweck ermöglicht eine effiziente Datenverarbeitung.

7-3. Handling older MySQL versions

Für Umgebungen unter MySQL 8.0 haben wir ebenfalls Ansätze mit benutzerdefinierten Variablen vorgestellt. Sie sollten jedoch die folgenden Einschränkungen berücksichtigen:

• Verminderte Lesbarkeit aufgrund komplexerer SQL
• Die Abfrageoptimierung kann in manchen Fällen schwieriger sein
• Zusätzliche Handhabung kann erforderlich sein, um die Datenkonsistenz zu wahren

Wenn möglich, sollten Sie dringend eine Migration zu MySQL 8.0 oder höher und die Nutzung von Fensterfunktionen in Betracht ziehen.

7-4. Key points for performance optimization

1. Indexes verwenden: Fügen Sie Indexe zu Spalten hinzu, die im ORDER BY verwendet werden, um die Geschwindigkeit zu erhöhen.
2. Ausführungspläne prüfen: Validieren Sie die Leistung im Voraus mit EXPLAIN.
3. Batch-Verarbeitung übernehmen: Verarbeiten Sie große Datensätze in kleineren Teilen, um die Last zu verteilen.
4. Views und CTEs verwenden: Die Wiederverwendbarkeit verbessern und komplexe Abfragen vereinfachen.

Durch die Anwendung dieser Techniken können Sie eine effiziente und stabile Datenverarbeitung erreichen.

7-5. Final notes

ROW_NUMBER() ist ein leistungsstarkes Werkzeug, das die Effizienz der Datenanalyse erheblich steigern kann.
In diesem Artikel haben wir alles von der Grundsyntax und praktischen Beispielen bis zu Einschränkungen und Alternativen behandelt.

Wir ermutigen Sie, die Abfragen selbst auszuführen, während Sie diesem Artikel folgen. Die Verbesserung Ihrer SQL‑Fähigkeiten hilft Ihnen, komplexere Datenanalysen und Berichte selbstbewusst zu bewältigen.

Appendix: Reference resources

• Offizielle Dokumentation: MySQL Window Functions
• Online‑SQL‑Umgebung: SQL Fiddle (ein Tool, mit dem Sie SQL online ausführen und testen können)