Überblick über die Hauptfunktionen von Google BigQuery – Üben Sie das Schreiben von Anfragen für Marketinganalysen
Veröffentlicht: 2022-04-12Je mehr Informationen ein Unternehmen sammelt, desto akuter stellt sich die Frage, wo diese gespeichert werden sollen. Wenn Sie nicht die Möglichkeit oder den Wunsch haben, Ihre eigenen Server zu unterhalten, kann Google BigQuery (GBQ) helfen. BigQuery bietet schnellen, kostengünstigen und skalierbaren Speicher für die Arbeit mit Big Data und ermöglicht das Schreiben von Abfragen mit SQL-ähnlicher Syntax sowie Standard- und benutzerdefinierten Funktionen.
In diesem Artikel gehen wir auf die Hauptfunktionen von BigQuery ein und zeigen ihre Möglichkeiten anhand konkreter Beispiele auf. Sie lernen, wie Sie grundlegende Abfragen schreiben und diese anhand von Demodaten testen.
Erstellen Sie Berichte zu GBQ-Daten ohne technische Schulung oder SQL-Kenntnisse.
Benötigen Sie regelmäßig Berichte zu Werbekampagnen, haben aber keine Zeit, SQL zu studieren oder auf eine Antwort Ihrer Analysten zu warten? Mit OWOX BI können Sie Berichte erstellen, ohne verstehen zu müssen, wie Ihre Daten strukturiert sind. Wählen Sie einfach die Parameter und Metriken aus, die Sie in Ihrem Smart Data-Bericht sehen möchten. OWOX BI Smart Data visualisiert Ihre Daten sofort so, dass Sie sie verstehen können.
Inhaltsverzeichnis
- Was ist SQL und welche Dialekte unterstützt BigQuery?
- Wo soll man anfangen
- Google BigQuery-Funktionen
- Aggregatfunktionen
- Datumsfunktionen
- String-Funktionen
- Fensterfunktionen
- Schlussfolgerungen
Was ist SQL und welche Dialekte unterstützt BigQuery?
Mit der Structured Query Language (SQL) können Sie Daten aus großen Arrays abrufen, Daten hinzufügen und Daten in großen Arrays ändern. Google BigQuery unterstützt zwei SQL-Dialekte: Standard-SQL und das veraltete Legacy-SQL.
Welchen Dialekt Sie wählen, hängt von Ihren Vorlieben ab, aber Google empfiehlt die Verwendung von Standard-SQL für diese Vorteile:
- Flexibilität und Funktionalität für verschachtelte und sich wiederholende Felder
- Unterstützung für die Sprachen DML und DDL, sodass Sie Daten in Tabellen ändern sowie Tabellen und Ansichten in GBQ verwalten können
- Schnellere Verarbeitung großer Datenmengen im Vergleich zu Legacy SQL
- Unterstützung für alle zukünftigen BigQuery-Updates
Weitere Informationen zu den Dialektunterschieden finden Sie in der BigQuery-Dokumentation.
Siehe auch: Welche Vorteile hat der neue Standard-SQL-Dialekt von Google BigQuery gegenüber Legacy SQL und welche Business-Aufgaben können Sie damit lösen?
Standardmäßig werden Google BigQuery-Abfragen auf Legacy-SQL ausgeführt.
Sie können auf verschiedene Arten zu Standard-SQL wechseln:
- Wählen Sie in der BigQuery-Oberfläche im Abfragebearbeitungsfenster Optionen anzeigen aus und entfernen Sie das Häkchen neben Use Legacy SQL :
- Fügen Sie vor der Abfrage die Zeile #standardSQL hinzu und beginnen Sie Ihre Abfrage mit einer neuen Zeile:
Wo soll man anfangen
Damit Sie Abfragen mit uns üben und ausführen können, haben wir eine Tabelle mit Demodaten vorbereitet. Füllen Sie das untenstehende Formular aus und wir senden es Ihnen per E-Mail zu.
Demodaten für die Praxis der SQL-Abfragen
herunterladen Laden Sie zunächst Ihre Demo-Datentabelle herunter und laden Sie sie in Ihr Google BigQuery-Projekt hoch. Am einfachsten geht das mit dem Add-on OWOX BI BigQuery Reports.
- Öffnen Sie Google Sheets und installieren Sie das Add-on OWOX BI BigQuery Reports.
- Öffnen Sie die heruntergeladene Tabelle, die Demodaten enthält, und wählen Sie OWOX BI BigQuery-Berichte –> Daten in BigQuery hochladen aus :
- Wählen Sie in dem sich öffnenden Fenster Ihr Google BigQuery-Projekt, einen Datensatz, und denken Sie sich einen Namen für die Tabelle aus, in der die geladenen Daten gespeichert werden.
- Geben Sie ein Format für die geladenen Daten an (wie im Screenshot gezeigt):
Wenn Sie kein Projekt in Google BigQuery haben, erstellen Sie eines. Dazu benötigen Sie ein aktives Rechnungskonto in der Google Cloud Platform. Lassen Sie sich nicht davon abschrecken, dass Sie eine Bankkarte verknüpfen müssen: Sie werden ohne Ihr Wissen mit nichts belastet. Darüber hinaus erhalten Sie bei der Registrierung 300 US-Dollar für 12 Monate, die Sie für die Datenspeicherung und -verarbeitung ausgeben können.
OWOX BI hilft Ihnen, Daten aus verschiedenen Systemen in BigQuery zusammenzuführen: Daten zu Benutzeraktionen auf Ihrer Website, Anrufe, Bestellungen aus Ihrem CRM, E-Mails, Werbekosten. Sie können OWOX BI verwenden, um erweiterte Analysen anzupassen und Berichte beliebiger Komplexität zu automatisieren.
Bevor wir über Google BigQuery-Funktionen sprechen, erinnern wir uns daran, wie grundlegende Abfragen sowohl im Legacy-SQL- als auch im Standard-SQL-Dialekt aussehen:
| Anfrage | Legacy-SQL | Standard-SQL |
|---|---|---|
| Wählen Sie Felder aus der Tabelle aus | SELECT Feld1,Feld2 | SELECT Feld1,Feld2 |
| Wählen Sie eine Tabelle aus, aus der Sie Felder auswählen möchten | FROM [ProjektID:Datensatz.Tabellenname] | VON `ProjektID.dataSet.tableName` |
| Wählen Sie den Parameter aus, nach dem die Werte gefiltert werden sollen | WHERE-Feld1=Wert | WHERE-Feld1=Wert |
| Wählen Sie Felder aus, nach denen Ergebnisse gruppiert werden sollen | GROUP BY Feld1, Feld2 | GROUP BY Feld1, Feld2 |
| Wählen Sie aus, wie Ergebnisse sortiert werden sollen | ORDER BY-Feld1 ASC (aufsteigend) oder DESC (absteigend) | ORDER BY-Feld1 ASC (aufsteigend) oder DESC (absteigend) |
Google BigQuery-Funktionen
Beim Erstellen von Abfragen verwenden Sie am häufigsten Aggregat-, Datums-, Zeichenfolgen- und Fensterfunktionen. Sehen wir uns jede dieser Funktionsgruppen genauer an.
Siehe auch: So beginnen Sie mit der Arbeit mit Cloud-Speicher – Erstellen Sie einen Datensatz und Tabellen und konfigurieren Sie den Import von Daten in Google BigQuery.
Aggregatfunktionen
Aggregatfunktionen liefern zusammenfassende Werte für eine ganze Tabelle. Sie können sie beispielsweise verwenden, um die durchschnittliche Scheckgröße oder den Gesamtumsatz pro Monat zu berechnen, oder Sie können sie verwenden, um das Segment der Benutzer auszuwählen, die die maximale Anzahl von Einkäufen getätigt haben.
Dies sind die beliebtesten Aggregatfunktionen:
| Legacy-SQL | Standard-SQL | Was die Funktion tut |
|---|---|---|
| AVG (Feld) | AVG([DISTINCT] (Feld)) | Gibt den Durchschnittswert der Feldspalte zurück. Wenn Sie in Standard-SQL eine DISTINCT-Bedingung hinzufügen, wird der Durchschnitt nur für Zeilen mit eindeutigen (sich nicht wiederholenden) Werten in der Feldspalte berücksichtigt. |
| MAX(Feld) | MAX(Feld) | Gibt den Maximalwert aus der Feldspalte zurück. |
| MIN(Feld) | MIN(Feld) | Gibt den Mindestwert aus der Feldspalte zurück. |
| SUM(Feld) | SUM(Feld) | Gibt die Summe der Werte aus der Feldspalte zurück. |
| ANZAHL(Feld) | ANZAHL(Feld) | Gibt die Anzahl der Zeilen in der Feldspalte zurück. |
| EXACT_COUNT_DISTINCT(Feld) | COUNT([DISTINCT] (Feld)) | Gibt die Anzahl eindeutiger Zeilen in der Feldspalte zurück. |
Eine Liste aller Aggregatfunktionen finden Sie in der Dokumentation zu Legacy-SQL und Standard-SQL.
Schauen wir uns die Demodaten an, um zu sehen, wie diese Funktionen funktionieren. Wir können den durchschnittlichen Umsatz für Transaktionen, Käufe für die höchsten und niedrigsten Beträge, Gesamtumsatz, Gesamttransaktionen und die Anzahl der einzelnen Transaktionen berechnen (um zu überprüfen, ob Käufe dupliziert wurden). Dazu schreiben wir eine Abfrage, in der wir den Namen unseres Google BigQuery-Projekts, des Datensatzes und der Tabelle angeben.
#Legacy-SQL
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, EXACT_COUNT_DISTINCT(transactionId) as unique_transactions FROM [owox-analytics:t_kravchenko.Demo_data]
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, EXACT_COUNT_DISTINCT(transactionId) as unique_transactions FROM [owox-analytics:t_kravchenko.Demo_data]#Standard-SQL
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, COUNT(DISTINCT(transactionId)) as unique_transactions FROM `owox-analytics.t_kravchenko.Demo_data`
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, COUNT(DISTINCT(transactionId)) as unique_transactions FROM `owox-analytics.t_kravchenko.Demo_data`Als Ergebnis erhalten wir Folgendes:
Sie können die Ergebnisse dieser Berechnungen in der Originaltabelle mit Demodaten überprüfen, indem Sie Standardfunktionen von Google Sheets (SUM, AVG und andere) oder Pivot-Tabellen verwenden.
Wie Sie auf dem obigen Screenshot sehen können, ist die Anzahl der Transaktionen und eindeutigen Transaktionen unterschiedlich. Dies deutet darauf hin, dass es in unserer Tabelle zwei Transaktionen mit derselben Transaktions-ID gibt:
Wenn Sie an eindeutigen Transaktionen interessiert sind, verwenden Sie eine Funktion, die eindeutige Zeichenfolgen zählt. Alternativ können Sie Daten mit der GROUP BY-Funktion gruppieren, um Duplikate zu entfernen, bevor Sie die Aggregatfunktion anwenden.
Demodaten für die Praxis der SQL-Abfragen
herunterladenDatumsfunktionen
Mit diesen Funktionen können Sie Datumsangaben bearbeiten: ihr Format ändern, das erforderliche Feld (Tag, Monat oder Jahr) auswählen oder das Datum um ein bestimmtes Intervall verschieben.
Sie können nützlich sein, wenn:
- Konvertieren von Daten und Zeiten aus verschiedenen Quellen in ein einziges Format, um erweiterte Analysen einzurichten
- Erstellen automatisch aktualisierter Berichte oder Auslösen von Mailings (z. B. wenn Sie Daten für die letzten zwei Stunden, die Woche oder den Monat benötigen)
- Erstellen von Kohortenberichten, in denen es notwendig ist, Daten für einen Zeitraum von Tagen, Wochen oder Monaten zu erhalten
Dies sind die am häufigsten verwendeten Datumsfunktionen:
| Legacy-SQL | Standard-SQL | Bedienungsanleitung |
|---|---|---|
| AKTUELLES DATUM() | AKTUELLES DATUM() | Gibt das aktuelle Datum im Format % JJJJ - % MM - % TT zurück. |
| DATUM (Zeitstempel) | DATUM (Zeitstempel) | Konvertiert das Datum von % JJJJ - % MM - % TT % H: % M: % C. in das Format % JJJJ - % MM - % TT. |
| DATE_ADD(Zeitstempel, Intervall, Intervall_Einheiten) | DATE_ADD(Zeitstempel, INTERVAL-Intervall Intervall_Einheiten) | Gibt das Zeitstempeldatum zurück und erhöht es um das angegebene Intervall interval.interval_units. In Legacy-SQL kann es die Werte YEAR, MONTH, DAY, HOUR, MINUTE und SECOND annehmen, und in Standard-SQL kann es YEAR, QUARTER, MONTH annehmen, WOCHE und TAG. |
| DATE_ADD(timestamp, - interval, interval_units) | DATE_SUB(Zeitstempel, INTERVAL-Intervall Intervall_Einheiten) | Gibt das Zeitstempeldatum zurück und verringert es um das angegebene Intervall. |
| DATEDIFF(Zeitstempel1, Zeitstempel2) | DATE_DIFF(Zeitstempel1, Zeitstempel2, Datumsteil) | Gibt die Differenz zwischen den Datumsangaben timestamp1 und timestamp2 zurück. Gibt in Legacy-SQL die Differenz in Tagen und in Standard-SQL die Differenz abhängig vom angegebenen date_part-Wert (Tag, Woche, Monat, Quartal, Jahr) zurück. |
| TAG (Zeitstempel) | EXTRACT(TAG AUS Zeitstempel) | Gibt den Tag aus dem Zeitstempeldatum zurück. Akzeptiert Werte von 1 bis einschließlich 31. |
| MONAT(Zeitstempel) | AUSZUG (MONAT AUS Zeitstempel) | Gibt die fortlaufende Monatsnummer aus dem Zeitstempeldatum zurück. Akzeptiert Werte von 1 bis einschließlich 12. |
| JAHR (Zeitstempel) | AUSZUG (JAHR AUS Zeitstempel) | Gibt das Jahr aus dem Zeitstempeldatum zurück. |
Eine Liste aller Datumsfunktionen finden Sie in der Dokumentation zu Legacy-SQL und Standard-SQL.
Werfen wir einen Blick auf unsere Demodaten, um zu sehen, wie jede dieser Funktionen funktioniert. Zum Beispiel erhalten wir das aktuelle Datum, wandeln das Datum aus der ursprünglichen Tabelle in das Format % JJJJ - % MM - % TT um, entfernen es und fügen einen Tag hinzu. Dann berechnen wir die Differenz zwischen dem aktuellen Datum und dem Datum aus der Quelltabelle und unterteilen das aktuelle Datum in separate Jahres-, Monats- und Tagesfelder. Dazu können Sie die folgenden Beispielabfragen kopieren und den Projektnamen, das Dataset und die Datentabelle durch Ihre eigenen ersetzen.
#Legacy-SQL
SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC,1, 'DAY') AS date_UTC_plus_one_day, DATE_ADD( date_UTC,-1, 'DAY') AS date_UTC_minus_one_day, DATEDIFF(CURRENT_DATE(), date_UTC ) AS difference_between_date, DAY( CURRENT_DATE() ) AS the_day, MONTH( CURRENT_DATE()) AS the_month, YEAR( CURRENT_DATE()) AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]
SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC,1, 'DAY') AS date_UTC_plus_one_day, DATE_ADD( date_UTC,-1, 'DAY') AS date_UTC_minus_one_day, DATEDIFF(CURRENT_DATE(), date_UTC ) AS difference_between_date, DAY( CURRENT_DATE() ) AS the_day, MONTH( CURRENT_DATE()) AS the_month, YEAR( CURRENT_DATE()) AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]#Standard-SQL
SELECT today, date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC_in_YYYYMMDD, INTERVAL 1 DAY) AS date_UTC_plus_one_day, DATE_SUB( date_UTC_in_YYYYMMDD,INTERVAL 1 DAY) AS date_UTC_minus_one_day, DATE_DIFF(today, date_UTC_in_YYYYMMDD, DAY) AS difference_between_date, EXTRACT(DAY FROM today ) AS the_day, EXTRACT(MONTH FROM today ) AS the_month, EXTRACT(YEAR FROM today ) AS the_year FROM ( SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD FROM `owox-analytics.t_kravchenko.Demo_data`)
SELECT today, date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC_in_YYYYMMDD, INTERVAL 1 DAY) AS date_UTC_plus_one_day, DATE_SUB( date_UTC_in_YYYYMMDD,INTERVAL 1 DAY) AS date_UTC_minus_one_day, DATE_DIFF(today, date_UTC_in_YYYYMMDD, DAY) AS difference_between_date, EXTRACT(DAY FROM today ) AS the_day, EXTRACT(MONTH FROM today ) AS the_month, EXTRACT(YEAR FROM today ) AS the_year FROM ( SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD FROM `owox-analytics.t_kravchenko.Demo_data`)Nachdem Sie die Abfrage ausgeführt haben, erhalten Sie diesen Bericht:
Siehe auch: Beispiele für Berichte, die mithilfe von SQL-Abfragen zu Daten in Google BigQuery erstellt werden können, und welche einzigartigen Metriken Sie Google Analytics-Daten mit OWOX BI ergänzen können.
String-Funktionen
Mit String-Funktionen können Sie einen String generieren, Teilstrings auswählen und ersetzen sowie die Länge eines Strings und die Indexfolge des Teilstrings im Originalstring berechnen. Mit Zeichenfolgenfunktionen können Sie beispielsweise:
- einen Bericht mit UTM-Tags filtern, die an die Seiten-URL übergeben werden
- Daten in ein einziges Format bringen, wenn die Quellen- und Kampagnennamen in verschiedene Register geschrieben werden
- Ersetzen falscher Daten in einem Bericht (z. B. wenn der Kampagnenname falsch gedruckt ist)
Dies sind die beliebtesten Funktionen für die Arbeit mit Strings:
| Legacy-SQL | Standard-SQL | Bedienungsanleitung |
|---|---|---|
| CONCAT('str1', 'str2') oder 'str1'+ 'str2' | CONCAT('str1', 'str2') | Verkettet „str1“ und „str2“ zu einer Zeichenfolge. |
| 'str1' ENTHÄLT 'str2' | REGEXP_CONTAINS('str1', 'str2') oder 'str1' LIKE '%str2%' | Gibt „true“ zurück, wenn die Zeichenfolge „str1“ die Zeichenfolge „str2“ enthält. In Standard-SQL kann die Zeichenfolge „str2“ mithilfe der re2-Bibliothek als regulärer Ausdruck geschrieben werden. |
| LÄNGE('str' ) | CHAR_LENGTH('str' ) oder CHARACTER_LENGTH('str' ) | Gibt die Länge des Strings 'str' (Anzahl der Zeichen) zurück. |
| SUBSTR('str', index [, max_len]) | SUBSTR('str', index [, max_len]) | Gibt einen Teilstring der Länge max_len zurück, der mit einem Indexzeichen aus dem String 'str' beginnt. |
| LOWER('str') | LOWER('str') | Konvertiert alle Zeichen in der Zeichenfolge „str“ in Kleinbuchstaben. |
| OBERE(str) | OBERE(str) | Konvertiert alle Zeichen in der Zeichenfolge „str“ in Großbuchstaben. |
| INSTR('str1', 'str2') | STRPOS('str1', 'str2') | Gibt den Index des ersten Vorkommens der Zeichenfolge „str2“ bis zur Zeichenfolge „str1“ zurück; Andernfalls wird 0 zurückgegeben. |
| ERSETZEN('str1', 'str2', 'str3') | ERSETZEN('str1', 'str2', 'str3') | Ersetzt „str1“ durch „str2“ durch „str3“. |
Weitere Informationen zu allen Zeichenfolgenfunktionen finden Sie in der Dokumentation zu Legacy-SQL und Standard-SQL.
Schauen wir uns Demodaten an, um zu sehen, wie die beschriebenen Funktionen verwendet werden. Angenommen, wir haben drei separate Spalten, die Tages-, Monats- und Jahreswerte enthalten:
Das Arbeiten mit einem Datum in diesem Format ist nicht sehr praktisch, daher können wir die Werte in einer Spalte kombinieren. Verwenden Sie dazu die folgenden SQL-Abfragen und denken Sie daran, den Namen Ihres Projekts, Datasets und Ihrer Tabelle in Google BigQuery zu ersetzen.
#Legacy-SQL
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY mix_string1
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY mix_string1#Standard-SQL
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM owox-analytics.t_kravchenko.Demo_data) GROUP BY mix_string1
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM owox-analytics.t_kravchenko.Demo_data) GROUP BY mix_string1Nach dem Ausführen der Abfrage erhalten wir das Datum in einer Spalte:
Wenn Sie eine Seite auf einer Website herunterladen, zeichnet die URL häufig die Werte der Variablen auf, die der Benutzer ausgewählt hat. Dies kann eine Zahlungs- oder Liefermethode, Transaktionsnummer, Index des physischen Geschäfts sein, in dem der Käufer den Artikel abholen möchte usw. Mithilfe einer SQL-Abfrage können Sie diese Parameter aus der Seitenadresse auswählen. Betrachten Sie zwei Beispiele dafür, wie und warum Sie dies tun könnten.
Beispiel 1 . Angenommen, wir möchten die Anzahl der Käufe wissen, bei denen Benutzer Waren in physischen Geschäften abholen. Dazu müssen wir die Anzahl der Transaktionen berechnen, die von Seiten in der URL gesendet werden, die eine Teilzeichenfolge shop_id (ein Index für ein physisches Geschäft) enthalten. Wir können dies mit den folgenden Abfragen tun:
#Legacy-SQL
SELECT COUNT(transactionId) AS transactions, check FROM ( SELECT transactionId, page CONTAINS 'shop_id' AS check FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY check
SELECT COUNT(transactionId) AS transactions, check FROM ( SELECT transactionId, page CONTAINS 'shop_id' AS check FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY check#Standard-SQL
SELECT COUNT(transactionId) AS transactions, check1, check2 FROM ( SELECT transactionId, REGEXP_CONTAINS( page, 'shop_id') AS check1, page LIKE '%shop_id%' AS check2 FROM `owox-analytics.t_kravchenko.Demo_data`) GROUP BY check1, check2
SELECT COUNT(transactionId) AS transactions, check1, check2 FROM ( SELECT transactionId, REGEXP_CONTAINS( page, 'shop_id') AS check1, page LIKE '%shop_id%' AS check2 FROM `owox-analytics.t_kravchenko.Demo_data`) GROUP BY check1, check2Aus der resultierenden Tabelle sehen wir, dass 5502 Transaktionen (check = true) von Seiten gesendet wurden, die shop_id enthalten:
Beispiel 2 . Sie haben jeder Versandmethode eine delivery_id zugewiesen und geben den Wert dieses Parameters in der Seiten-URL an. Um herauszufinden, welche Liefermethode der Benutzer gewählt hat, müssen Sie die delivery_id in einer separaten Spalte auswählen.
Dazu können wir folgende Abfragen verwenden:
#Legacy-SQL
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') as delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, LENGTH(page_lower_case) AS page_length, INSTR(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM [owox-analytics:t_kravchenko.Demo_data]))) ORDER BY page_lower_case ASC
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') as delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, LENGTH(page_lower_case) AS page_length, INSTR(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM [owox-analytics:t_kravchenko.Demo_data]))) ORDER BY page_lower_case ASC#Standard-SQL
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') AS delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, CHAR_LENGTH(page_lower_case) AS page_length, STRPOS(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM `owox-analytics.t_kravchenko.Demo_data`))) ORDER BY page_lower_case ASC
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') AS delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, CHAR_LENGTH(page_lower_case) AS page_length, STRPOS(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM `owox-analytics.t_kravchenko.Demo_data`))) ORDER BY page_lower_case ASCAls Ergebnis erhalten wir in Google BigQuery eine Tabelle wie diese:
Demodaten für die Praxis der SQL-Abfragen
herunterladenFensterfunktionen
Diese Funktionen ähneln den oben besprochenen Aggregatfunktionen. Der Hauptunterschied besteht darin, dass Fensterfunktionen Berechnungen nicht für den gesamten Datensatz ausführen, der mit der Abfrage ausgewählt wurde, sondern nur für einen Teil dieser Daten – eine Teilmenge oder ein Fenster .
Mithilfe von Fensterfunktionen können Sie Daten in einem Gruppenabschnitt aggregieren, ohne die JOIN-Funktion zum Kombinieren mehrerer Abfragen zu verwenden. Sie können beispielsweise den durchschnittlichen Umsatz pro Werbekampagne oder die Anzahl der Transaktionen pro Gerät berechnen. Indem Sie dem Bericht ein weiteres Feld hinzufügen, können Sie beispielsweise den Anteil der Einnahmen aus einer Werbekampagne am Black Friday oder den Anteil der Transaktionen, die über eine mobile Anwendung getätigt wurden, ganz einfach ermitteln.
Zusammen mit jeder Funktion in der Abfrage müssen Sie den OVER-Ausdruck buchstabieren, der die Fenstergrenzen definiert. OVER enthält drei Komponenten, mit denen Sie arbeiten können:
- PARTITION BY — Definiert das Merkmal, nach dem Sie die Originaldaten in Teilmengen unterteilen, wie z. B. clientId oder DayTime
- ORDER BY — Definiert die Reihenfolge der Zeilen in einer Teilmenge, wie z. B. Stunde DESC
- FENSTERRAHMEN – Ermöglicht die Verarbeitung von Zeilen innerhalb einer Teilmenge eines bestimmten Features (z. B. nur die fünf Zeilen vor der aktuellen Zeile)
In dieser Tabelle haben wir die am häufigsten verwendeten Fensterfunktionen zusammengestellt:
| Legacy-SQL | Standard-SQL | Bedienungsanleitung |
|---|---|---|
| AVG (Feld) ANZAHL(Feld) COUNT(DISTINCT-Feld) MAX() MINDEST() SUMME() | AVG([DISTINCT] (Feld)) ANZAHL(Feld) COUNT([DISTINCT] (Feld)) MAX(Feld) MIN(Feld) SUM(Feld) | Gibt den Durchschnitts-, Zahlen-, Höchst-, Mindest- und Gesamtwert aus der Feldspalte innerhalb der ausgewählten Teilmenge zurück. DISTINCT wird verwendet, um nur eindeutige (sich nicht wiederholende) Werte zu berechnen. |
| DENSE_RANK() | DENSE_RANK() | Gibt die Zeilennummer innerhalb einer Teilmenge zurück. |
| FIRST_VALUE(Feld) | FIRST_VALUE (Feld[{RESPECT | IGNORE} NULLS]) | Gibt den Wert der ersten Zeile aus der Feldspalte innerhalb einer Teilmenge zurück. Standardmäßig werden Zeilen mit leeren Werten aus der Feldspalte in die Berechnung einbezogen. RESPECT oder IGNORE NULLS gibt an, ob NULL-Strings eingeschlossen oder ignoriert werden sollen. |
| LAST_VALUE(Feld) | LAST_VALUE (Feld [{RESPECT | IGNORE} NULLS]) | Gibt den Wert der letzten Zeile innerhalb einer Teilmenge aus der Feldspalte zurück. Standardmäßig werden Zeilen mit leeren Werten in der Feldspalte in die Berechnung einbezogen. RESPECT oder IGNORE NULLS gibt an, ob NULL-Strings eingeschlossen oder ignoriert werden sollen. |
| LAG(Feld) | LAG (Feld[, Offset [, Standardausdruck]]) | Gibt den Wert der vorherigen Zeile in Bezug auf die aktuelle Feldspalte innerhalb der Teilmenge zurück. Offset ist eine Ganzzahl, die die Anzahl der Zeilen angibt, die von der aktuellen Zeile nach unten versetzt werden sollen. Default_expression ist der Wert, den die Funktion zurückgibt, wenn es nicht erforderlich ist Zeichenfolge innerhalb der Teilmenge. |
| LEAD(Feld) | LEAD (field[, offset [, default_expression]]) | Gibt den Wert der nächsten Zeile relativ zur aktuellen Feldspalte innerhalb der Teilmenge zurück. Offset ist eine Ganzzahl, die die Anzahl der Zeilen definiert, die Sie in Bezug auf die aktuelle Zeile nach oben verschieben möchten. Default_expression ist der Wert, den die Funktion zurückgibt, wenn es in der aktuellen Teilmenge keine erforderliche Zeichenfolge gibt. |
Eine Liste aller aggregierten Analysefunktionen und Navigationsfunktionen finden Sie in der Dokumentation zu Legacy SQL und Standard SQL.
Beispiel 1 . Angenommen, wir möchten die Aktivitäten von Kunden während der Arbeitszeit und außerhalb der Arbeitszeit analysieren. Dazu müssen wir Transaktionen in zwei Gruppen unterteilen und die interessierenden Metriken berechnen:
- Gruppe 1 – Einkäufe während der Arbeitszeit von 9:00 bis 18:00 Uhr
- Gruppe 2 – Einkäufe außerhalb der Geschäftszeiten von 00:00 bis 9:00 Uhr und von 18:00 bis 23:59 Uhr
Neben Arbeits- und Nicht-Arbeitszeit ist eine weitere Variable zum Bilden eines Fensters clientId. Das heißt, für jeden Benutzer haben wir zwei Fenster:
| Fenster | Kunden ID | Tageszeit |
|---|---|---|
| Fenster 1 | Client-ID 1 | Arbeitszeit |
| Fenster 2 | Client-ID 2 | arbeitsfreie Stunden |
| Fenster 3 | Client-ID 3 | Arbeitszeit |
| Fenster 4 | Client-ID 4 | arbeitsfreie Stunden |
| Fenster N | clientId N | Arbeitszeit |
| Fenster N+1 | clientId N+1 | arbeitsfreie Stunden |
Verwenden wir Demodaten, um den durchschnittlichen, maximalen, minimalen und Gesamtumsatz, die Gesamtzahl der Transaktionen und die Anzahl der eindeutigen Transaktionen pro Benutzer während der Arbeitszeit und außerhalb der Arbeitszeit zu berechnen. Die folgenden Anfragen helfen uns dabei.
#Legacy-SQL
SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC
SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC#Standard-SQL
#standardSQL SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC
#standardSQL SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESCSehen wir uns am Beispiel des Benutzers mit der clientId 102041117.1428132012 an, was als Ergebnis passiert. In der Originaltabelle für diesen Benutzer haben wir die folgenden Daten:
Durch Ausführen der Abfrage erhalten wir einen Bericht, der den durchschnittlichen, minimalen, maximalen und Gesamtumsatz dieses Benutzers sowie die Gesamtzahl der Transaktionen des Benutzers enthält. Wie Sie im folgenden Screenshot sehen können, wurden beide Transaktionen vom Benutzer während der Arbeitszeit durchgeführt:
Beispiel 2 . Nun zu einer komplizierteren Aufgabe:
- Geben Sie Sequenznummern für alle Transaktionen in Abhängigkeit vom Zeitpunkt ihrer Ausführung in das Fenster ein. Denken Sie daran, dass wir das Fenster nach Benutzer und Arbeits-/Nicht-Arbeitszeitfenstern definieren.
- Melden Sie den Umsatz der nächsten/vorherigen Transaktion (relativ zur aktuellen) innerhalb des Fensters.
- Zeigen Sie den Umsatz der ersten und letzten Transaktion im Fenster an.
Dazu verwenden wir die folgenden Abfragen:
#Legacy-SQL
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour#Standard-SQL
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hourWir können die Ergebnisse der Berechnungen am Beispiel eines uns bereits bekannten Benutzers überprüfen: clientId 102041117.1428132012:
Aus dem Screenshot oben können wir das sehen:
- Die erste Transaktion war um 15:00 Uhr und die zweite Transaktion war um 16:00 Uhr
- nach der Transaktion um 15:00 gab es eine Transaktion um 16:00 mit einem Umsatz von 25066 (Spalte lead_revenue)
- vor der Transaktion um 16:00 gab es eine Transaktion um 15:00 mit einem Umsatz von 3699 (Spalte lag_revenue)
- die erste Transaktion innerhalb des Fensters war um 15:00 Uhr und der Umsatz für diese Transaktion war 3699 (Spalte first_revenue_by_hour)
- Die Abfrage verarbeitet die Daten Zeile für Zeile, sodass für die betreffende Transaktion die letzte Transaktion im Fenster sie selbst ist und die Werte in den Spalten last_revenue_by_hour und Revenue gleich sind
Hilfreiche Artikel zu Google BigQuery:
- Die 6 besten BigQuery-Visualisierungstools
- So laden Sie Daten in Google BigQuery hoch
- So laden Sie Rohdaten von Google Ads in Google BigQuery hoch
- Google BigQuery Google Sheets-Konnektor
- Automatisieren Sie Berichte in Google Tabellen mithilfe von Daten aus Google BigQuery
- Automatisieren Sie Berichte in Google Data Studio basierend auf Daten von Google BigQuery
Wenn Sie ungesampelte Daten von Ihrer Website in Google BigQuery erfassen möchten, aber nicht wissen, wo Sie anfangen sollen, buchen Sie eine Demo. Wir verraten Ihnen, welche Möglichkeiten Sie mit BigQuery und OWOX BI haben.
Unsere Kunden
zunehmen 22 % schneller
Wachsen Sie schneller, indem Sie messen, was in Ihrem Marketing am besten funktioniert
Analysieren Sie Ihre Marketingeffizienz, finden Sie die Wachstumsbereiche, steigern Sie den ROI
Demo erhaltenSchlussfolgerungen
In diesem Artikel haben wir uns die beliebtesten Gruppen von Funktionen angesehen: Aggregat, Date, String und Window. Google BigQuery hat jedoch viele weitere nützliche Funktionen, darunter:
- Casting-Funktionen, mit denen Sie Daten in ein bestimmtes Format konvertieren können
- Platzhalterfunktionen für Tabellen, mit denen Sie auf mehrere Tabellen in einem Dataset zugreifen können
- Reguläre Ausdrucksfunktionen, mit denen Sie das Modell einer Suchabfrage und nicht ihren genauen Wert beschreiben können
Wir werden auf jeden Fall in unserem Blog über diese Funktionen schreiben. In der Zwischenzeit können Sie alle in diesem Artikel beschriebenen Funktionen anhand unserer Demodaten ausprobieren.
Demodaten für die Praxis der SQL-Abfragen
herunterladen