MySQL Fensterfunktionen

Einführung

In diesem Lab werden Sie die Leistungsfähigkeit von MySQL Window Functions erkunden. Sie lernen, wie Sie Berechnungen über Mengen von Tabellenzeilen durchführen, die mit der aktuellen Zeile in Beziehung stehen.

Sie beginnen mit der Erstellung eines Beispieldatensatzes und verwenden dann Funktionen wie ROW_NUMBER(), SUM(), AVG() und LAG() in Kombination mit der OVER()-Klausel, um fortgeschrittene Datenanalysen durchzuführen. Dieses Lab bietet praktische Schritt-für-Schritt-Beispiele, die Ihnen helfen, diese leistungsstarken Funktionen zu verstehen und anzuwenden.

Datenbank und Tabelle einrichten

Bevor Sie Window Functions verwenden, benötigen Sie eine Datenbank und eine Tabelle mit Beispieldaten. In diesem Schritt erstellen Sie eine Datenbank namens company und eine Tabelle namens employees.

Öffnen Sie zunächst das Terminal auf Ihrem Desktop.

Verbinden Sie sich als Benutzer root mit dem MySQL-Server. Da es sich um eine Laborumgebung handelt, können Sie sudo verwenden, um sich ohne Passwort zu verbinden.

sudo mysql -u root

Sobald Sie verbunden sind, sehen Sie die MySQL-Eingabeaufforderung (mysql>).

Erstellen Sie nun die Datenbank company und wechseln Sie zu ihr. Die Klausel IF NOT EXISTS verhindert einen Fehler, falls die Datenbank bereits existiert.

CREATE DATABASE IF NOT EXISTS company;
USE company;

Erstellen Sie als Nächstes die Tabelle employees. Diese Tabelle speichert die Mitarbeiter-ID, den Namen, die Abteilung und das Gehalt.

CREATE TABLE IF NOT EXISTS employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

Fügen Sie nun einige Beispieldaten in die Tabelle employees ein.

INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice', 'Sales', 60000.00),
(2, 'Bob', 'Marketing', 55000.00),
(3, 'Charlie', 'Sales', 65000.00),
(4, 'David', 'IT', 70000.00),
(5, 'Eve', 'Marketing', 62000.00)
ON DUPLICATE KEY UPDATE
    employee_name = VALUES(employee_name),
    department = VALUES(department),
    salary = VALUES(salary);

Um zu überprüfen, ob die Daten korrekt eingefügt wurden, können Sie alle Zeilen aus der Tabelle employees anzeigen.

SELECT * FROM employees;

Die Ausgabe sollte die fünf von Ihnen eingefügten Datensätze anzeigen:

+-------------+---------------+------------+----------+
| employee_id | employee_name | department | salary   |
+-------------+---------------+------------+----------+
|           1 | Alice         | Sales      | 60000.00 |
|           2 | Bob           | Marketing  | 55000.00 |
|           3 | Charlie       | Sales      | 65000.00 |
|           4 | David         | IT         | 70000.00 |
|           5 | Eve           | Marketing  | 62000.00 |
+-------------+---------------+------------+----------+
5 rows in set (0.00 sec)

Nachdem die Datenbank und die Tabelle bereit sind, können Sie mit dem nächsten Schritt fortfahren, um mehr über Window Functions zu erfahren.

Zeilen mit ROW_NUMBER() einstufen

Die Funktion ROW_NUMBER() weist jeder Zeile innerhalb einer Partition eines Ergebnissatzes eine eindeutige fortlaufende Ganzzahl zu. Sie wird häufig für Ranglisten und Paginierung verwendet.

Die grundlegende Syntax lautet: ROW_NUMBER() OVER (ORDER BY spaltenname [ASC|DESC])

• OVER(): Diese Klausel definiert das Fenster (die Menge der Zeilen) für die Funktion.
• ORDER BY: Diese Klausel innerhalb von OVER() gibt die Reihenfolge an, in der die Zeilennummern zugewiesen werden.

