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:
| Monat | Umsatz | Region |
| 4 | 40 | East |
| 5 | 20 | East |
| 6 | 60 | West |
| 7 | 55 | West |
| 8 | 61 | East |
SELECT month, revenue, MAX(revenue) OVER (ORDER BY month ASC) as max_revenue
from table1Dies wird zurückgeben:
| Monat | Umsatz | max_revenue |
| 4 | 40 | 40 |
| 5 | 20 | 40 |
| 6 | 60 | 60 |
| 7 | 55 | 60 |
| 8 | 61 | 61 |
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| Monat | Umsatz | avg_revenue |
| 4 | 40 | 40 |
| 5 | 20 | 30 |
| 6 | 60 | 40 |
| 7 | 55 | 43.75 |
| 8 | 61 | 47.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 table1Würde uns Folgendes ergeben:
| Monat | Umsatz | Region | max_revenue |
| 4 | 40 | East | 40 |
| 5 | 20 | East | 40 |
| 6 | 60 | West | 60 |
| 7 | 55 | West | 60 |
| 8 | 61 | East | 61 |
Jetzt wird das Maximum für jede Region separat berechnet. Die Regionen East und West behalten unabhängig voneinander jeweils ihr eigenes laufendes Maximum.
Aufgabe
EinfachVerfü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:
- Die maximale Anzahl an Wörtern auf einer beliebigen Seite dieses Buches
- Die durchschnittliche Anzahl der Wörter pro Seite im gesamten Buch
- 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:
bookpagewordsmax_words_in_bookavg_words_per_pagediff_from_avg
Probier es selbst
Diese Lektion enthält ein kurzes Quiz. Starte die Lektion, um es zu beantworten und deinen Fortschritt zu speichern.
Alle Lektionen in Grundlagen
4Weitere Schlüsselwörter
Das IN-SchlüsselwortDas BETWEEN-SchlüsselwortDas LIKE-SchlüsselwortDas AS-SchlüsselwortRückblick – Handymodelle7Datumsangaben
Datumsangaben behandeln – Teil 1Datumsangaben behandeln – Teil 2Datumsangaben behandeln – Teil 32Bedingungen
Grundlagen der BedingungenDas Schlüsselwort ANDDas Schlüsselwort ORDas Schlüsselwort NOTMehrere kombinierte BedingungenKlammernBoolesche Werte5Arithmetische Operationen
Mathematische OperatorenMathematische SpaltenDie Modulo-OperationDie ROUND()-Funktion3Spezifisches Rückgabeformat
NULL-WerteErgebnisse sortieren Teil 1Ergebnisse sortieren Teil 2Zusammenfassung - Cyber Security FirmAnzahl der Datensätze begrenzenZusammenfassung - Vehicle Factory6Einführungsaufgaben
Wiederholung – ParlamentswahlWiederholung – Festnahme eines Straftäters durch die PolizeiWiederholung – Getränkebehälter an der BarWiederholung – Neue Spalten erstellen9Mehrere Tabellen
Grundlegender Join Teil 1Grundlegender Join Teil 2Rückblick – JoinSelf-JoinRückblick – Self-JoinUnionAbfragen vereinfachen, WITH-SchlüsselwortRückblick – WITH-AbfragenRückblick – Immobilienauftragnehmer12Fensterfunktionen Teil 2
RANK- & DENSE_RANK-FunktionenWiederholung – RANK & DENSE_RANKNTILE-FunktionAggregationsfunktionenROWS- & RANGE-KriteriumÜbe selbstständig: SQL-Playground