Wichtige Window Functions für Analytics

Bevor wir loslegen, stell dir vor, du arbeitest mit einer Tabelle mit tausenden Verkaufszeilen. Deine Aufgabe: Finde raus, wer in jeder Kategorie der Top-Verkäufer ist, wer Zweiter ist usw. Oder du willst einfach alle Zeilen in deinem Ergebnis durchnummerieren, um die Reihenfolge zu sehen. All das geht easy mit Window Functions.

Window Functions sind SQL-Funktionen, die mit einem Teilbereich von Zeilen (nennen wir ihn "Fenster") aus deinem Datensatz arbeiten. Im Gegensatz zu Aggregatfunktionen, die Zeilen zu einer einzigen zusammenfassen (wie SUM() oder AVG()), lassen Window Functions die Zeilen stehen und hängen berechnete Werte dran.

Unterschied zu Aggregatfunktionen

Aggregatfunktionen "quetschen" die Daten zusammen, indem sie Zeilen gruppieren:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Das Ergebnis: nur ein paar Zeilen, je nachdem wie viele Abteilungen es gibt.

Vergleichen wir das mit einer Window Function – hier bleiben die Zeilen erhalten, aber es kommt ein neues Feld dazu, zum Beispiel ROW_NUMBER():

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

Hier bekommst du alle Zeilen, aber mit einer zusätzlichen Spalte rank_within_department, wo jeder Mitarbeiter eine Nummer innerhalb seiner Abteilung bekommt.

Wichtige Window Functions

Syntax von OVER()

Der wichtigste Teil jeder Window Function ist das magische Wort OVER(). Damit bestimmst du, mit welchem "Fenster" die Funktion arbeitet. Innerhalb von OVER() kannst du Gruppierung (PARTITION BY) und/oder Sortierreihenfolge (ORDER BY) angeben.

Allgemeiner Syntax:

<window_function>() OVER (
   [PARTITION BY <gruppe>] 
   [ORDER BY <reihenfolge>]
)

Komponenten:

• PARTITION BY: Teilt die Zeilen in Gruppen. Zum Beispiel: "teile die Daten nach Abteilungen auf".
• ORDER BY: Gibt die Sortierreihenfolge an. Zum Beispiel: "sortiere Mitarbeiter nach Gehalt absteigend".

Funktion ROW_NUMBER()

Die Funktion ROW_NUMBER() nummeriert die Zeilen, beginnend bei 1, innerhalb des angegebenen "Fensters". Das ist praktisch, wenn du einfach eine Zeilennummer in einer temporären Tabelle brauchst oder die Reihenfolge einer Zeile bestimmen willst.

Beispiel. Tabelle sales (Verkäufe):

Query:

SELECT seller_name, product_category, revenue,
       ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS row_number
FROM sales;

Ergebnis:

Wie das funktioniert:

1. Die Daten werden nach product_category gruppiert.
2. Jede Gruppe wird nach revenue (absteigend) sortiert.
3. Die Zeilen in jeder Gruppe bekommen eine fortlaufende Nummer.

Funktion RANK()

Die Funktion RANK() wird zum Rangieren von Zeilen genutzt. Im Gegensatz zu ROW_NUMBER() berücksichtigt sie gleiche Werte und überspringt Ränge, wenn Werte gleich sind.

Beispiel:

SELECT seller_name, product_category, revenue,
       RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
FROM sales;

Ergebnis:

Funktion DENSE_RANK()

DENSE_RANK() ist ähnlich wie RANK(), aber mit einem Unterschied: Sie überspringt keine Ränge, wenn es gleiche Werte gibt.

Beispiel. Wir fügen einen Verkauf mit gleichem Umsatz hinzu:

Query:

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Ergebnis:

Beispiele: Zeilennummerierung

Aufgabe: Nummeriere alle Bestellungen in der Tabelle orders, sortiert nach Datum.

SELECT order_id, customer_name, order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS order_number
FROM orders;

Ergebnis: Du bekommst eine Liste der Bestellungen mit Nummerierung in der Reihenfolge ihrer Ausführung.

Beispiele: Top-3 Verkäufer in jeder Kategorie

Aufgabe: Finde die drei besten Verkäufer in jeder Produktkategorie.

WITH ranked_sales AS (
    SELECT seller_name, product_category, revenue,
           RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
    FROM sales
)
SELECT seller_name, product_category, revenue
FROM ranked_sales
WHERE rank <= 3;

Beispiele: Gleiche Werte erkennen

Aufgabe: Finde heraus, ob es Verkäufer mit gleichem Umsatz in jeder Kategorie gibt.

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Jetzt kannst du die Ränge sehen, wo gleiche Werte "kleben bleiben".