Fahren Sie in der MySQL-Shell fort und verwenden Sie nun ROW_NUMBER(), um Mitarbeiter nach ihrem Gehalt in absteigender Reihenfolge einzustufen.

SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM
    employees;

Diese Abfrage fügt eine Spalte salary_rank hinzu, die die Mitarbeiter vom höchsten zum niedrigsten Gehalt nummeriert.

+---------------+------------+----------+-------------+
| employee_name | department | salary   | salary_rank |
+---------------+------------+----------+-------------+
| David         | IT         | 70000.00 |           1 |
| Charlie       | Sales      | 65000.00 |           2 |
| Eve           | Marketing  | 62000.00 |           3 |
| Alice         | Sales      | 60000.00 |           4 |
| Bob           | Marketing  | 55000.00 |           5 |
+---------------+------------+----------+-------------+
5 rows in set (0.00 sec)

Wie Sie sehen, hat David den Rang 1, da er das höchste Gehalt hat. Dies zeigt, wie ROW_NUMBER() zur Erstellung einer einfachen Rangliste verwendet werden kann.

Laufende Summe mit SUM() berechnen

Ein laufender Gesamtbetrag (oder kumulative Summe) ist die Summe einer Zahlenfolge, die aktualisiert wird, sobald jede neue Zahl hinzugefügt wird. In SQL können Sie dies mit SUM() OVER() berechnen.

Die Syntax lautet: SUM(spaltenname) OVER (ORDER BY spaltenname [ASC|DESC])

Diese Funktion summiert die Werte einer Spalte in der durch die ORDER BY-Klausel angegebenen Reihenfolge.

Berechnen wir nun den laufenden Gesamtbetrag der Gehälter, sortiert nach employee_id.

SELECT
    employee_name,
    salary,
    SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM
    employees;

Das Ergebnis zeigt das Gehalt jedes Mitarbeiters und die kumulative Summe bis zu dieser Zeile.

+---------------+----------+---------------+
| employee_name | salary   | running_total |
+---------------+----------+---------------+
| Alice         | 60000.00 |      60000.00 |
| Bob           | 55000.00 |     115000.00 |
| Charlie       | 65000.00 |     180000.00 |
| David         | 70000.00 |     250000.00 |
| Eve           | 62000.00 |     312000.00 |
+---------------+----------+---------------+
5 rows in set (0.00 sec)

Zum Beispiel ist der running_total für Bob die Summe seines Gehalts und des Gehalts von Alice (60000,00 + 55000,00 = 115000,00). Dies ist nützlich, um kumulative Kennzahlen wie Verkäufe oder Ausgaben im Laufe der Zeit zu verfolgen.

Gruppenberechnungen mit PARTITION BY

Die Klausel PARTITION BY teilt den Ergebnissatz in Partitionen (Gruppen) auf und wendet die Fensterfunktion auf jede Partition unabhängig an. Dies ist nützlich für Berechnungen innerhalb bestimmter Kategorien.

Die Syntax lautet: funktion() OVER (PARTITION BY spaltenname ORDER BY ...)

Verwenden wir PARTITION BY, um Mitarbeiter innerhalb jeder Abteilung basierend auf ihrem Gehalt einzustufen.

SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM
    employees;

Diese Abfrage partitioniert die Daten nach department und stuft dann die Mitarbeiter in jeder Abteilung nach Gehalt ein.

+---------------+------------+----------+--------------+
| employee_name | department | salary   | rank_in_dept |
+---------------+------------+----------+--------------+
| David         | IT         | 70000.00 |            1 |
| Eve           | Marketing  | 62000.00 |            1 |
| Bob           | Marketing  | 55000.00 |            2 |
| Charlie       | Sales      | 65000.00 |            1 |
| Alice         | Sales      | 60000.00 |            2 |
+---------------+------------+----------+--------------+
5 rows in set (0.00 sec)

