In dieser Vorlesung machen wir echte Query-Magie! Wir bauen ein paar Beispiele mit mehreren CTEs, um zu zeigen, wie sie miteinander kombiniert werden können, um komplexe und mehrstufige Abfragen zu bauen. Diese Beispiele sind im echten Leben super nützlich, vor allem bei komplizierten Analyseaufgaben.
Beispiel 1: Analyse der Studentenleistungen
Stell dir vor, wir haben eine Uni-Datenbank mit drei Tabellen:
Tabelle students:
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
student_name TEXT NOT NULL
);
Tabelle grades:
CREATE TABLE grades (
grade_id SERIAL PRIMARY KEY,
student_id INT REFERENCES students(student_id),
course_id INT NOT NULL,
grade NUMERIC(3, 1) NOT NULL
);
Tabelle courses:
CREATE TABLE courses (
course_id SERIAL PRIMARY KEY,
course_name TEXT NOT NULL
);
Aufgabe: Hol dir eine Liste der Studenten, deren Durchschnittsnote über 85 liegt, zusammen mit ihrem Durchschnitt und den Namen der Kurse, die sie besuchen.
Query:
WITH high_achievers AS (
-- Finde Studenten mit hohem Durchschnitt
SELECT
student_id,
AVG(grade) AS avg_grade
FROM grades
GROUP BY student_id
HAVING AVG(grade) > 85
),
student_courses AS (
-- Finde Kurse, für die jeder Student eingeschrieben ist
SELECT
s.student_id,
c.course_name
FROM grades g
JOIN courses c ON g.course_id = c.course_id
JOIN students s ON g.student_id = s.student_id
)
-- Ergebnisse zusammenführen
SELECT
s.student_name,
ha.avg_grade,
sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN students s ON s.student_id = ha.student_id;
Erklärung:
- Im ersten CTE (
high_achievers) berechnen wir den Durchschnitt für jeden Studenten und wählen die aus, deren Noten über 85 liegen. - Im zweiten CTE (
student_courses) ordnen wir Studenten ihren Kursen zu. - Im Hauptquery kombinieren wir die Daten aus beiden CTEs, um die Liste der Studenten, ihren Durchschnitt und die Kurse zu bekommen, die sie besuchen.
Beispiel 2: Verkaufsreport für einen Online-Shop
Stell dir vor, wir arbeiten mit einem Online-Shop und haben folgende Tabellen:
Tabelle orders (Bestellungen):
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount NUMERIC(10, 2) NOT NULL
);
Tabelle customers (Kunden):
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name TEXT NOT NULL
);
Tabelle order_items (Artikel in Bestellungen):
CREATE TABLE order_items (
order_item_id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(order_id),
product_id INT NOT NULL,
quantity INT NOT NULL,
price NUMERIC(10, 2) NOT NULL
);
Aufgabe: Erstelle einen Report, der für jeden Kunden zeigt:
- Die Gesamtanzahl der Bestellungen.
- Die Gesamtsumme aller Bestellungen im letzten Monat.
- Eine Liste aller einzigartigen Produkte, die er gekauft hat.
Query:
WITH recent_orders AS (
-- Wähle Bestellungen aus dem letzten Monat
SELECT
order_id,
customer_id,
total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
),
customer_summary AS (
-- Zähle Gesamtanzahl und Summe für jeden Kunden
SELECT
ro.customer_id,
COUNT(ro.order_id) AS total_orders,
SUM(ro.total_amount) AS total_spent
FROM recent_orders ro
GROUP BY ro.customer_id
),
customer_products AS (
-- Wähle einzigartige Produkte, die jeder Kunde gekauft hat
SELECT DISTINCT
ro.customer_id,
oi.product_id
FROM recent_orders ro
JOIN order_items oi ON ro.order_id = oi.order_id
)
-- Ergebnisse zusammenführen
SELECT
c.customer_name,
cs.total_orders,
cs.total_spent,
ARRAY_AGG(cp.product_id) AS purchased_products
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
JOIN customer_products cp ON cp.customer_id = c.customer_id
GROUP BY c.customer_name, cs.total_orders, cs.total_spent;
Erklärung:
- Im ersten CTE (
recent_orders) wählen wir Bestellungen aus dem letzten Monat. - Im zweiten CTE (
customer_summary) berechnen wir die Gesamtanzahl der Bestellungen und die Gesamtsumme für jeden Kunden. - Im dritten CTE (
customer_products) holen wir die einzigartigen Produkte, die jeder Kunde gekauft hat. - Im finalen Query kombinieren wir die Daten und nutzen
ARRAY_AGG(), um die Liste der einzigartigen Produkte zu bauen.
Beispiel 3: Analyse der Mitarbeiterhierarchie
Wir haben eine Mitarbeitertabelle:
Tabelle employees:
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name TEXT NOT NULL,
manager_id INT NULL
);
Aufgabe: Baue die Hierarchie der Mitarbeiter, beginnend beim Geschäftsführer. Zeige die Ebene jedes Mitarbeiters in der Hierarchie an.
Query:
WITH RECURSIVE employee_hierarchy AS (
-- Starte mit Mitarbeitern ohne Manager (Geschäftsführer)
SELECT
employee_id,
employee_name,
manager_id,
1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Füge alle Untergebenen der aktuellen Ebene hinzu
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- Hierarchie ausgeben
SELECT
employee_id,
employee_name,
manager_id,
level
FROM employee_hierarchy
ORDER BY level, employee_id;
- Im rekursiven Query starten wir mit dem Geschäftsführer (Mitarbeiter ohne Manager).
- In jedem Schritt fügen wir die Untergebenen der aktuellen Ebene hinzu und erhöhen das Feld
levelum eins. - Im Hauptquery wählen wir die komplette Hierarchie, sortiert nach Ebene und Mitarbeiter-ID.
Nützliche Tipps und typische Fehler
- Zu viele CTEs: Nutze sie nicht, wenn ein Subquery reicht. CTEs können manchmal die Performance verschlechtern, weil sie Daten materialisieren.
- CTE-Namen: Gib deinen CTEs verständliche und kurze Namen, damit die Queries lesbar bleiben.
- Ausführungsreihenfolge: Denk dran, dass CTEs strikt in der Reihenfolge ihrer Deklaration ausgeführt werden.
- Gruppierung von Daten: Nutze
GROUP BYnur, wenn es wirklich nötig ist, um unnötige Operationen zu vermeiden.
Alle diese Beispiele zeigen, wie man CTEs nutzen kann, um komplexe Aufgaben in Schritte zu teilen und die Lesbarkeit und Wartbarkeit von Queries zu verbessern. Jetzt bist du mit den Tools ausgestattet, um auch schwierige Analyseaufgaben mit PostgreSQL zu lösen!
GO TO FULL VERSION