googlesheetsquery

Abfrage von Google Sheets

Die Abfragesprache von Google ist eine schnelle und leistungsstarke Methode zum Analysieren von Daten in Ihren Google-Tabellen, die viele der in SQL verwendeten Muster übernimmt. Dieses Tutorial macht Sie mit den Grundlagen der Verwendung von Abfragen zur Beantwortung spezifischer Fragen aus einem Beispieldatensatz vertraut.

Beispieldaten importieren

Zuerst benötigen wir einige Beispieldaten zum Spielen. Wir verwenden die Importfunktion, um eine Tabelle mit Weltbevölkerungsdaten aus Wikipedia abzurufen:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)", "table", 3)

Diese Funktion findet die dritte Tabelle unter der oben genannten URL, extrahiert Daten aus jeder Zelle und importiert sie in Ihr Google Sheet. Cool! Aber lassen Sie uns noch etwas tun, um die Abfrage dieser Daten etwas einfacher zu machen.

Klicken Sie auf eine beliebige Zelle mit Daten, um sie hervorzuheben, und drücken Sie dann CMD + A, um die gesamte Tabelle hervorzuheben. Wenn die gesamte Tabelle hervorgehoben ist, klicken Sie auf „Daten“ > „Benannte Bereiche“, um das Bedienfeld „Benannte Bereiche“ zu öffnen – es wird von rechts aus angezeigt.

Ein benannter Bereich ist ein Spitzname, den Sie einer Gruppe von Zellen geben können. Nennen wir unsere „Länder“.

Großartig! Jetzt können wir mit der Abfrage beginnen!

Markieren Sie Zelle J1 – wir verwenden diese Zelle, um alle folgenden Abfragen einzugeben:

Grundlagen der Abfrage

Zeigen Sie mir eine Kopie des gesamten Datensatzes:

=QUERY(countries, "select *",1)

Zeigen Sie mir nur die Spalten „Länder“ und „Region“ an:

=QUERY(countries, "select B, C",1)

Konkret werden mit der „Where“-Klausel

Die WHERE-Klausel ist eine großartige Möglichkeit, spezifischere Fragen aus Ihrem Datensatz zu stellen.

Zeigen Sie mir Länder mit einer Bevölkerung von mindestens 100 Millionen:

=QUERY(countries, "SELECT B, E where E >= 100000000")

Zeige mir Länder mit „es“ im Namen:

=QUERY(countries, "SELECT B where B contains 'es'")

Zeige mir Länder ohne „es“ im Namen:

=QUERY(countries, "SELECT B where not B contains 'es'")

Zeige mir Länder, die mit „S“ beginnen:

=QUERY(countries, "SELECT B where B STARTS WITH 'S'")

Zeigen Sie mir Länder an, die mit „s“ enden (achten Sie auf die Groß-/Kleinschreibung:

=QUERY(countries, "SELECT B where B ENDS WITH 's'")

Zeige mir Länder, die „United“ enthalten:

=QUERY(countries, "SELECT B where B CONTAINS 'United'")

Lassen Sie uns die UND-Klausel noch spezifischer hinzufügen!

Zeigen Sie mir alle Länder Amerikas , die ebenfalls weniger als 100.000 Einwohner haben :

=QUERY(countries, "select B, F WHERE C = 'Americas' AND F <= 100000", 1)

AND-Klauseln können bis ins Unendliche aneinandergereiht werden.

Diese Abfrage gibt alle Länder in Amerika mit einer Bevölkerung von weniger als 100.000 und mehr als 10.000 zurück:

=QUERY(countries, "select B, F WHERE C = 'Americas' AND F <= 100000 AND F >= 10000", 1)

Aggregierte Werte

Vielleicht ist es eines der ersten Dinge, die Sie beim Durchsuchen eines Datensatzes tun möchten, einige einfache Aggregationen anzuzeigen: die Maximal-, Minimal- und Durchschnittswerte in bestimmten Feldern. Lassen Sie uns die maximale, minimale und durchschnittliche Bevölkerung für die Länder in unserem Datensatz im Jahr 2017 ermitteln:

=QUERY(countries, "select max(F), min(F), avg(F)", 1)

Das ist cool,

Mit einer leichten Modifikation können wir die maximale, minimale und durchschnittliche Bevölkerung jedes Kontinents sehen:

=QUERY(countries, "select C, max(F), min (F), avg(F) GROUP by C", 1)

„Zählen“ und „Gruppieren nach“ zusammen sind eine weitere großartige Möglichkeit, einen Datensatz zu erkunden. In Kombination ermöglichen sie Ihnen das Zählen von Elementen in einer oder mehreren Spalten sowie die Anzeige und Aggregation.

Lassen Sie uns diese beiden Klauseln verwenden, um zu sehen, wie viele Länder in unserer Liste sich in jeder Region befinden:

=QUERY(countries, "SELECT C, count(B) GROUP by C", 1)

Fügen wir die Klausel „ORDER BY“ hinzu, um uns eine Liste aller Regionen anzuzeigen, geordnet aufsteigend von der höchsten zur niedrigsten:

=QUERY(countries, "SELECT C, count(B) GROUP by C ORDER by C asc", 1)

Die „PIVOT“-Klausel aggregiert Werte in einer einzigen Zeile.

Wenn wir beispielsweise die Summe aller Populationen im Jahr 2017 nach Regionen anzeigen möchten, könnten wir Folgendes verwenden:

=QUERY(countries, "SELECT sum(F) pivot C", 1)

Ein bisschen Mathe machen

Sie können Ihre Daten auch analysieren, indem Sie im Rahmen Ihrer Abfragen mathematische Operationen ausführen.

Lassen Sie uns nun eine Liste der Länder mit ihrem Bevölkerungsanteil im Jahr 2017 als Prozentsatz der gesamten Weltbevölkerung anzeigen:

=QUERY(countries, "select B, C, (F / 7550262101) * 100", 1)

Unsere Prozentangaben sehen cool aus, aber die Spaltenüberschrift ist super lesbar. Wir könnten in die Zelle klicken und sie umbenennen – aber wir können den Namen auch als Teil der Abfrage festlegen, indem wir einen „Label“-Parameter hinzufügen:

=QUERY(countries, "select B, C, (F / 7550262101) * 100 Label (F / 7550262101) * 100 'Percentage'", 1)

Referenzen

Dokumentation zur Google Query Language: https://developers.google.com/chart/interactive/docs/querysprache

Übersicht über die Google Sheets-Abfragefunktion von Coding Is For Losers: https://codingisforlosers.com/google-sheets-query-function/#why