MySQL Common Table Expressions (CTEs)

Einführung

In diesem Lab lernen Sie, wie Sie Common Table Expressions (CTEs) in MySQL verwenden. CTEs helfen dabei, komplexe Abfragen zu vereinfachen, indem sie diese in logische, lesbare Schritte unterteilen. Eine CTE erstellt einen temporären, benannten Ergebnissatz, auf den Sie innerhalb einer einzelnen SQL-Anweisung verweisen können.

Sie beginnen mit der Einrichtung einer Datenbank und der Erstellung einer einfachen CTE zum Filtern von Mitarbeiterdaten. Anschließend erkunden Sie rekursive CTEs zur Verarbeitung hierarchischer Daten, wie z. B. eines Organigramms. Abschließend lernen Sie, wie Sie eine CTE mit einer Tabelle verknüpfen, um fortgeschrittenere Datenanalysen durchzuführen.

Datenbank und eine einfache CTE einrichten

In diesem ersten Schritt richten Sie die Datenbankumgebung ein und definieren eine einfache Common Table Expression (CTE). CTEs werden mit der WITH-Klausel definiert und fungieren als temporäre Ansicht, die nur für die Dauer einer einzelnen Abfrage existiert. Dies erleichtert das Lesen und Verwalten komplexer Abfragen.

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

Verbinden Sie sich als Benutzer root mit dem MySQL-Server. In dieser Laborumgebung 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 eine Datenbank namens labex_db und wechseln Sie zu dieser.

CREATE DATABASE IF NOT EXISTS labex_db;
USE labex_db;

Erstellen Sie als Nächstes eine Tabelle namens employees und fügen Sie einige Beispieldaten ein. Diese Tabelle speichert grundlegende Informationen über Mitarbeiter, einschließlich ihrer Abteilung und ihres Gehalts.

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

INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'John', 'Doe', 'Sales', 60000.00),
(2, 'Jane', 'Smith', 'Marketing', 75000.00),
(3, 'Robert', 'Jones', 'Sales', 55000.00),
(4, 'Emily', 'Brown', 'IT', 90000.00),
(5, 'Michael', 'Davis', 'Marketing', 70000.00);

Nachdem die Tabelle nun bereit ist, definieren wir eine einfache CTE, um nur die Mitarbeiter aus der Abteilung 'Sales' auszuwählen.

WITH SalesEmployees AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department = 'Sales'
)
SELECT * FROM SalesEmployees;

Lassen Sie uns diese Abfrage aufschlüsseln:

• WITH SalesEmployees AS (...): Dies definiert eine CTE namens SalesEmployees.
• Die Abfrage in den Klammern wählt Mitarbeiter aus der Tabelle employees aus, bei denen die department 'Sales' ist.
• SELECT * FROM SalesEmployees;: Dies ist die Hauptabfrage, die alle Daten aus unserem temporären SalesEmployees-Ergebnissatz abruft.

Sie sollten die folgende Ausgabe sehen, die nur die Mitarbeiter aus der Abteilung Sales auflistet:

+-------------+------------+-----------+----------+
| employee_id | first_name | last_name | salary   |
+-------------+------------+-----------+----------+
|           1 | John       | Doe       | 60000.00 |
|           3 | Robert     | Jones     | 55000.00 |
+-------------+------------+-----------+----------+
2 rows in set (0.00 sec)

Dies bestätigt, dass Ihre erste CTE korrekt funktioniert. Bitte bleiben Sie in der MySQL-Shell für den nächsten Schritt.

Erstellen einer rekursiven CTE für hierarchische Daten

Rekursive CTEs sind ein leistungsstarkes Feature für die Abfrage hierarchischer Daten, wie z. B. Organigramme oder verschachtelte Kategorien. Eine rekursive CTE bezieht sich selbst, um eine baumartige Struktur Schritt für Schritt zu verarbeiten. Sie besteht aus einem "Anker-Element" (dem Startpunkt) und einem "rekursiven Element" (der Iteration).

In diesem Schritt erstellen Sie eine Tabelle, die eine Mitarbeiterhierarchie darstellt, und verwenden eine rekursive CTE, um diese anzuzeigen.

Erstellen Sie zunächst, während Sie sich noch in der MySQL-Shell befinden, die Tabelle employee_hierarchy. Diese Tabelle enthält eine manager_id, die auf die employee_id eines anderen Mitarbeiters verweist und so die Hierarchie erstellt.

