Aufgabe 03 - Fensterfunktionen

Aufgabe 03 - Fensterfunktionen

Worum geht es?

In dieser Übung wird ausschließlich mit Fensterfunktionen gearbeitet (OVER ( [PARTITION BY] [ORDER BY] [FRAME] )) an der Northwind-Datenbank (siehe Kapitel 1 - Komplexe Abfragen). Die Aufgaben steigern sich von der laufenden Summe bis zu einer Saison-Trend-Zerlegung, die selbst erfahrene Analysten fordert.

Was Sie dafür brauchen

• Northwind wie in Aufgabe 01 importiert.
• Fensterfunktionen: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, SUM/AVG/STDDEV OVER, Frames (ROWS BETWEEN ...).

Welche Kompetenzen Sie erwerben und zeigen

• Sie berechnen laufende Summen und Zeilennummern je Partition.
• Sie bilden Ranglisten und Vormonatsvergleiche.
• Sie nutzen Frames für gleitende Durchschnitte und Kohorten.
• Sie zerlegen Zeitreihen in Trend und Saisonalität rein mit Fensterlogik.

Pädagogische Einordnung

• Reproduktion: laufende Summe und Zeilennummer anwenden (Teil A).
• Reorganisation und Transfer: Ranking und Vormonatsvergleich einsetzen (Teil B).
• Reflexion, Problemlösung und Urteilsbildung: Kohorten, Ausreißer und Saisonmuster modellieren (Teile C und D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Bestellwert je Bestellung durchgehend: Summe der Positionen unit_price * quantity * (1 - discount).

Teil A - Einstieg

1. Laufende Summe je Kunde: je Kunde die Bestellungen chronologisch, mit order_id, order_date, Bestellwert, der kumulativen Summe je Kunde (SUM(...) OVER (PARTITION BY customer_id ORDER BY order_date)) und der Zeilennummer (ROW_NUMBER()).

Teil B - Ranking und Zeitvergleich

1. Top-3-Produkte je Kategorie: je Produkt den Gesamtumsatz, je Kategorie den Rang (RANK oder DENSE_RANK) und nur die Top 3 je Kategorie (mit Gleichständen).
2. Monat-zu-Monat je Kunde: je Kunde und Monat den Umsatz und mit LAG den Vormonat sowie die absolute und prozentuale Veränderung.

Teil C - Frames und Kohorten

1. Wiederbeschaffungsrhythmus je Produkt: mit LAG(order_date) je Produkt den Abstand in Tagen und einen gleitenden Durchschnitt der letzten fünf Abstände (ROWS BETWEEN 4 PRECEDING AND CURRENT ROW).
2. Onboarding-Kohorte: je Kunde die erste Bestellung (FIRST_VALUE(order_date)), die Tage seit Erstkauf, die kumulierte Umsatzsumme und eine Markierung „Onboarding-Phase” für die ersten 90 Tage.
3. Verkäuferleistung je Quartal: je Mitarbeiter und Quartal Umsatz, Team-Gesamtumsatz des Quartals (Fenster über alle Mitarbeiter), prozentualer Anteil und Rang im Quartal.

Teil D - Expertenteil: Ausreißer und Saison-Trend-Zerlegung

1. Preis-Ausreißer (Z-Score): je Produkt Mittelwert und Standardabweichung der tatsächlich verrechneten Positionspreise (unit_price * (1 - discount)) als Fensterfunktionen, daraus je Position den Z-Score (wert - mittel) / stddev und eine Markierung möglicher Ausreißer (|Z| > 2).
2. Saison-Trend-Zerlegung je Kategorie:
   • Monatsumsätze je Kategorie über alle Jahre bilden (in der Endabfrage nur Fensterfunktionen).
   • Eine zentrierte 12-Monats-Gleitmittelreihe als Trend berechnen (ROWS BETWEEN 6 PRECEDING AND 6 FOLLOWING).
   • Die Saisonabweichung je Monat als Monatsumsatz - Trend bestimmen und je Kategorie mit einem weiteren Fenster so normieren, dass der Mittelwert der Saisonfaktoren nahe 0 liegt.
   • Je Kategorie die zwei Monate mit der stärksten positiven saisonalen Abweichung ausgeben (mit Gleichständen).
3. Reflexion: In drei bis vier Sätzen erklären, warum diese Auswertung mit Fensterfunktionen möglich ist, ohne die Detailzeilen zu verlieren, und wo ein GROUP BY an seine Grenze käme.

Wissenscheck

1. Worin unterscheiden sich RANK, DENSE_RANK und ROW_NUMBER?
2. Was bewirkt ein ROWS BETWEEN 4 PRECEDING AND CURRENT ROW-Frame?
3. Wie berechnet man einen Z-Score mit Fensterfunktionen?
4. Warum behält eine Fensterfunktion die Detailzeilen, ein GROUP BY aber nicht?
5. Wozu dient ein zentrierter Frame (n PRECEDING AND n FOLLOWING)?

Abgabe

• Eine Dokumentation mit allen SQL-Abfragen und Erläuterungen zu den Ergebnissen, besonders zur Saison-Zerlegung aus Teil D.

---

HTL Villach, 2025-2026,
https://www.htl-villach.at