GSP412

Überblick

BigQuery ist eine vollständig verwaltete, automatisierte und kostengünstige Analysedatenbank von Google. Mit diesem Tool können Sie mehrere Terabyte an Daten abfragen und müssen dabei weder eine Infrastruktur verwalten, noch benötigen Sie einen Datenbankadministrator. BigQuery basiert auf SQL und die Abrechnung erfolgt nach dem „Pay as you go“-Modell. Mithilfe von BigQuery können Sie sich auf die Datenanalyse konzentrieren und wichtige Informationen erhalten.

Durch das Zusammenführen von Datentabellen können Sie wichtige Einblicke in Ihr Dataset gewinnen. Damit Sie aussagekräftige Ergebnisse erhalten, gilt es jedoch, beim Erstellen eines Joins einige häufige Stolperfallen zu vermeiden. Darum soll es in diesem Lab gehen. Es gibt folgende Join-Typen:

• Cross Join: Hierbei wird jede Zeile des ersten Datasets mit jeder Zeile des zweiten Datasets kombiniert und jede Kombination wird in der Ausgabe dargestellt.
• Inner Join: Bei diesem Typ sind Schlüsselwerte in beiden Tabellen erforderlich, damit die Einträge in der Ergebnistabelle angezeigt werden. Einträge werden in der Zusammenführung nur dann angezeigt, wenn für die Schlüsselwerte Übereinstimmungen in beiden Tabellen vorhanden sind.
• Left Join: Jede Zeile in der linken Tabelle wird in den Ergebnissen angezeigt, unabhängig davon, ob Übereinstimmungen in der rechten Tabelle vorliegen.
• Right Join: Dies ist die Umkehrung eines Left Joins. Jede Zeile in der rechten Tabelle wird in den Ergebnissen angezeigt, unabhängig davon, ob in der linken Tabelle Übereinstimmungen vorliegen.

Weitere Informationen zu Joins finden Sie auf der Seite „Join“.

In diesem Lab arbeiten Sie in BigQuery mit der Kopie eines E-Commerce-Datasets, das Millionen von Google Analytics-Einträgen des Google Merchandise Store enthält. Ihre Aufgabe ist es, anhand der verfügbaren Felder und Zeilen bestimmte Informationen abzurufen.

Informationen zur Syntax, die Ihnen dabei helfen, die Abfragen nachzuvollziehen und zu aktualisieren, finden Sie unter Standard-SQL-Abfragesyntax.

Aufgaben

Aufgaben in diesem Lab:

• BigQuery verwenden, um doppelte Zeilen in einem Dataset zu finden und Probleme zu beheben
• Joins von Datentabellen erstellen
• Aus verschiedenen Join-Typen auswählen.

Einrichtung und Anforderungen

Vor dem Klick auf „Start Lab“ (Lab starten)

Lesen Sie diese Anleitung. Labs sind zeitlich begrenzt und können nicht pausiert werden. Der Timer beginnt zu laufen, wenn Sie auf Lab starten klicken, und zeigt Ihnen, wie lange die Ressourcen für das Lab verfügbar sind.

In diesem praxisorientierten Lab können Sie die Lab-Aktivitäten in einer echten Cloud-Umgebung selbst durchführen – nicht in einer Simulations- oder Demo-Umgebung. Dazu erhalten Sie neue, temporäre Anmeldedaten, mit denen Sie für die Dauer des Labs auf Google Cloud zugreifen können.

Für dieses Lab benötigen Sie Folgendes:

• Einen Standardbrowser (empfohlen wird Chrome)

Hinweis: Nutzen Sie den privaten oder Inkognitomodus, um dieses Lab durchzuführen. So wird verhindert, dass es zu Konflikten zwischen Ihrem persönlichen Konto und dem Teilnehmerkonto kommt und zusätzliche Gebühren für Ihr persönliches Konto erhoben werden.

• Zeit für die Durchführung des Labs – denken Sie daran, dass Sie ein begonnenes Lab nicht unterbrechen können.

Hinweis: Wenn Sie über ein persönliches Google Cloud-Konto oder -Projekt verfügen, verwenden Sie es nicht für dieses Lab. So werden zusätzliche Kosten für Ihr Konto vermieden.

Lab starten und bei der Google Cloud Console anmelden