Beachten Sie, dass die Rangfolge für jede Abteilung neu beginnt. Zum Beispiel sind sowohl Eve als auch Charlie auf Rang 1, aber innerhalb ihrer jeweiligen Abteilungen 'Marketing' und 'Sales'. Dies ermöglicht eine granularere Analyse im Vergleich zu einer globalen Rangfolge.

Zeilen vergleichen mit LAG()

Die Funktion LAG() ermöglicht den Zugriff auf eine Zeile mit einem bestimmten physischen Offset, die vor der aktuellen Zeile liegt. Sie ist nützlich, um einen Wert in der aktuellen Zeile mit einem Wert in einer vorherigen Zeile zu vergleichen.

Die Syntax lautet: LAG(expression, offset, default_value) OVER (ORDER BY ...)

• expression: Die Spalte oder der Ausdruck, der abgerufen werden soll.
• offset: Die Anzahl der Zeilen, die zurückgeschaut werden soll (Standard ist 1).
• default_value: Der Wert, der zurückgegeben wird, wenn der Offset außerhalb des gültigen Bereichs liegt (z. B. für die erste Zeile).

Ermitteln wir das Gehalt des vorherigen Mitarbeiters in der Liste, sortiert nach employee_id.

SELECT
    employee_name,
    salary,
    LAG(salary) OVER (ORDER BY employee_id) AS previous_salary
FROM
    employees;

Diese Abfrage ruft das Gehalt aus der vorhergehenden Zeile ab. Für die erste Zeile, für die keine vorhergehende Zeile existiert, wird NULL zurückgegeben.

+---------------+----------+-----------------+
| employee_name | salary   | previous_salary |
+---------------+----------+-----------------+
| Alice         | 60000.00 |            NULL |
| Bob           | 55000.00 |        60000.00 |
| Charlie       | 65000.00 |        55000.00 |
| David         | 70000.00 |        65000.00 |
| Eve           | 62000.00 |        70000.00 |
+---------------+----------+-----------------+
5 rows in set (0.00 sec)

Sie können dies verwenden, um die Differenz zwischen aufeinanderfolgenden Gehältern zu berechnen. Wenn das vorherige Gehalt NULL ist (für die erste Zeile), ist das Ergebnis ebenfalls NULL.

SELECT
    employee_name,
    salary,
    salary - LAG(salary) OVER (ORDER BY employee_id) AS salary_diff
FROM
    employees;

Diese Abfrage berechnet die Differenz zwischen dem Gehalt des aktuellen Mitarbeiters und dem des vorherigen.

+---------------+----------+-------------+
| employee_name | salary   | salary_diff |
+---------------+----------+-------------+
| Alice         | 60000.00 |        NULL |
| Bob           | 55000.00 |    -5000.00 |
| Charlie       | 65000.00 |    10000.00 |
| David         | 70000.00 |     5000.00 |
| Eve           | 62000.00 |    -8000.00 |
+---------------+----------+-------------+
5 rows in set (0.00 sec)

Sie haben nun mehrere wichtige Fensterfunktionen geübt. Sie können die MySQL-Shell beenden.

exit;

Zusammenfassung

In diesem Lab haben Sie die Verwendung von MySQL-Fensterfunktionen erkundet. Sie haben gelernt, wie man Zeilennummern mit ROW_NUMBER() zuweist, laufende Summen mit SUM() OVER() berechnet, Berechnungen für bestimmte Gruppen mit PARTITION BY durchführt und mit LAG() auf Daten aus vorherigen Zeilen zugreift.

Durch die Anwendung dieser Funktionen auf einen Beispieldatensatz haben Sie praktische Erfahrungen in der Durchführung fortgeschrittener Datenanalysen direkt in Ihren SQL-Abfragen gesammelt. Dies sind wertvolle Fähigkeiten für die Erstellung komplexer Berichte und Erkenntnisse aus Ihren Daten.