CREATE TABLE IF NOT EXISTS employee_hierarchy (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

INSERT INTO employee_hierarchy (employee_id, employee_name, manager_id) VALUES
(1, 'David', NULL),
(2, 'Emily', 1),
(3, 'Frank', 1),
(4, 'Grace', 2),
(5, 'Henry', 2),
(6, 'Ivy', 3),
(7, 'Jack', 3);

In dieser Struktur ist 'David' der Manager auf oberster Ebene, da seine manager_id NULL ist.

Schreiben Sie nun eine rekursive CTE, um diese Hierarchie zu durchlaufen. Das Schlüsselwort RECURSIVE ist erforderlich.

WITH RECURSIVE EmployeeHierarchyCTE AS (
    -- Anchor member: wählt den Manager auf oberster Ebene aus
    SELECT employee_id, employee_name, manager_id, 0 AS level
    FROM employee_hierarchy
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: verbindet sich mit sich selbst, um Untergebene zu finden
    SELECT e.employee_id, e.employee_name, e.manager_id, h.level + 1
    FROM employee_hierarchy e
    INNER JOIN EmployeeHierarchyCTE h ON e.manager_id = h.employee_id
)
SELECT * FROM EmployeeHierarchyCTE ORDER BY level, employee_name;

Lassen Sie uns diese Abfrage analysieren:

• Anker-Element: Die erste SELECT-Anweisung findet die Wurzel der Hierarchie (wo manager_id NULL ist) und weist ihr level 0 zu.
• Rekursives Element: Die zweite SELECT-Anweisung verknüpft employee_hierarchy mit der CTE selbst (EmployeeHierarchyCTE). Sie findet alle Mitarbeiter, deren manager_id mit einer bereits in der CTE vorhandenen employee_id übereinstimmt, und erhöht das level.
• UNION ALL: Dieser Operator kombiniert die Ergebnisse aus dem Anker- und den rekursiven Elementen.

Die Abfrage liefert die folgende Ausgabe, die das vollständige Organigramm mit Ebenen zeigt:

+-------------+---------------+------------+-------+
| employee_id | employee_name | manager_id | level |
+-------------+---------------+------------+-------+
|           1 | David         |       NULL |     0 |
|           2 | Emily         |          1 |     1 |
|           3 | Frank         |          1 |     1 |
|           4 | Grace         |          2 |     2 |
|           5 | Henry         |          2 |     2 |
|           6 | Ivy           |          3 |     2 |
|           7 | Jack          |          3 |     2 |
+-------------+---------------+------------+-------+
7 rows in set (0.00 sec)

Sie haben erfolgreich eine rekursive CTE zur Abfrage hierarchischer Daten verwendet. Bitte bleiben Sie für den letzten Schritt in der MySQL-Shell.

CTE mit einer Tabelle verknüpfen

CTEs können wie reguläre Tabellen mit anderen Tabellen verknüpft werden. Dies ist nützlich, um aggregierte Daten aus einer CTE mit detaillierten Daten aus einer anderen Tabelle zu kombinieren. In diesem Schritt erstellen Sie eine CTE zur Berechnung des durchschnittlichen Gehalts pro Abteilung und verknüpfen diese dann wieder mit der employees-Tabelle.

Führen Sie die folgende Abfrage aus, während Sie sich noch in der MySQL-Shell befinden. Sie definiert eine CTE namens AvgSalaryByDepartment und verknüpft diese dann mit der employees-Tabelle.

WITH AvgSalaryByDepartment AS (
    SELECT
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT
    e.employee_id,
    e.first_name,
    e.department,
    e.salary,
    a.avg_salary
FROM
    employees e
JOIN
    AvgSalaryByDepartment a ON e.department = a.department;

Hier ist die Aufschlüsselung:

• Die CTE AvgSalaryByDepartment berechnet das durchschnittliche Gehalt für jede Abteilung.
• Die Hauptabfrage verknüpft dann die employees-Tabelle (alias e) mit dieser CTE (alias a) über die Spalte department.
• Dies ermöglicht es Ihnen, das Gehalt jedes Mitarbeiters neben dem Durchschnittsgehalt seiner Abteilung anzuzeigen.

Die erwartete Ausgabe ist:

+-------------+------------+------------+----------+--------------+
| employee_id | first_name | department | salary   | avg_salary   |
+-------------+------------+------------+----------+--------------+
|           1 | John       | Sales      | 60000.00 | 57500.000000 |
|           2 | Jane       | Marketing  | 75000.00 | 72500.000000 |
|           3 | Robert     | Sales      | 55000.00 | 57500.000000 |
|           4 | Emily      | IT         | 90000.00 | 90000.000000 |
|           5 | Michael    | Marketing  | 70000.00 | 72500.000000 |
+-------------+------------+------------+----------+--------------+
5 rows in set (0.00 sec)

Sie können die Ergebnisse manuell überprüfen. Beispielsweise beträgt das Durchschnittsgehalt für die Abteilung 'Sales' (60000 + 55000) / 2 = 57500, was der Ausgabe entspricht. Dies bestätigt, dass Ihre Abfrage korrekt funktioniert.

Sie haben erfolgreich eine CTE mit einer Tabelle verknüpft. Sie können die MySQL-Shell nun verlassen.

exit;

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie Common Table Expressions (CTEs) in MySQL effektiv nutzen. Sie haben mit der Einrichtung einer Datenbank und von Tabellen begonnen und sind dann zur Erstellung verschiedener Arten von CTEs übergegangen.

Sie haben gelernt:

• Eine einfache CTE mit der WITH-Klausel zu definieren, um Daten zu filtern und die Lesbarkeit von Abfragen zu verbessern.
• Eine rekursive CTE zu erstellen, um hierarchische Daten aus einer Tabelle zu durchlaufen und anzuzeigen.
• Eine CTE mit einer Tabelle zu verknüpfen, um aggregierte Ergebnisse mit detaillierten Daten auf Zeilenebene für komplexere Analysen zu kombinieren.

Diese Fähigkeiten sind grundlegend für das Schreiben von sauberen, wartbaren und leistungsstarken SQL-Abfragen, insbesondere bei der Arbeit mit komplexer Logik oder Datenstrukturen.