1. Klicken Sie auf Lab starten. Wenn Sie für das Lab bezahlen müssen, wird ein Pop-up-Fenster geöffnet, in dem Sie Ihre Zahlungsmethode auswählen können. Auf der linken Seite befindet sich der Bereich Details zum Lab mit diesen Informationen:

   • Schaltfläche Google Cloud Console öffnen
   • Restzeit
   • Temporäre Anmeldedaten für das Lab
   • Ggf. weitere Informationen für dieses Lab

2. Klicken Sie auf Google Cloud Console öffnen (oder klicken Sie mit der rechten Maustaste und wählen Sie Link in Inkognitofenster öffnen aus, wenn Sie Chrome verwenden).

   Im Lab werden Ressourcen aktiviert. Anschließend wird ein weiterer Tab mit der Seite Anmelden geöffnet.

   Tipp: Ordnen Sie die Tabs nebeneinander in separaten Fenstern an.

   Hinweis: Wird das Dialogfeld Konto auswählen angezeigt, klicken Sie auf Anderes Konto verwenden.

3. Kopieren Sie bei Bedarf den folgenden Nutzernamen und fügen Sie ihn in das Dialogfeld Anmelden ein.

   {{{user_0.username | "Username"}}}

   Sie finden den Nutzernamen auch im Bereich Details zum Lab.

4. Klicken Sie auf Weiter.

5. Kopieren Sie das folgende Passwort und fügen Sie es in das Dialogfeld Willkommen ein.

   {{{user_0.password | "Password"}}}

   Sie finden das Passwort auch im Bereich Details zum Lab.

6. Klicken Sie auf Weiter.

   Wichtig: Sie müssen die für das Lab bereitgestellten Anmeldedaten verwenden. Nutzen Sie nicht die Anmeldedaten Ihres Google Cloud-Kontos. Hinweis: Wenn Sie Ihr eigenes Google Cloud-Konto für dieses Lab nutzen, können zusätzliche Kosten anfallen.

7. Klicken Sie sich durch die nachfolgenden Seiten:

   • Akzeptieren Sie die Nutzungsbedingungen.
   • Fügen Sie keine Wiederherstellungsoptionen oder Zwei-Faktor-Authentifizierung hinzu (da dies nur ein temporäres Konto ist).
   • Melden Sie sich nicht für kostenlose Testversionen an.

Nach wenigen Augenblicken wird die Google Cloud Console in diesem Tab geöffnet.

Hinweis: Wenn Sie sich eine Liste der Google Cloud-Produkte und ‑Dienste ansehen möchten, klicken Sie oben links auf das Navigationsmenü.

Die BigQuery Console öffnen

1. Klicken Sie in der Google Cloud Console im Navigationsmenü auf BigQuery.

Zuerst wird das Fenster Willkommen bei BigQuery in der Cloud Console geöffnet, das neben allgemeinen Informationen auch einen Link zur Kurzanleitung und zu den Versionshinweisen enthält.

2. Klicken Sie auf Fertig.

Die BigQuery Console wird geöffnet.

Aufgabe 1: Neues Dataset zum Speichern der Tabellen erstellen

Erstellen Sie in Ihrem BigQuery-Projekt ein neues Dataset mit dem Namen ecommerce.

1. Klicken Sie auf die drei Punkte neben Ihrer Projekt-ID und wählen Sie Dataset erstellen aus.

Das Dialogfeld Dataset erstellen wird geöffnet.

2. Legen Sie als Dataset-ID den Namen ecommerce fest.

3. Verwenden Sie für die anderen Optionen die Standardwerte und klicken Sie auf Dataset erstellen.

Im linken Bereich sehen Sie dann eine Tabelle mit dem Namen ecommerce, die unter Ihrem Projekt aufgeführt ist.

Klicken Sie auf Fortschritt prüfen.

Neues Dataset erstellen

Aufgabe 2: Lab-Projekt in BigQuery anpinnen

Folgendes Szenario: Ihr Team stellt Ihnen ein neues Dataset zu den Lagerbeständen für jedes Ihrer Produkte bereit, die auf Ihrer E-Commerce-Website zum Verkauf stehen. Ihre Aufgabe ist es, sich mit den Produkten auf der Website und den Feldern, die Sie zum Zusammenführen mit anderen Datasets verwenden können, vertraut zu machen.

