Jetzt bist du bereit, in die Welt der praktischen Beispiele einzutauchen, um zu sehen, wie das Ganze bei echten Aufgaben funktioniert!
Beispiel: Verkaufs-Ranking nach Regionen berechnen
Stell dir vor, wir haben eine Tabelle sales, die Verkaufsdaten aus verschiedenen Regionen enthält. Wir müssen das Verkaufs-Ranking für jede Region bestimmen.
| id | region | sales_amount |
|---|---|---|
| 1 | North | 5000 |
| 2 | North | 3000 |
| 3 | North | 7000 |
| 4 | South | 2000 |
| 5 | South | 4000 |
| 6 | East | 8000 |
| 7 | East | 6000 |
Aufgabe: Finde das RANK der Verkäufe pro Region
SELECT
region,
sales_amount,
RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
FROM
sales;
Ergebnis:
| region | sales_amount | sales_rank |
|---|---|---|
| North | 7000 | 1 |
| North | 5000 | 2 |
| North | 3000 | 3 |
| South | 4000 | 1 |
| South | 2000 | 2 |
| East | 8000 | 1 |
| East | 6000 | 2 |
Beachte, dass wir PARTITION BY region genutzt haben, um die Ränge getrennt für jede Region zu berechnen. Hätten wir PARTITION BY nicht verwendet, würde das Ranking global für die ganze Tabelle berechnet werden.
Beispiel: Kumulative Summe des Umsatzes berechnen
Jetzt lass uns die Tabelle transactions nehmen, um die kumulierte Summe des Umsatzes für jeden Kunden zu berechnen.
| id | customer_id | purchase_date | amount |
|---|---|---|---|
| 1 | 101 | 2023-01-01 | 100 |
| 2 | 101 | 2023-01-03 | 50 |
| 3 | 102 | 2023-01-02 | 200 |
| 4 | 101 | 2023-01-05 | 150 |
| 5 | 102 | 2023-01-04 | 100 |
Aufgabe: Berechne die kumulierte Summe für jeden Kunden
SELECT
customer_id,
purchase_date,
amount,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS cumulative_sum
FROM
transactions;
Ergebnis:
| customer_id | purchase_date | amount | cumulative_sum |
|---|---|---|---|
| 101 | 2023-01-01 | 100 | 100 |
| 101 | 2023-01-03 | 50 | 150 |
| 101 | 2023-01-05 | 150 | 300 |
| 102 | 2023-01-02 | 200 | 200 |
| 102 | 2023-01-04 | 100 | 300 |
Hier ist der Knackpunkt — wir nutzen ORDER BY purchase_date innerhalb von OVER(), damit die kumulierte Summe chronologisch berechnet wird.
Beispiel: Daten in Quartile aufteilen
Stell dir vor, wir haben eine Tabelle students mit Namen und Testergebnissen. Wir wollen die Schüler in 4 Gruppen nach ihren Ergebnissen aufteilen.
| id | name | test_score |
|---|---|---|
| 1 | Alice | 85 |
| 2 | Bob | 95 |
| 3 | Charlie | 75 |
| 4 | Diana | 88 |
| 5 | Edward | 65 |
| 6 | Fiona | 70 |
Aufgabe: Teile die Studenten in 4 Gruppen mit NTILE()
SELECT
name,
test_score,
NTILE(4) OVER (ORDER BY test_score DESC) AS quartile
FROM
students;
Das Ergebnis der Abfrage sieht so aus:
| name | test_score | quartile |
|---|---|---|
| Bob | 95 | 1 |
| Diana | 88 | 1 |
| Alice | 85 | 2 |
| Charlie | 75 | 3 |
| Fiona | 70 | 3 |
| Edward | 65 | 4 |
NTILE(4) teilt die Daten in 4 Gruppen. Die Schüler mit den besten Ergebnissen landen in der ersten Gruppe, die mit den schlechtesten — in der letzten.
Beispiel: Zeitreihenanalyse
In der Tabelle site_visits werden die täglichen Website-Besuche gespeichert. Wir wollen die Differenz der Besuche zwischen den Tagen für jede Website berechnen.
| site_id | visit_date | visits |
|---|---|---|
| 1 | 2023-01-01 | 100 |
| 1 | 2023-01-02 | 120 |
| 1 | 2023-01-03 | 110 |
| 2 | 2023-01-01 | 50 |
| 2 | 2023-01-02 | 60 |
| 2 | 2023-01-03 | 70 |
Unsere Aufgabe — die Differenz der Besuche zwischen den Tagen berechnen
SELECT
site_id,
visit_date,
visits,
visits - LAG(visits) OVER (PARTITION BY site_id ORDER BY visit_date) AS visit_diff
FROM
site_visits;
Ergebnis:
| site_id | visit_date | visits | visit_diff |
|---|---|---|---|
| 1 | 2023-01-01 | 100 | NULL |
| 1 | 2023-01-02 | 120 | 20 |
| 1 | 2023-01-03 | 110 | -10 |
| 2 | 2023-01-01 | 50 | NULL |
| 2 | 2023-01-02 | 60 | 10 |
| 2 | 2023-01-03 | 70 | 10 |
Die Funktion LAG() holt den Wert aus der vorherigen Zeile. Wenn es keine vorherige Zeile gibt, ist das Ergebnis NULL. Mehr dazu erfährst du in den nächsten Vorlesungen :P
GO TO FULL VERSION