Level 18: Fensterfunktionen: laufende Summen und Durchschnitte

Lektion

In Level 17 ordneten die Fensterfunktionen die Zeilen. Die üblichen Aggregate (SUM, AVG, COUNT, MIN, MAX) werden ebenfalls zu Fensterfunktionen, wenn man ihnen OVER hinzufügt. Der Unterschied zu GROUP BY: Alle Zeilen bleiben erhalten, und jede erhält das Ergebnis des über ihr Fenster berechneten Aggregats.

Mit PARTITION BY allein ist das Fenster die ganze Gruppe: SUM(amount) OVER (PARTITION BY region) zeigt bei jedem Verkauf den Umsatz seiner Region, 2600 für den Norden und 2450 für den Süden. So kann man jede Zeile in derselben Zeile mit ihrer Gruppe vergleichen: Abstand zum Durchschnitt, Anteil am Gesamtwert…

OVER () mit leeren Klammern nimmt die ganze Tabelle als Fenster. Das ist praktisch für einen Anteil am Gesamtwert: amount * 100.0 / SUM(amount) OVER () liefert den Prozentsatz, den jeder Verkauf ausmacht. Schreibe 100.0, um die Ganzzahldivision zu vermeiden.

Mit einem ORDER BY in OVER wird die Berechnung kumulativ: Das Fenster reicht von der ersten Zeile bis zur aktuellen Zeile. SUM(amount) OVER (PARTITION BY seller ORDER BY month) ergibt für Ana 300 im Januar, 750 im Februar und 1150 im März: Das ist eine laufende Summe, wie ein Kontostand. Dank PARTITION BY beginnt die laufende Summe bei jedem Verkäufer wieder bei null.

Vorsicht bei Gleichständen: Die laufende Summe schreitet nach Sortierwert voran, nicht nach Zeile. Haben zwei Zeilen im ORDER BY denselben Wert, zum Beispiel zwei Transaktionen am selben Tag, werden sie zusammen addiert und erhalten dieselbe laufende Summe. Für eine streng zeilenweise laufende Summe füge eine Spalte zum Auflösen des Gleichstands hinzu (ORDER BY made_on, id). Level 19 erklärt diesen Mechanismus und zeigt ihn an einem Beispiel (Übung 19.4).

Schließlich kann man GROUP BY und Fenster kombinieren, da das Fenster nach der Gruppierung berechnet wird: SUM(SUM(amount)) OVER () addiert die Umsätze aller Verkäufer zum Gesamtumsatz.

Syntax

SELECT col,
       SUM(x) OVER (PARTITION BY g ORDER BY d) AS laufend,
       x * 100.0 / SUM(x) OVER () AS pct
FROM meine_tabelle;

Kommentiertes Beispiel

SELECT seller,
       month,
       amount,
       SUM(amount) OVER (PARTITION BY seller ORDER BY month) AS running_total
FROM sales;

Für jeden Verkäufer wächst die laufende Summe Monat für Monat und beginnt beim nächsten Verkäufer wieder bei null.

Ergebnis des Beispiels

Das Wichtigste

• SUM/AVG/COUNT + OVER: ein Aggregat auf jeder Zeile, ohne Gruppierung.
• ORDER BY in OVER: laufende Summe.
• OVER (): die ganze Tabelle.

Häufige Fallen

• PARTITION BY vergessen ergibt eine laufende Summe über die ganze Tabelle statt über jede Gruppe.
• Ganzzahlige Division: Schreibe 100.0 für einen Prozentsatz.
• Zwei Zeilen mit demselben Datum bekommen dieselbe laufende Summe: Das ist der Standardrahmen RANGE.

Die 5 Übungen des Levels

1. Geführt · Zeige jeden Verkauf (Verkäufer, Region, Betrag) und in derselben Zeile die Summe seiner Region (region_total). (Tabelle: sales)
2. Training · Zeige für Konto 1 jede Transaktion (Datum, Betrag) und den Kontostand nach jeder Buchung, in zeitlicher Reihenfolge. (Tabelle: transactions)
3. Training · Zeige jede Note (Schüler, Fach, Note) mit dem Durchschnitt ihres Fachs, auf eine Nachkommastelle gerundet (subject_avg). (Tabelle: grades)
4. Training · Zeige für jede Stadt und jeden Tag den Regen des Tages und den seit Monatsbeginn in dieser Stadt aufsummierten Regen (cum_rain). (Tabelle: readings)
5. Herausforderung · Zeige die Summe jedes Verkäufers und seinen Anteil an der Gesamtsumme in Prozent, auf eine Nachkommastelle gerundet (pct). (Tabelle: sales)

In SpeedQL wird jede Abfrage sofort geprüft: Ihr Ergebnis wird mit dem erwarteten verglichen, dann läuft die Abfrage noch einmal auf einer versteckten Kontrolldatenbank. Zu jeder Übung gibt es schriftliche Hinweise, die du nur bei Bedarf aufrufst.