Das Projekt mit dem neuen Dataset heißt data-to-insights.

1. Klicken Sie im Navigationsmenü auf  > BigQuery.

Das Fenster „Welcome to BigQuery in the Cloud Console“ (Willkommen bei BigQuery in der Cloud Console) wird geöffnet.

Hinweis: Dieses Fenster enthält einen Link zur Kurzanleitung und Informationen zu Aktualisierungen der Benutzeroberfläche.

2. Klicken Sie auf Fertig.

3. Öffentliche BigQuery-Datasets werden nicht standardmäßig angezeigt. So öffnen Sie das öffentliche Datasets-Projekt: Kopieren Sie data-to-insights.

4. Klicken Sie auf + Hinzufügen > Projekt nach Name markieren. Fügen Sie dann den data-to-insights-Namen ein.

5. Klicken Sie auf Markieren.

Das data-to-insights-Projekt ist jetzt im Abschnitt „Explorer“ aufgelistet.

Aufgabe 3: Felder untersuchen

Machen Sie sich nun mit den Produkten und Feldern auf der Website vertraut, mit denen Sie Abfragen zur Analyse des Datasets erstellen können.

1. Gehen Sie dazu im linken Bereich unter „Ressourcen“ zu data-to-insights > ecommerce > all_sessions_raw.

2. Klicken Sie rechts (unter dem Abfrageeditor) auf den Tab Schema, um die Felder und Informationen zu den einzelnen Feldern anzeigen zu lassen.

Aufgabe 4: Ein Schlüsselfeld in Ihrem E-Commerce-Dataset identifizieren

Sehen Sie sich die Produkte auf der Website und die Felder genau an. Ihre Aufgabe ist es, sich mit den Produkten auf der Website und den Feldern, die Sie zum Zusammenführen mit anderen Datasets verwenden können, vertraut zu machen.

Einträge untersuchen

In diesem Abschnitt finden Sie heraus, wie viele Produktnamen und Produkt-SKUs auf Ihrer Website vorhanden sind und ob diese Felder eindeutig sind.

1. Um herauszufinden, wie viele Produktnamen und Produkt-SKUs auf der Website vorhanden sind, kopieren Sie die folgende Anfrage in den BigQuery EDITOR:

#standardSQL # how many products are on the website? SELECT DISTINCT productSKU, v2ProductName FROM `data-to-insights.ecommerce.all_sessions_raw`

2. Klicken Sie auf Ausführen.

Wenn Sie erfahren möchten, wie viele Datensätze insgesamt ausgegeben wurden, sehen Sie sich die Paginierungsergebnisse in der Console an.

Aber bedeutet dies, dass es tatsächlich so viele eindeutige SKUs gibt? Eine der ersten Abfragen, die Sie als Data Analyst durchführen werden, ist die Überprüfung der Eindeutigkeit Ihrer Datenwerte.

3. Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus, um die Anzahl der unterschiedlichen SKUs mit DISTINCT aufzulisten:

#standardSQL # find the count of unique SKUs SELECT DISTINCT productSKU FROM `data-to-insights.ecommerce.all_sessions_raw`

Beziehung zwischen SKU und Name untersuchen

Ermitteln Sie jetzt, welche Produkte mehr als eine SKU haben und welche SKUs zu mehr als einem Produktnamen gehören.

1. Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus, um festzustellen, ob einige Produktnamen mehr als eine SKU haben. Mit der Funktion STRING_AGG() aggregieren Sie alle Produkt-SKUs, die einem Produktnamen zugeordnet sind, als kommagetrennte Werte.

SELECT v2ProductName, COUNT(DISTINCT productSKU) AS SKU_count, STRING_AGG(DISTINCT productSKU LIMIT 5) AS SKU FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE productSKU IS NOT NULL GROUP BY v2ProductName HAVING SKU_count > 1 ORDER BY SKU_count DESC

2. Klicken Sie auf Ausführen.

Das Ergebnis:

Der Katalog der E-Commerce-Website zeigt, dass jeder Produktname mehrere Optionen (Größe, Farbe) haben kann, die als separate SKUs verkauft werden.

Sie sehen also, dass 1 Produkt 12 SKUs haben kann. Wie genau sollte eine einzelne SKU verwendet werden? Sollte es zulässig sein, dass sie mehr als einem Produkt zugeordnet ist?

• Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus, um es herauszufinden:

SELECT productSKU, COUNT(DISTINCT v2ProductName) AS product_count, STRING_AGG(DISTINCT v2ProductName LIMIT 5) AS product_name FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE v2ProductName IS NOT NULL GROUP BY productSKU HAVING product_count > 1 ORDER BY product_count DESC

Hinweis: Probieren Sie auch aus, was passiert, wenn Sie STRING_AGG() durch ARRAY_AGG() ersetzen. Nicht schlecht, oder? BigQuery unterstützt nativ verschachtelte Arraywerte. Mehr dazu finden Sie im Handbuch „Mit Arrays arbeiten“.

Im nächsten Abschnitt sehen Sie, warum diese m:n-Beziehung ein Problem darstellt.

Klicken Sie auf Fortschritt prüfen.

Ein Schlüsselfeld in Ihrem E-Commerce-Dataset identifizieren

Aufgabe 5: Problem: nicht eindeutiger Schlüssel

Bei der Bestandsverfolgung dient eine SKU dazu, genau ein einziges Produkt eindeutig zu identifizieren. Für uns bildet sie die Grundlage Ihrer JOIN-Bedingung, wenn Sie Informationen in anderen Tabellen nachschlagen. Wie wir feststellen werden, kann ein nicht eindeutiger Schlüssel zu schwerwiegenden Datenproblemen führen.

1. Schreiben Sie eine Abfrage, mit der Sie alle Produktnamen für die Artikelnummer 'GGOEGPJC019099' ermitteln.

Mögliche Lösung:

SELECT DISTINCT v2ProductName, productSKU FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE productSKU = 'GGOEGPJC019099'

2. Klicken Sie auf Ausführen.

v2ProductName	productSKU
Hundefrisbee 7"	GGOEGPJC019099
Hundefrisbee 7"	GGOEGPJC019099
Hundeflugscheibe blau 7 Zoll	GGOEGPJC019099

Sie sehen, dass in den Abfrageergebnissen drei verschiedene Namen für dasselbe Produkt auftauchen. In diesem Beispiel ist einmal ein Sonderzeichen vorhanden und einmal wird eine nur leicht abweichende Bezeichnung verwendet:

Websitedaten mit Ihrer Produktinventarliste zusammenführen

Sehen Sie sich nun an, welche Auswirkungen ein Zusammenführen auf ein Dataset hat, wenn es mehrere Produkten mit der gleichen SKU gibt. Prüfen Sie zuerst im Produktinventar-Dataset (die Tabelle products), ob diese SKU dort eindeutig ist.

• Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus:

SELECT SKU, name, stockLevel FROM `data-to-insights.ecommerce.products` WHERE SKU = 'GGOEGPJC019099'

Join-Problem: unbeabsichtigte Viele-zu-Eins-Beziehung zwischen Artikelnummern

Wir haben nun zwei Datasets: eines für den Lagerbestand und das andere für unsere Website-Analysen. Erstellen Sie als Nächstes ein JOIN des Inventar-Datasets mit den Produktnamen Ihrer Website und den SKUs, damit der Lagerbestand des jeweiligen Produkts auf der Website angezeigt werden kann.

1. Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus:

SELECT DISTINCT website.v2ProductName, website.productSKU, inventory.stockLevel FROM `data-to-insights.ecommerce.all_sessions_raw` AS website JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE productSKU = 'GGOEGPJC019099'

Als Nächstes erweitern wir unsere vorherige Abfrage, um mit SUM ganz einfach die Summe des verfügbaren Bestands nach Produkt zu ermitteln.

2. Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus:

WITH inventory_per_sku AS ( SELECT DISTINCT website.v2ProductName, website.productSKU, inventory.stockLevel FROM `data-to-insights.ecommerce.all_sessions_raw` AS website JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE productSKU = 'GGOEGPJC019099' ) SELECT productSKU, SUM(stockLevel) AS total_inventory FROM inventory_per_sku GROUP BY productSKU

Oh nein! Es wird 154 × 3 = 462, also die dreifache Zahl angezeigt. Dies wird als unbeabsichtigter Cross Join bezeichnet. Wir kommen später auf dieses Thema zurück.

Klicken Sie auf Fortschritt prüfen.

Problem: nicht eindeutiger Schlüssel

