Analytische Funktionen in Google BigQuery - Teil 1: Grundlagen

Google BigQuery, die Data-Warehouse-Lösung von Google, verfügt über zahlreiche Funktionen und Möglichkeiten. In Teil 1 einer Reihe von Beiträgen befassen wir uns mit der Definition von Analysefunktionen, wie sie sich von Aggregatfunktionen unterscheiden und die verschiedenen Verwendungsmöglichkeiten in alltäglichen Szenarien.

Beispiele

- Definieren wir zunächst unsere Beispieltabelle, Tabelle 1, auf der alle Abfragen in diesem Beitrag und ihre Ergebnisse beruhen.

Tabelle1: Bestellungen

Außerdem sind alle Abfragen in diesem Beitrag im BigQuery Standard-SQL-Dialekt geschrieben.

Aggregat vs. analytische Funktion

Diejenigen, die mit traditionellen SQL-Datenbanken vertraut sind, wissen bereits, wie Aggregatfunktionen funktionieren, aber der Vollständigkeit halber füge ich sie hier ein.

Eine Aggregatfunktion ist eine Funktion, die einen einzelnen aggregierten Wert über eine Gruppe von Zeilen berechnet. Die Syntax dieser Funktion ist denkbar einfach:

Jede Spalte, die nicht aggregiert wird, muss in der GROUP BY-Klausel vorhanden sein:

Im Gegensatz dazu ist eine analytische Funktion eine Funktion, die einen aggregierten Wert für jede Zeile über eine Gruppe von Zeilen berechnet. Die Syntax dieser Funktion lautet wie folgt:

Da die Gruppierung durch den eigenen Bedingungsblock der analytischen Funktion erfolgt, ist die GROUP BY-Klausel nicht erforderlich.

Klauseln

PARTITION BY

PARTITION BY ist wohl die wichtigste Klausel einer analytischen Funktion. Ähnlich wie die GROUP BY-Klausel der Aggregatfunktion begrenzt sie die Anzahl der Zeilen, die die Funktion lediglich auf einer Teilmenge aller Zeilen berechnet. Während in einer GROUP BY-Klausel alle Nicht-Aggregatsspalten enthalten sein müssen, gibt es hier keine solche Einschränkung.

Beispiel 1

Diese Abfrage berechnet die Differenz zwischen dem tatsächlichen Preis und dem Durchschnittspreis des Geschäfts. Da wir eine analytische Funktion verwenden, können wir die Spalten OrderId und Date in unsere Ergebnisse einbeziehen, ohne nach ihnen zu gruppieren. Wenn wir das gleiche Ergebnis mit Hilfe von Aggregatfunktionen erhalten wollten, würde die Abfrage wie folgt aussehen:

ORDER BY

Als nächstes kommt die ORDER BY-Klausel. Ähnlich wie die ORDER BY-Klausel am (nahen) Ende einer Abfrage dient sie dazu, jede Partition zu ordnen, bevor die analytische Funktion ausgewertet wird. Während Funktionen wie SUM oder AVG nicht von einer geordneten Partition profitieren, gibt es bestimmte Funktionen, bei denen dies ein Muss ist. Eine solche Funktion ist LAG. Diese Funktion hat ein Argument (einen Spaltennamen), und ihr Rückgabewert ist der Wert der Spalte aus der vorherigen Zeile.

Beispiel 2

Diese Abfrage gibt die IDs aller Bestellungen zurück, die an einem bestimmten Datum für jede Filiale zuerst bearbeitet wurden. (Wir gehen davon aus, dass die Bestellung mit einer niedrigeren ID vor einer Bestellung mit einer höheren ID eingegangen ist). Das gleiche Problem kann mit Aggregatfunktionen gelöst werden, aber diese Methode verbindet (wieder) eine Teilmenge der Tabelle mit der Tabelle selbst, was viel weniger performant ist als die von uns verwendete analytische Funktion.

Schlussfolgerung

Die analytischen Funktionen von BigQuery sind leistungsstarke Werkzeuge, mit denen schwierige und teuer zu berechnende Abfragen auf einfachere Lösungen reduziert werden können. Wir haben uns zwar mit der Definition und der Semantik der analytischen Funktionen befasst, müssen aber noch das Windowing und erweiterte Anwendungsfälle behandeln. Bleiben Sie dran für Teil 2 dieser Blogserie.

Interessiert an mehr über Analytic?

- Analytische Funktionskonzepte

- Liste der Aggregatfunktionen, die als analytische Funktionen verwendet werden können

‍