Menu
CoddyTech

Aggregationsfunktionen

Teil des Abschnitts Grundlagen der SQL-Journey von Coddy. Lektion 68 von 72.

Mit OVER (ORDER BY ...) werden Aggregatfunktionen wie AVG() oder MAX() über die Zeilen bis einschließlich der aktuellen Zeile berechnet, einschließlich der Zeilen, die beim ORDER BY-Wert mit ihr gleichauf liegen. Ohne ORDER BY innerhalb von OVER werden sie über alle Zeilen berechnet, sodass jede Zeile denselben Wert erhält.
Zum Beispiel könnten wir den maximalen Umsatz berechnen, den wir bis zum Ende jeder Periode erzielt haben:

MonatUmsatzRegion
440East
520East
660West
755West
861East
SELECT month, revenue, MAX(revenue) OVER (ORDER BY month ASC) as max_revenue
from table1

Dies wird zurückgeben:

MonatUmsatzmax_revenue
44040
52040
66060
75560
86161

Für die Monate 4 und 5 beträgt der maximale Umsatz 40, für die Monate 6 und 7 beträgt er 60 und für Monat 8 beträgt er 61. Das liegt daran, dass der alte Umsatz verworfen und der bisher größte verwendet wird, sobald ein neuer, höherer Umsatz gefunden wird.

Für die Funktion AVG() sieht es so aus:

SELECT month, revenue, AVG(revenue) OVER (ORDER BY month ASC) as avg_revenue
from table1
MonatUmsatzavg_revenue
44040
52030
66040
75543.75
86147.2

Wir können unsere Berechnungen auch mithilfe von PARTITION BY nach bestimmten Kategorien gruppieren. Zum Beispiel:

SELECT month, revenue, region,
MAX(revenue) OVER (PARTITION BY region ORDER BY month ASC) as max_revenue
FROM table1

Würde uns Folgendes ergeben:

MonatUmsatzRegionmax_revenue
440East40
520East40
660West60
755West60
861East61

Jetzt wird das Maximum für jede Region separat berechnet. Die Regionen East und West behalten unabhängig voneinander jeweils ihr eigenes laufendes Maximum.

challenge icon

Aufgabe

Einfach

Verfügbare Tabellen und Spalten:

  • books: book, page, words

Zeige für jedes Buch den Buchtitel, die Gesamtzahl der Seiten, die Anzahl der Wörter auf jeder Seite und berechne:

  1. Die maximale Anzahl an Wörtern auf einer beliebigen Seite dieses Buches
  2. Die durchschnittliche Anzahl der Wörter pro Seite im gesamten Buch
  3. Die Differenz zwischen der Wortanzahl auf der aktuellen Seite und der durchschnittlichen Wortanzahl pro Seite des Buches

Gib die Ergebnisse nach Buch und Seitenzahl sortiert aus.

Gib die folgenden Spalten zurück:

  • book
  • page
  • words
  • max_words_in_book
  • avg_words_per_page
  • diff_from_avg

Probier es selbst

quiz iconTeste dich selbst

Diese Lektion enthält ein kurzes Quiz. Starte die Lektion, um es zu beantworten und deinen Fortschritt zu speichern.

Alle Lektionen in Grundlagen

Übe selbstständig: SQL-Playground