Aufgabe 6: Lösung für das Join-Problem: vor dem Zusammenführen verschiedene Artikelnummern verwenden

Welche Möglichkeiten gibt es, das Problem der Dreifachzählung zu lösen? Zuerst einmal dürfen Sie nur verschiedene Artikelnummern von der Website auswählen, bevor Sie sie mit anderen Datasets zusammenführen.

Sie wissen, dass sich mehr als ein Produktname (z. B. 7-Zoll-Frisbee für Hunde) eine einzige SKU teilen können.

1. Erfassen Sie alle möglichen Namen in einem Array:

SELECT productSKU, ARRAY_AGG(DISTINCT v2ProductName) AS push_all_names_into_array FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE productSKU = 'GGOEGAAX0098' GROUP BY productSKU

Statt einer Zeile für jeden Produktnamen haben Sie jetzt nur noch eine Zeile für jede eindeutige SKU.

2. Wenn Sie die Produktnamen deduplizieren möchten, können Sie das Array mit LIMIT begrenzen:

SELECT productSKU, ARRAY_AGG(DISTINCT v2ProductName LIMIT 1) AS push_all_names_into_array FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE productSKU = 'GGOEGAAX0098' GROUP BY productSKU

Join-Problem: Verlust von Dateneinträgen nach einem Join

Jetzt können Sie die Daten mit dem Produktinventar-Dataset zusammenführen.

1. Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus:

#standardSQL SELECT DISTINCT website.productSKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU

Wie es aussieht, sind 819 SKUs nach dem Zusammenführen der Datasets verloren gegangen. Wir untersuchen das genauer und machen mit einer SKU-Spalte aus jedem Dataset unsere Felder spezifischer:

2. Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus:

#standardSQL # pull ID fields from both tables SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU # IDs are present in both tables, how can you dig deeper?

Anscheinend sind die SKUs für diese 1.090 Datensätze nach dem Join in beiden Datasets vorhanden. Wie können Sie die fehlenden Datensätze finden?

Lösung für das Join-Problem: Auswahl des richtigen Join-Typs und nach NULL filtern

Der Standard-JOIN-Typ ist ein INNER JOIN. Dieser gibt nur Einträge aus, wenn sowohl auf der linken als auch auf der rechten zusammengeführten Tabelle eine Übereinstimmung der SKU besteht.

1. Schreiben Sie die vorangegangene Abfrage so um, dass ein anderer Join-Typ verwendet wird, der alle Einträge von der Websitetabelle einschließt, und zwar unabhängig davon, ob eine Übereinstimmung bei den Artikelnummereinträgen in der Produktinventartabelle besteht. Join-Typ-Optionen: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN.

Mögliche Lösung:

#standardSQL # the secret is in the JOIN type # pull ID fields from both tables SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU

2. Klicken Sie auf Ausführen.

Sie haben einen LEFT JOIN durchgeführt, mit dem alle 1.909 Artikelnummern der Website ausgegeben werden.

Wie viele Artikelnummern aus Ihrem Produktinventar-Dataset fehlen?

1. Schreiben Sie eine Abfrage, mit der Sie die NULL-Werte aus Ihrer Inventartabelle herausfiltern.

Mögliche Lösung:

#standardSQL # find product SKUs in website table but not in product inventory table SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE inventory.SKU IS NULL

2. Klicken Sie auf Ausführen.

Frage: Wie viele Produkte fehlen?

Antwort: Es fehlen 819 Produkte (ARTIKELNUMMER IST NULL) aus Ihrem Produktinventar-Dataset.

• Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus, um die Verwendung einer der spezifischen SKUs aus dem Website-Dataset zu bestätigen:

#standardSQL # you can even pick one and confirm SELECT * FROM `data-to-insights.ecommerce.products` WHERE SKU = 'GGOEGATJ060517' # query returns zero results

Wie sieht es nun umgekehrt aus? Gibt es Produkte, die im Produktinventar-Dataset vorhanden sind, aber auf der Website fehlen?

1. Schreiben Sie eine Abfrage mit einem anderen Join-Typ, um dies zu untersuchen.

Mögliche Lösung:

#standardSQL # reverse the join # find records in website but not in inventory SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website RIGHT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE website.productSKU IS NULL

2. Klicken Sie auf Ausführen.

