Bevor Sie tiefer in dieses Kapitel eintauchen, lesen Sie bitte unser vorheriges Kapitel, in dem wir die GROUP BY-Klausel ausführlich besprechen. Aggregationsfunktionen und GROUP BY gehen Hand in Hand, da sie häufig zusammen verwendet werden, um Daten zu gruppieren und Berechnungen für diese Gruppen durchzuführen.
Aggregationsfunktionen in SQL ermöglichen es Ihnen, Berechnungen für eine Reihe von Werten durchzuführen, um einen einzelnen Wert zurückzugeben. Diese Funktionen werden häufig mit der GROUP BY-Anweisung verwendet, die Zeilen mit denselben Werten in Zusammenfassungszeilen gruppiert. Bevor Sie sich mit Aggregationsfunktionen befassen, ist es wichtig, die GROUP BY-Anweisung zu verstehen, da sie häufig zusammen verwendet werden.
SELECT
COUNT(*) AS total_trips,
FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`
Diese Abfrage zählt die Gesamtzahl der Zeilen in der angegebenen Tabelle, was zu einem einzelnen Wert führt, der die Gesamtzahl der Datensätze in der Datenmenge darstellt. Zum Beispiel die bikeshare_trips Die Tabelle enthält insgesamt 1.947.417 Zeilen.
Als Nächstes berechnen wir den Durchschnittswert einer bestimmten Spalte mithilfe der AVG-Funktion.
SELECT
AVG(duration_sec) AS avg_duration_sec
FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`
Diese Abfrage berechnet den Durchschnittswert aller Einträge in der Dauer_Sek Spalte. Basierend auf dem Ergebnis beträgt die durchschnittliche Reisedauer ungefähr 1.008 Sekunden, was etwa 17 Minuten entspricht. Solche allgemeinen Erkenntnisse können mithilfe der AVG-Funktion effektiv gewonnen werden.
Lassen Sie uns nun eine Spalte in diese Abfrage einführen und die GROUP BY-Klausel damit verwenden.
SELECT
start_station_name,
AVG(duration_sec) AS avg_duration_sec
FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`
GROUP BY 1
Diese Query berechnet die durchschnittliche Reisedauer (Dauer_Sek) für jeden Name der Startstation. Indem Sie die Ergebnisse nach der Startstation gruppieren, können Sie die durchschnittliche Dauer der Fahrten ermitteln, die von jeder Station aus gestartet wurden.
Lassen Sie uns nun herausfinden, welche Station die längste durchschnittliche Dauer hatte, indem wir die ORDER BY-Klausel verwenden, um die Ergebnisse zu sortieren.
SELECT
start_station_name,
AVG(duration_sec) AS avg_duration_sec
FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`
GROUP BY 1
ORDER BY 2 DESCDiese Abfrage berechnet die durchschnittliche Reisedauer für jede Startstation und sortiert die Ergebnisse dann in absteigender Reihenfolge nach der durchschnittlichen Dauer (durchschnittliche_Dauer in Sekunden). Auf diese Weise wird die Station mit der längsten durchschnittlichen Reisedauer — wie University und Emerson — ganz oben in der Liste angezeigt.
Die durchschnittliche Dauer liefert zwar nützliche Informationen, aber sie könnte verzerrt sein, wenn eine Station wie University und Emerson nur eine außergewöhnlich lange Fahrt hätten. Diese Einschränkung bedeutet, dass wir nicht wissen, wie häufig solche Ereignisse aufgetreten sind. Um ein klareres Bild der Daten zu erhalten, können wir die SUM-Funktion verwenden, um alle Reisedauern für jede Station zu addieren, und die COUNT-Funktion verwenden, um zu ermitteln, wie viele Fahrten von jeder Station aus gestartet wurden.
SELECT
start_station_name,
AVG(duration_sec) AS avg_duration_sec,
SUM(duration_sec) AS sum_duration_sec,
COUNT(trip_id) AS count_trip
FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`
GROUP BY 1
ORDER BY 2 DESC
Hinweis: Die Funktionen SUM und AVG können nur auf numerische Datentypen wie Ganzzahlen und Gleitkommazahlen angewendet werden. Sie können nicht mit den Datentypen Zeichenfolge oder Datum verwendet werden.
Diese Query berechnet die durchschnittliche Reisedauer, die Gesamtdauer aller Fahrten und die Anzahl der Fahrten für jede Startstation. Durch die Sortierung der Ergebnisse auf der Grundlage der Gesamtdauer (Gesamtdauer_Sek), können wir besser verstehen, welche Stationen die höchste kumulierte Reisedauer haben und wie oft Fahrten von jeder Station aus beginnen. Dieser Ansatz bietet einen umfassenderen Überblick über die Daten, die über die durchschnittliche Dauer hinausgehen.
Die Funktionen MIN und MAX helfen dabei, die kleinsten und größten Werte in einem Datensatz zu identifizieren.
SELECT
start_station_name,
AVG(duration_sec) AS avg_duration_sec,
SUM(duration_sec) AS sum_duration_sec,
COUNT(trip_id) AS count_trip,
MIN(duration_sec) AS min_duration_sec,
MAX(duration_sec) AS max_duration_sec,
FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`
GROUP BY 1
ORDER BY 4 DESC
Diese Abfrage findet die Mindest- und Höchstwerte in der angegebenen Spalte. Das Ergebnis zeigt den kleinsten bzw. den größten Wert.
Jetzt werden wir zu Shopdaten wechseln, um Aggregationsfunktionen in den verschiedenen Datensatztypen zu verwenden.
SELECT
seller_channel,
COUNT(*) AS count_rows,
COUNT(order_number) AS count_order_number,
COUNT (discount_code) AS count_discount_code
FROM `dwh-udemy-course.3_dwh_ecommerce.order_item`
GROUP BY 1
ORDER BY 1
Zusammenfassend haben wir zuvor untersucht, wie konsistent Bestellnummern vorhanden waren, Rabattcodes jedoch nur für den Online-Shop relevant waren. Für ein tieferes Verständnis haben wir die Produktmengen untersucht, die über verschiedene Verkäuferkanäle bestellt wurden. Wir haben beispielsweise festgestellt, dass wir für zwei Verkäuferkanäle — „Über Sie“ und „Online Shop“ — Mengen für eBay bestellt hatten, nicht aber für „Auto“, was darauf hindeutet, dass über diesen Kanal keine Produkte verkauft wurden.
Darüber hinaus haben wir die durchschnittliche Menge der bestellten Produkte berechnet und sie kanalübergreifend verglichen. Wir haben festgestellt, dass im „Online Shop“ die durchschnittliche Menge an bestellten Produkten zwar etwas höher war als bei „Über Sie“, es ist jedoch wichtig zu beachten, dass dieser Durchschnitt auf den bestellten Artikeln und nicht auf der Gesamtbestellmenge beruhte. Diese Unterscheidung ist für eine genaue Analyse von entscheidender Bedeutung.
Wie wir anhand der BigQuery-Beispiele gesehen haben, bieten Aggregationsfunktionen und die GROUP BY-Klausel leistungsstarke Tools für die Datenanalyse. Für detailliertere Einblicke sollten Sie erwägen, mit verschiedenen GROUP BY-Spalten zu experimentieren, z. B. die Namen der Start- und Endstationen in den Bike-Share-Daten zu vergleichen, um die durchschnittliche Dauer zwischen den Stationen zu ermitteln. Das Verständnis dieser Nuancen ermöglicht eine umfassendere Datenanalyse.
Das Verständnis und die effektive Verwendung von Aggregationsfunktionen in SQL sind für die Datenanalyse von entscheidender Bedeutung. Mit Funktionen wie AVG, SUM, MIN, MAX und COUNT können Sie große Datensätze effizient zusammenfassen und analysieren. In Kombination mit der GROUP BY-Anweisung bieten diese Funktionen aussagekräftige Einblicke in Ihre Daten.
Ganz gleich, ob Sie Gesamteinträge zählen, Durchschnittswerte berechnen, Werte summieren oder Mindest- und Höchstwerte ermitteln, die Beherrschung dieser Aggregationsfunktionen wird Ihre Datenanalysefunktionen verbessern. Experimentieren Sie mit diesen Funktionen an Ihren Datensätzen, um wertvolle Erkenntnisse und Trends zu entdecken. Weitere Informationen und ausführliche Tutorials finden Sie in unseren anderen Blogbeiträgen und Videos. Viel Spaß beim Abfragen!
In der Welt der Datenanalyse sind SQL-Kenntnisse unerlässlich. Ein wichtiger Aspekt von SQL ist das Verständnis, wie Aggregationsfunktionen zum Zusammenfassen und Analysieren von Daten verwendet werden. In diesem Blogbeitrag werden die wichtigsten Aggregationsfunktionen — AVG, SUM, MIN, MAX und COUNT — mithilfe von Google BigQuery behandelt. Wir werden praktische Beispiele untersuchen, um Ihnen zu helfen, die Anwendungen und die Bedeutung dieser Funktionen zu verstehen.