Antwort: Ja. Es gibt zwei Artikelnummern, die im Website-Dataset fehlen.

Fügen Sie als Nächstes weitere Felder aus dem Produktinventar-Dataset hinzu, um weitere Informationen zu erhalten.

• Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus:

#standardSQL # what are these products? # add more fields in the SELECT STATEMENT SELECT DISTINCT website.productSKU AS website_SKU, inventory.* FROM `data-to-insights.ecommerce.all_sessions_raw` AS website RIGHT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE website.productSKU IS NULL

Warum könnten die unten stehenden Produkte im ecommerce-Dataset fehlen?

website_SKU	SKU	name	orderedQuantity	stockLevel	restockingLeadTime	sentimentScore	sentimentMagnitude
null	GGOBJGOWUSG69402	USB-Soundbar – nur im Geschäft	10	15	2	1.0	1.0
null	GGADFBSBKS42347	PC-Gaming-Lautsprecher	0	100	1	null	null

Mögliche Antworten:

• Ein neues Produkt (keine Bestellungen, kein sentimentScore) und ein Produkt, das „nur im Geschäft“ verfügbar ist
• Ein weiteres Produkt ist neu mit 0 Bestellungen

Warum wird das neue Produkt nicht in Ihrem Website-Dataset angezeigt?

• Bei dem Website-Dataset handelt es sich um vergangene Bestellvorgänge von Kundinnen und Kunden. Brandneue Produkte, die noch nie verkauft wurden, werden erst dann in der Webanalyse angezeigt, wenn sie angesehen oder gekauft werden.

Hinweis: In Produktionsabfragen werden in der Regel keine RIGHT JOINs angezeigt. Sie würden in diesem Fall einfach einen LEFT JOIN durchführen und die Reihenfolge der Tabellen ändern.

Was müssten Sie tun, um eine Abfrage zu erhalten, in der alle Produkte angezeigt werden, die sowohl auf der Website als im Inventar fehlen?

1. Schreiben Sie eine Abfrage mit einem anderen Join-Typ.

Mögliche Lösung:

#standardSQL SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website FULL JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE website.productSKU IS NULL OR inventory.SKU IS NULL

2. Klicken Sie auf Ausführen.

Sie haben 819 + 2 = 821 Produkt-SKUs.

LEFT JOIN + RIGHT JOIN = FULL JOIN, der alle Einträge aus beiden Tabellen ausgibt, unabhängig von übereinstimmenden Join-Schlüsseln. Dann filtern Sie heraus, wo auf beiden Seiten Nichtübereinstimmungen bestehen.

Join-Problem: unbeabsichtigter Cross Join

Wenn Sie die Beziehung zwischen Datentabellenschlüsseln (1:1, 1:N, N:N) nicht kennen, kann dies zu unerwarteten Ergebnissen führen und die Abfrageleistung erheblich reduzieren.

Der letzte Join-Typ ist der CROSS JOIN.

Erstellen Sie eine neue Tabelle mit einem prozentualen Rabatt, der für die gesamte Website für Produkte aus der Kategorie „Ausverkauf“ gilt.

1. Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus:

#standardSQL CREATE OR REPLACE TABLE ecommerce.site_wide_promotion AS SELECT .05 AS discount;

Auf der linken Seite wird site_wide_promotion nun im Abschnitt „Ressource“ unter Ihrem Projekt und Dataset aufgeführt.

2. Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus, um herauszufinden, wie viele Produkte im Ausverkauf sind:

SELECT DISTINCT productSKU, v2ProductCategory, discount FROM `data-to-insights.ecommerce.all_sessions_raw` AS website CROSS JOIN ecommerce.site_wide_promotion WHERE v2ProductCategory LIKE '%Clearance%'

Hinweis: Bei einem CROSS JOIN gibt es keine Join-Bedingung (z. B. ON oder USING). Das Feld wird einfach mit dem ersten Dataset bzw. für alle Artikel mit 0,05 multipliziert.

Sehen wir uns an, welche Auswirkungen es hat, wenn in der Rabatttabelle unabsichtlich mehr als ein Eintrag hinzugefügt wird.

3. Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus, um zwei weitere Datensätze in die Rabatttabelle einzufügen:

INSERT INTO ecommerce.site_wide_promotion (discount) VALUES (.04), (.03);

Als Nächstes sehen wir uns die Datenwerte in der Rabatttabelle an.

4. Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus:

SELECT discount FROM ecommerce.site_wide_promotion

Wie viele Einträge wurden ausgegeben?

Antwort: 3

Was passiert, wenn Sie den Rabatt noch einmal auf alle 82 Produkte im Ausverkauf anwenden?

5. Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus:

SELECT DISTINCT productSKU, v2ProductCategory, discount FROM `data-to-insights.ecommerce.all_sessions_raw` AS website CROSS JOIN ecommerce.site_wide_promotion WHERE v2ProductCategory LIKE '%Clearance%'

Wie viele Produkte werden ausgegeben?

Antwort: Statt 82 werden nun 246 Produkte ausgegeben, also mehr als in der Originaltabelle.

Die Ursache dafür lässt sich herausfinden, wenn wir eine einzelne Produkt-SKU betrachten.

6. Löschen Sie die vorherige Abfrage und führen Sie die folgende Abfrage aus:

#standardSQL SELECT DISTINCT productSKU, v2ProductCategory, discount FROM `data-to-insights.ecommerce.all_sessions_raw` AS website CROSS JOIN ecommerce.site_wide_promotion WHERE v2ProductCategory LIKE '%Clearance%' AND productSKU = 'GGOEGOLC013299'

Welche Auswirkungen hatte der CROSS JOIN?

Antwort: Da es drei Rabattcodes gibt, für die ein Cross Join durchgeführt werden kann, wird das ursprüngliche Dataset verdreifacht.

Hinweis: Diese Funktionsweise beschränkt sich nicht nur auf Cross Joins. Mit einem normalen Join können Sie ebenfalls unbeabsichtigt Cross Joins erstellen, wenn die Datenbeziehung „m:n“ lautet. Dies kann dazu führen, dass unbeabsichtigt Millionen oder gar Milliarden von Einträgen ausgegeben werden.

Lösung: Sie müssen die Datenbeziehungen kennen, bevor Sie einen Join erstellen, und Sie dürfen nicht davon ausgehen, dass die Schlüssel eindeutig sind.

Klicken Sie auf Fortschritt prüfen.

Join-Problem: Lösung

Das wars!

In diesem Lab haben Sie gelernt, wie Sie doppelte Einträge erkennen und wann Sie welchen Join-Typ verwenden sollten. So können Sie schwerwiegende SQL-Probleme vermeiden. Gut gemacht!

Weitere Informationen

• Sie haben bereits ein Google Analytics-Konto und möchten Ihre eigenen Datasets in BigQuery abfragen? Dann befolgen Sie die Schritte im Artikel BigQuery Export einrichten in der Google Analytics-Hilfe.
• Weitere Informationen zu Best Practices bei der Optimierung der Abfrageleistung.
• Wenn Sie noch mehr SQL-Syntax für JOINS einüben möchten, lesen Sie die BigQuery-Dokumentation zu JOIN.
• Labs zum Vertiefen Ihres Wissens:
  • In BigQuery mit JSON, Arrays und STRUCTs arbeiten
  • Einführung in SQL für BigQuery und Cloud SQL
  • Taxikosten mit einem ML-Prognosemodell in BigQuery vorhersagen

Google Cloud-Schulungen und -Zertifizierungen

In unseren Schulungen erfahren Sie alles zum optimalen Einsatz unserer Google Cloud-Technologien und können sich entsprechend zertifizieren lassen. Unsere Kurse vermitteln technische Fähigkeiten und Best Practices, damit Sie möglichst schnell mit Google Cloud loslegen und Ihr Wissen fortlaufend erweitern können. Wir bieten On-Demand-, Präsenz- und virtuelle Schulungen für Anfänger wie Fortgeschrittene an, die Sie individuell in Ihrem eigenen Zeitplan absolvieren können. Mit unseren Zertifizierungen weisen Sie nach, dass Sie Experte im Bereich Google Cloud-Technologien sind.

Anleitung zuletzt am 3. Februar 2024 aktualisiert

Lab zuletzt am 20. September 2023 getestet

© 2024 Google LLC. Alle Rechte vorbehalten. Google und das Google-Logo sind Marken von Google LLC. Alle anderen Unternehmens- und Produktnamen können Marken der jeweils mit ihnen verbundenen Unternehmen sein.